?
Solved

VB recordset not returning all records.

Posted on 2011-10-05
13
Medium Priority
?
306 Views
Last Modified: 2012-05-12
Using the following code in VB6, the routine appears to be intermittently returning a false EOF status, and therefore not returning all records.  Is there a problem with 'walking' a record set in this manner?

With rsInv
        .MoveFirst
        While Not .EOF
             <do something with record data>
            .MoveNext
            DoEvents
        Wend
End With

In the loop, I am updating a progress bar which is the reason for the DoEvents.
0
Comment
Question by:mdreed
  • 6
  • 5
  • 2
13 Comments
 
LVL 14

Expert Comment

by:Brook Braswell
ID: 36917744
is this an ADO ?  If so what version ?
What SQL backend are you using?
0
 

Author Comment

by:mdreed
ID: 36917799
Yes, I am using ADO.
SQL is SQL 2005
0
 

Author Comment

by:mdreed
ID: 36917847
I believe the ADO version is 2.8.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 14

Expert Comment

by:Brook Braswell
ID: 36918046
not much of a change...but try this...It would also be nice to know what is happening in the <do something>

With rsInv
        .MoveFirst
        Do While Not .EOF
             <do something with record data>
            .MoveNext
            DoEvents
        loop
End With
0
 

Author Comment

by:mdreed
ID: 36918098
I wouldn't have thought it would matter, but here is the entire code block.  I am building and writing text records to a file for each record in the recordset, and updating a progress bar while doing it.

With rsInv
        .MoveFirst
        iRecCnt = .RecordCount
        If iRecCnt > 0 Then
            frmMessage.pbMsg.Max = iRecCnt
        Else
            frmMessage.pbMsg.Max = 1
        End If
        While Not .EOF
            frmMessage.lblTotMkd = iRecCnt
            frmMessage.pbMsg.Value = iRecNum
            sOutRec = !InvStore & Format(!InvCountArea, "0000") & "RCV" & Format (!InvCountDt, "mmddyyyy")
            sOutRec = sOutRec & Rpad(!InvUPC, " ", 13) & Rpad(!InvDescr, " ", 40)
            sOutRec = sOutRec & Format(!InvQty, "00000000") & "+"
            ts.WriteLine (sOutRec)
            .MoveNext
            iRecNum = iRecNum + 1
            iRecCnt = iRecCnt - 1
            DoEvents
        Wend
    End With
0
 
LVL 14

Expert Comment

by:Brook Braswell
ID: 36918215
I just wanted to see if you were changing any of the values in the rsINV while running through it.
if you had a sort set in the rsINV then you could inadvertently set your pointer to a position based on the sort.

Here are some suggested changes to your code...
With rsInv
     iRecNum = 0
     .MoveFirst
     iRecCnt = .RecordCount
     frmMessage.pbMsg.Max = iif(iRecCnt > 0,iRecCnt,1)
     Do While Not .EOF
        frmMessage.lblTotMkd = iRecCnt - iRecNum
        iRecNum = iRecNum + 1 
        frmMessage.pbMsg.Value = iRecNum
        sOutRec = !InvStore & Format(!InvCountArea, "0000") & "RCV" & Format (!InvCountDt, "mmddyyyy")
        sOutRec = sOutRec & Rpad(!InvUPC, " ", 13) & Rpad(!InvDescr, " ", 40)
        sOutRec = sOutRec & Format(!InvQty, "00000000") & "+"
        ts.WriteLine (sOutRec)
        .MoveNext
        DoEvents
     Loop
End With

Open in new window

0
 

Author Comment

by:mdreed
ID: 36918598
So, you have no suggestions or there are no known issues with 'While Not .EOF' as far as you know as to why it intermittently fails to go through the entire recordset ?
0
 
LVL 14

Expert Comment

by:Brook Braswell
ID: 36918795
no...the only thoughts I have on this would be.
1.  As mentioned earlier.  A sort has been put on the rsINV and a value in the data has changed to sets your pointer to a lower position.  Based on your code example I do not see this as an issue.
2.  Perhaps the SQL has a limit of the number of rows to come across at one time.  
     This is only a slim possibility and even then, it would be unlikely that the rsInv is limited as your move through the rows.
3.  Your Query to populate the rsINV is not getting the number of rows you think it is.

How many rows are you expecting ?
How many rows are in the rsINV at the time you start ?
How many rows are actually exported ?

0
 
LVL 14

Expert Comment

by:VBClassicGuy
ID: 36919038
This may or not help. In some weird circumstances, I've encountered cases where .RecordCount was inaccurate (God only knows why, and I can't duplicate the scenario). Anyway, in these cases i use this statement which always seems to be accurate:

Dim NumberOfRecords As Long   'use another data type if need be
SELECT COUNT (*) AS [RecCnt] FROM [Table_Name]
NumberOfRecords = rs!RecCnt
0
 
LVL 14

Accepted Solution

by:
Brook Braswell earned 1500 total points
ID: 36919139
VBClassicGuy:
I have seen these cases as well and a trick I learned that repaired that for the RS was this...

rsInv.MoveLast
rsInv.MoveFirst

' then carry on as usual
0
 
LVL 14

Expert Comment

by:VBClassicGuy
ID: 36919328
Thanks Brook1966, that's good to know.
0
 

Author Comment

by:mdreed
ID: 36919621
I am going to run the routine 20 or 30 times to see if I can reproduce the problem at will.  I that yields nothing, I will try some of the above suggestions.

The concern I have over the .RecordCount suggestion above is that I am not using the .RecordCount value for my loop.  Once I have done some testing, I will provide whatever results I find.
0
 

Author Closing Comment

by:mdreed
ID: 36970672
After much testing, I cannot reproduce the condition at will.  As a stab in the dark, I will add the .movelast before the .movefirst to see if there is a reoccurrance.

Although I have not been provided a known solution (as far as I know), I will award the points anyway.

Thanks to all for input.
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses
Course of the Month8 days, 11 hours left to enroll

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question