PRB: MoveNext Method Sometimes Skips Rows Or Reach EOF When Recordset Is Sorted

• Microsoft Data Access Components version 2.5

SYMPTOMS

In cases where the recordset has been sorted and the current record's data is changed, the position can also change. In such cases, the MoveNext method works normally, but be aware that the position is moved one record forward from the new position, not the old position. For example, changing the data in the current record, such that the record is moved to the end of the sorted recordset, means that calling MoveNext results in ADO setting the current record to the position after the last record in the recordset (EOF = True). See the More Information section for the Visual Basic code demonstration.

STATUS

This behavior is by design.

MORE INFORMATION

Steps to Reproduce Behavior

1. Start a new project in Visual Basic and choose Standard EXE. Form1 is created by default.



2. Add a reference to Microsoft ActiveX Data Objects 2.5 Library.



3. Double-click Form1. Copy and then paste the following code under the Form_Load() event:




    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim sConnect As String

    sConnect = "Provider=SQLOLEDB;Data source=MyServer;Database=Pubs;UID=sa;PWD=;"
    cn.Open sConnect
    cn.Execute "If Exists (Select * From sysobjects Where id = object_id('T1')) Drop Table T1"
    cn.Execute "CREATE TABLE T1(K1 INT PRIMARY KEY, F1 VARCHAR(1) )"
    cn.Execute "INSERT INTO T1 VALUES( 1, 'A')"
    cn.Execute "INSERT INTO T1 VALUES( 2, 'B')"
    cn.Execute "INSERT INTO T1 VALUES( 3, 'D')"
    cn.Execute "INSERT INTO T1 VALUES( 4, 'E')"
    rs.CursorLocation = adUseClient
    rs.Open "Select * From T1", cn, adOpenKeyset, adLockBatchOptimistic
    rs.MoveFirst
    rs.Sort = "F1"
    rs!F1 = "C"
    rs.MoveNext
    Debug.Print rs!K1, rs!F1
    rs!F1 = "F"
    rs.MoveNext
    Debug.Print rs.EOF 

4. NOTE: When the first record's F1 column is changed to "C", according the sorting order, MoveNext sets the current record to the record with the value as (3, 'D'). While changing the F1 value to "F", this brings the newly updated row to the last row. Thus, MoveNext sets the current record to the position after the last record in the recordset (EOF = True).

Additional query words: kbdsupport kbgrpmac kbgrpvbdb kbmdac250

Keywords : kbGrpVBDB kbGrpMDAC kbDSupport kbMDAC250fix
Version : WINDOWS:2.5
Platform : WINDOWS
Issue type : kbprb