Microsoft KB Archive/245344

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

Article ID: 245344

Article Last Modified on 11/5/2003

-

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.



Steps to Reproduce Behavior
 Start a new project in Visual Basic and choose Standard EXE. Form1 is created by default. Add a reference to Microsoft ActiveX Data Objects 2.5 Library.  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  and 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=;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            '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  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).</li></ol>

Keywords: kbfix kbdatabase kbprb KB245344

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.