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

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.

This behavior is by design.

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).