Обсуждение: strange behavior

Поиск
Список
Период
Сортировка

strange behavior

От
"Patrick Hatcher"
Дата:
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

Re: strange behavior

От
Andrew Ayers
Дата:
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.

Re: strange behavior

От
"Patrick Hatcher"
Дата:
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

Re: strange behavior

От
"Greg Campbell"
Дата:
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

Re: strange behavior

От
Jeff Eckermann
Дата:
> 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