Solved

ADO Bug?

Posted on 2002-05-04
6
236 Views
Last Modified: 2013-11-23
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
Comment
Question by:Quatro
  • 3
  • 2
6 Comments
 
LVL 1

Author Comment

by:Quatro
ID: 6990211
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
 
LVL 33

Expert Comment

by:hongjun
ID: 6990218
How about this
Do While Not MeetingRS.BOF AND Not MeetingRS.EOF


hongjun
0
 
LVL 1

Author Comment

by:Quatro
ID: 6990222
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
Technology Partners: 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 12

Accepted Solution

by:
roverm earned 200 total points
ID: 6990467
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
 
LVL 1

Author Comment

by:Quatro
ID: 6990666
> "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
 
LVL 12

Expert Comment

by:roverm
ID: 6991724
Thanks for the points!

D'Mzzl!
RoverM
0

Featured Post

Technology Partners: 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!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
IF ELSE Statement in Excel Macro VBA 16 87
RUNRMTCMD from AS/400 12 86
DBCtrlGrid, Delphi, Scroll 7 32
migrate this code to work on android 1 27
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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…

740 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