Thread: strange behavior
ODBC: 7.03.02
App: Visual Basics 6
Pg: 7.3.3 (same problem on 7.4)
I'm having a problem I cannot figure out. I have code that reads through a
recordset and makes necessary updates to fields and then updates the
record. For some reason, the process always stops at record 99. No matter
what happens, it stops while trying to do rs.MOVENEXT and freezes my app.
What I do notice is that if I remove the code between the Do and MOVENEXT,
the entire recordset is read without issue. Am I missing something? Do I
need to clear some variable out?
Here's a snippet of my code (it looks suspiciously like the example
provided on the website):
Set rsBASADDs = New ADODB.Recordset
rsBASADDs.Open "Select oid,* from PREP order by dept, vend,mstyle", Conn1,
adOpenDynamic, adLockOptimistic, adCmdText
If rsBASADDs.BOF And rsBASADDs.EOF Then
CloseTimer
End If
rsBASADDs.MoveFirst
Do While Not rsBASADDs.EOF
If (rsBASADDs.Fields("nrfcolorcode") = Null Or
Val(rsBASADDs.Fields("nrfcolorcode")) = 0) And _
(rsBASADDs.Fields("nrfsizecode") = Null Or Val(rsBASADDs.Fields
("nrfsizecode")) = 0) Then
NoColorSizeStyle = True
rsBASADDs.Fields("mwcolorcode") = "00"
rsBASADDs.Fields("mwsizecode") = "000"
'rsBASADDs.Fields("mwsizerange") = "000"
rsBASADDs.Fields("status") = "NON CS STYLE"
End If
rsBASADDs.Update
x = x + 1
Debug.Print rsBASADDs.Fields("oid") & " - " & x
rsBASADDs.MoveNext
Loop
TIA
Patrick Hatcher
Patrick Hatcher wrote:
> ODBC: 7.03.02
> App: Visual Basics 6
> Pg: 7.3.3 (same problem on 7.4)
>
> I'm having a problem I cannot figure out. I have code that reads through a
> recordset and makes necessary updates to fields and then updates the
> record. For some reason, the process always stops at record 99. No matter
> what happens, it stops while trying to do rs.MOVENEXT and freezes my app.
> What I do notice is that if I remove the code between the Do and MOVENEXT,
> the entire recordset is read without issue. Am I missing something? Do I
> need to clear some variable out?
Strange...
> Here's a snippet of my code (it looks suspiciously like the example
> provided on the website):
>
> Set rsBASADDs = New ADODB.Recordset
>
> rsBASADDs.Open "Select oid,* from PREP order by dept, vend,mstyle", Conn1,
> adOpenDynamic, adLockOptimistic, adCmdText
>
> If rsBASADDs.BOF And rsBASADDs.EOF Then
> CloseTimer
> End If
>
>
> rsBASADDs.MoveFirst
>
> Do While Not rsBASADDs.EOF
>
> If (rsBASADDs.Fields("nrfcolorcode") = Null Or
> Val(rsBASADDs.Fields("nrfcolorcode")) = 0) And _
> (rsBASADDs.Fields("nrfsizecode") = Null Or Val(rsBASADDs.Fields
> ("nrfsizecode")) = 0) Then
> NoColorSizeStyle = True
> rsBASADDs.Fields("mwcolorcode") = "00"
> rsBASADDs.Fields("mwsizecode") = "000"
> 'rsBASADDs.Fields("mwsizerange") = "000"
> rsBASADDs.Fields("status") = "NON CS STYLE"
>
> End If
>
> rsBASADDs.Update
> x = x + 1
> Debug.Print rsBASADDs.Fields("oid") & " - " & x
> rsBASADDs.MoveNext
> Loop
Have you tried moving the Update inside of the If-Then expression?
Because you only need to perform the Update if you have changed the
record (not every record).
Can't see anything else you are doing wrong, offhand...
Andrew L. Ayers
-- CONFIDENTIALITY NOTICE --
This message is intended for the sole use of the individual and entity to whom it is addressed, and may contain
informationthat is privileged, confidential and exempt from disclosure under applicable law. If you are not the
intendedaddressee, nor authorized to receive for the intended addressee, you are hereby notified that you may not use,
copy,disclose or distribute to anyone the message or any information contained in the message. If you have received
thismessage in error, please immediately advise the sender by reply email, and delete the message. Thank you.
Figured out the problem. I had Declare/Fetch enabled and the cache size
set to 100.
Now my next question is. How do I go about using Declare/Fetch with large
recordsets? After the buffer reaches the end of my first Declare/Fetch, I
cannot seem to go any further
thanks again
Patrick Hatcher
Patrick
Hatcher/MCOM/FDD
To
11/25/2003 01:37 PM Pg_ODBC Pg_ODBC
cc
Subject
strange behavior
ODBC: 7.03.02
App: Visual Basics 6
Pg: 7.3.3 (same problem on 7.4)
I'm having a problem I cannot figure out. I have code that reads through a
recordset and makes necessary updates to fields and then updates the
record. For some reason, the process always stops at record 99. No matter
what happens, it stops while trying to do rs.MOVENEXT and freezes my app.
What I do notice is that if I remove the code between the Do and MOVENEXT,
the entire recordset is read without issue. Am I missing something? Do I
need to clear some variable out?
Here's a snippet of my code (it looks suspiciously like the example
provided on the website):
Set rsBASADDs = New ADODB.Recordset
rsBASADDs.Open "Select oid,* from PREP order by dept, vend,mstyle", Conn1,
adOpenDynamic, adLockOptimistic, adCmdText
If rsBASADDs.BOF And rsBASADDs.EOF Then
CloseTimer
End If
rsBASADDs.MoveFirst
Do While Not rsBASADDs.EOF
If (rsBASADDs.Fields("nrfcolorcode") = Null Or
Val(rsBASADDs.Fields("nrfcolorcode")) = 0) And _
(rsBASADDs.Fields("nrfsizecode") = Null Or Val(rsBASADDs.Fields
("nrfsizecode")) = 0) Then
NoColorSizeStyle = True
rsBASADDs.Fields("mwcolorcode") = "00"
rsBASADDs.Fields("mwsizecode") = "000"
'rsBASADDs.Fields("mwsizerange") = "000"
rsBASADDs.Fields("status") = "NON CS STYLE"
End If
rsBASADDs.Update
x = x + 1
Debug.Print rsBASADDs.Fields("oid") & " - " & x
rsBASADDs.MoveNext
Loop
TIA
Patrick Hatcher
I am not sure if there is an alternative to Declare/Fetch.
I would suggest taking a more client/server-centric approach. Your loop
uses a dynamic cursor (holding the cursor on the server side, while each
row is examined and possibly changed by the client). The same function
could have been accomplished with an SQL Update statement with the
appropriate where clause. The server would process it, and it would be
faster.
I tend to think SELECT statements with client-side cursors for
retrieving information.
I tend to use executable statements otherwise
INSERTS for adding. UPDATES for changing. And DELETES for ... the
obvious.
Patrick Hatcher wrote:
>
> Figured out the problem. I had Declare/Fetch enabled and the cache size
> set to 100.
> Now my next question is. How do I go about using Declare/Fetch with large
> recordsets? After the buffer reaches the end of my first Declare/Fetch, I
> cannot seem to go any further
>
> thanks again
> Patrick Hatcher
>
>
> Patrick
> Hatcher/MCOM/FDD
> To
> 11/25/2003 01:37 PM Pg_ODBC Pg_ODBC
> cc
>
> Subject
> strange behavior
>
>
>
>
>
>
>
> ODBC: 7.03.02
> App: Visual Basics 6
> Pg: 7.3.3 (same problem on 7.4)
>
> I'm having a problem I cannot figure out. I have code that reads through a
> recordset and makes necessary updates to fields and then updates the
> record. For some reason, the process always stops at record 99. No matter
> what happens, it stops while trying to do rs.MOVENEXT and freezes my app.
> What I do notice is that if I remove the code between the Do and MOVENEXT,
> the entire recordset is read without issue. Am I missing something? Do I
> need to clear some variable out?
>
> Here's a snippet of my code (it looks suspiciously like the example
> provided on the website):
>
> Set rsBASADDs = New ADODB.Recordset
>
> rsBASADDs.Open "Select oid,* from PREP order by dept, vend,mstyle", Conn1,
> adOpenDynamic, adLockOptimistic, adCmdText
>
> If rsBASADDs.BOF And rsBASADDs.EOF Then
> CloseTimer
> End If
>
> rsBASADDs.MoveFirst
>
> Do While Not rsBASADDs.EOF
>
> If (rsBASADDs.Fields("nrfcolorcode") = Null Or
> Val(rsBASADDs.Fields("nrfcolorcode")) = 0) And _
> (rsBASADDs.Fields("nrfsizecode") = Null Or Val(rsBASADDs.Fields
> ("nrfsizecode")) = 0) Then
> NoColorSizeStyle = True
> rsBASADDs.Fields("mwcolorcode") = "00"
> rsBASADDs.Fields("mwsizecode") = "000"
> 'rsBASADDs.Fields("mwsizerange") = "000"
> rsBASADDs.Fields("status") = "NON CS STYLE"
>
> End If
>
> rsBASADDs.Update
> x = x + 1
> Debug.Print rsBASADDs.Fields("oid") & " - " & x
> rsBASADDs.MoveNext
> Loop
>
> TIA
>
> Patrick Hatcher
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
> Patrick Hatcher wrote: > > > > Figured out the problem. I had Declare/Fetch > enabled and the cache size > > set to 100. > > Now my next question is. How do I go about using > Declare/Fetch with large > > recordsets? After the buffer reaches the end of > my first Declare/Fetch, I > > cannot seem to go any further I have not ventured into this, but I suspect that you may have to resort to ODBC function calls in order to do this. Something like "SQLMoreResults". Though you that would seem to lead you towards coding to the ODBC api directly. For most of us, the purpose of using a driver is to avoid having to do that. Hopefully someone can rescue you with an easier solution. __________________________________ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree