BUG: Navigating Through an ADO Recordset Clone May Generate Error -2147217885

• ActiveX Data Objects (ADO), versions 2.0, 2.01, 2.1, 2.1 SP1, 2.1 SP2

SYMPTOMS

Performing the following steps on a disconnected ADO recordset:

1. Sort the recordset.



2. Delete a record from the recordset.



3. Clone the recordset.



4. Navigate through the recordset Clone using the MoveNext method.

Run-time error '-2147217885(80040e23)': A given HROW referred to a hard- or soft-deleted row

1. Delete a record from the recordset.



2. Clone the recordset.



3. Filter the recordset Clone.



4. Navigate through the recordset Clone using the MoveNext method.

STATUS

Microsoft has confirmed this to be a bug in the Microsoft products listed at the beginning of this article.

MORE INFORMATION

Steps to Reproduce Behavior

1. Create a new Visual Basic project. Form1 is created by default.



2. Add a Project Reference to Microsoft ActiveX Data Objects Library 2.0 or Microsoft ActiveX Data Objects Library 2.1.



3. Add a command button to Form1. Command1 is created by default.



4. Paste the following code into the General Declaration section of Form1. Replace sql_server_name with the name of your SQL Server:




Dim rs As ADODB.Recordset
Dim rsClone As ADODB.Recordset
Dim Cn As ADODB.Connection

Private Sub Command1_Click()

Set Cn = New ADODB.Connection
Set rs = New ADODB.Recordset

' Open the connection
Cn.Open "Provider=SQLOLEDB;Data Source=sql_server_name;User ID=sa;Password=;Initial Catalog=Pubs;"

'Open a disconnected ADO recordset
 With rs
    .CursorLocation = adUseClient
    .LockType = adLockBatchOptimistic
    .Open "SELECT * FROM Titles", Cn

    'Disconnect the recordset
      Set .ActiveConnection = Nothing
      Cn.Close
      Set Cn = Nothing
 End With

'Perform the Sort/Delete/Clone/MoveNext steps, to generate the error
 With rs
    ' Sort the recordset
      .Sort = "pub_id"
      .MoveLast
    ' Delete current record in the recordset
      .Delete
    ' Take a clone of the recordset
      Set rsClone = .Clone
 End With

' Filter the recordset Clone
 rsClone.Filter = "pub_id <= 1000"

' Navigate through the cloned recordset
 With rsClone
    Do While Not .EOF
        ' Error occurs on this next line
         .MoveNext
    Loop
 End With

' Clean up
 rsClone.Close
 Set rsClone = Nothing
 rs.Close
 Set rs = Nothing

 End Sub 

5. Run the project. You may get error -2147217885.



6. Comment out either of the following lines of code:




.Sort = "pub_id" 

-or-

rsClone.Filter = "pub_id <= 1000" 

7. Run the project. You should now be able to run without receiving error -2147217885.

Additional query words: