PRB: MoveNext Method Sometimes Skips Rows Or Reaches EOF When Recordset Is Sorted (245344)


The information in this article applies to:

  • Microsoft Data Access Components 2.5
  • Microsoft Data Access Components 2.6
  • Microsoft Data Access Components 2.7
This article was previously published under Q245344

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. Modify sConnect to connect to your SQL Server.

    Note You must change User ID <User ID> and password <Strong Password> to the correct values before you run this code. Make sure that User ID has the appropriate permissions to perform this operation on the database. 
        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=<User ID>;PWD=<Strong Password>;"
    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             'Positions on 1, 'A'
    rs.Sort = "F1"           'Still at 1, 'A'
    rs!F1 = "C"              'Repositions between 2, 'B' and 3, 'D'
    Debug.Print rs!K1, rs!F1 '1, 'C'
    rs.MoveNext              'Now at 3, 'D'
    Debug.Print rs!K1, rs!F1 '3, 'D'
    rs!F1 = "F"              'Repositions after 4, 'E'
    Debug.Print rs!K1, rs!F1 '3, 'F'
    rs.MoveNext              'Repositions after the last record
    Debug.Print rs.EOF
  4. Run the project.

    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).
Modification Type:MajorLast Reviewed:11/5/2003
Keywords:kbDatabase kbfix kbprb KB245344
©2003 Microsoft Corporation. All rights reserved.