Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 243
  • Last Modified:

ADO Bug?

I have an ado recordset populated with records.  I am stepping through the recordset backwards and deleting each record.

The first time I attempt to decrement the loop (after deleting my first record) it skips a record and I end up at the third record.  This never happens again throughout the process.  The result is every record gets deleted except for the second record.

        sql = "Select * from tblmeetings where recurringmeetingid = " & mRecurringMeetingID
       
        Set MeetingRS = New Recordset
       
        MeetingRS.Open sql, DBConnect, adOpenDynamic, adLockOptimistic
        If Not MeetingRS.EOF Then
            MeetingRS.MoveLast
            Do While Not MeetingRS.BOF
                MeetingRS.MovePrevious
            Loop
        End If
0
Quatro
Asked:
Quatro
  • 3
  • 2
1 Solution
 
QuatroAuthor Commented:
I made a mistake in the algorithm I posted.  It should look like this:


       sql = "Select * from tblmeetings where recurringmeetingid = " & mRecurringMeetingID
       
       Set MeetingRS = New Recordset
       
       MeetingRS.Open sql, DBConnect, adOpenDynamic, adLockOptimistic
       If Not MeetingRS.EOF Then
           MeetingRS.MoveLast
           Do While Not MeetingRS.BOF
               MeetingRS.Delete 'This line was forgotten in the last post
               MeetingRS.MovePrevious
           Loop
       End If
0
 
hongjunCommented:
How about this
Do While Not MeetingRS.BOF AND Not MeetingRS.EOF


hongjun
0
 
QuatroAuthor Commented:
I tried something similar to that.  The problem is that even though it skips a record, it still finishes at the BOF record.  As a result it would then exit the loop.

Also the code I posted is slightly simplified in comparison to the real algorithm.  The real one choosed not to delete certain records so I can't even continually loop until every record is deleted.

I have used this work around for now but its dodgy as hell and I really don't want to do it this way:

This is the real function in total:

Public Function RemoveAssociatedMeetings(Optional RemoveOnlyNonUserAlteredRecs As Boolean = True)
    Dim MeetingRS As Recordset
    Dim sql As String
    Dim TestPass As Long
    'Remove instances of the meeting from all meetings created by the recurrence
    'Deleting those that were not altered by the user
    If mRecurringMeetingID > 0 Then
        TestPass = 1
       
        'This loop is necessary due to a bug in ado that causes the second record to be skipped
        'This will run through the recordset as many times as it needs to until all records
        'that need to be deleted are removed.
        Do While TestPass <> 0
            sql = "Select * from tblmeetings where recurringmeetingid = " & mRecurringMeetingID
           
            Set MeetingRS = New Recordset
           
            MeetingRS.Open sql, DBConnect, adOpenDynamic, adLockOptimistic
           
            TestPass = 0
            If Not MeetingRS.EOF Then
                MeetingRS.MoveLast
                Do While Not MeetingRS.BOF
                    If (MeetingRS!UserAltered = True And RemoveOnlyNonUserAlteredRecs = True) Or CLng(MeetingRS!RDate) < CLng(VBA.Date) Then
                        MeetingRS!RecurringMeetingID = 0
                    Else
                        TestPass = 1
                        MeetingRS.Delete
                    End If
                   
                    MeetingRS.MovePrevious
                Loop
            End If
            MeetingRS.Close
            Set MeetingRS = Nothing
        Loop
    End If
End Function
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
rovermCommented:
First of all:
If you want to delete all records, why don't you use the DELETE SQL statement:

"DELETE FROM tblmeetings where recurringmeetingid = " & mRecurringMeetingID

Second:
Use an UPDATE (or UPDATEBATCH if the recordset is disconnected) af each deletion:

Do While Not MeetingRS.BOF
  MeetingRS.Delete
  MeetingRS.Update
  MeetingRS.MovePrevious
Loop

Last:
Why loop backwards ? If you always delete the first record the EOF will automaticly be set:

MeetingRS.MoveFirst
Do While Not MeetingRS.EOF
  MeetingRS.Delete adAffectCurrent
Loop

D'Mzzl!
RoverM
0
 
QuatroAuthor Commented:
> "DELETE FROM tblmeetings where recurringmeetingid = " & mRecurringMeetingID

The sql will be a little more complex than that but I think I can use it.


>Second:
>Use an UPDATE (or UPDATEBATCH if the recordset is disconnected) af each deletion:

>Do While Not MeetingRS.BOF
> MeetingRS.Delete
> MeetingRS.Update
> MeetingRS.MovePrevious
>Loop

I tried that and it made no difference.

>Why loop backwards ? If you always delete the first record the EOF will automaticly be set:

My original code was looping forwards.  When I encountered this same bug I thought it was because the deleted record was no longer part of the index and therefore I was skipping records.  Also I work with n-tier models and use custom object models a lot where it is always good practice to loop backwards if you are deleting.

I will get back to you once I've attempted your first suggestion.

Thanks,

Quatro
0
 
rovermCommented:
Thanks for the points!

D'Mzzl!
RoverM
0

Featured Post

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!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now