ADO Recordset Error

I am using an ADO Data Control linked to an Apex True DBGrid Pro 6.0 datagrid.  When I delete a record a first time everything is fine.  After the row is deleted it disappears from the datagrid.  If without moving the cursor, an attempt is made to delete the next record the following error occurs:

"-2147217885  A given HROW referred to a hard- or soft-deleted row."

I have no idea what the heck this means.  Here is my code that deletes the record:

Private Sub cmdDelete_Click()
  On Error GoTo cmdDeleteErr
 
  If adoData.Recordset.RecordCount = 0 Then Exit Sub
 
  Beep
  If vbNo = MsgBox("Delete the highlighted transaction?", vbQuestion + vbYesNo + vbDefaultButton2, "Confirmation") Then Exit Sub
 
  Screen.MousePointer = vbHourglass
  adoData.Recordset.Delete
  'Update label counter to reflect record deletion
  lblCount = adoData.Recordset.RecordCount
  Screen.MousePointer = vbDefault
 
  Exit Sub
 
cmdDeleteErr:
  Screen.MousePointer = vbDefault
  Beep
  MsgBox Err & "     " & Error
End Sub


Any ideas on how to alleviate this error will be greatly appreciated.
jasonboetcherAsked:
Who is Participating?
 
Brendt HessConnect With a Mentor Senior DBACommented:
You'll probably need to do a MOVENEXT on your recordset, and/or set focus to the new row.

If you delete a record from a recordset, the deleted record remains current.  From the VB help file:

"In an updatable Recordset object, Delete removes the current record and makes it inaccessible. Although you can't edit or use the deleted record, it remains current. Once you move to another record, however, you can't make the deleted record current again. Subsequent references to a deleted record in a Recordset are invalid and produce an error."
0
 
bsmith11Commented:
Did you try Refreshing the recordset after the delete was done.  The error sounds like the record was deleted, but a commit was not done on the delete.
0
 
jasonboetcherAuthor Commented:
I changed my code to the following, adding the "Update" method to the recordset after running the "Delete" method and received the same error.  My initial thought was the same as yours but I can't find any other property, method, or event of a recordset to try that might fix my problem.  Any other ideas?




Private Sub cmdDelete_Click()
  On Error GoTo cmdDeleteErr
 
  If adoData.Recordset.RecordCount = 0 Then Exit Sub
 
  Beep
  If vbNo = MsgBox("Delete the highlighted transaction?", vbQuestion + vbYesNo + vbDefaultButton2, "Confirmation") Then Exit Sub
 
  Screen.MousePointer = vbHourglass
  adoData.Recordset.Delete
  adoData.Recordset.Update
  'Update label counter to reflect record deletion
  lblCount = adoData.Recordset.RecordCount
  Screen.MousePointer = vbDefault
 
  Exit Sub
 
cmdDeleteErr:
  Screen.MousePointer = vbDefault
  Beep
  MsgBox Err & "     " & Error
End Sub
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
bsmith11Commented:
What about the resync or requery methods?  There is some information in the help on these.  Another option might be to close the recordset and re open it.
0
 
sramkrisCommented:
Performing the following steps on a disconnected ADO recordset:

Sort the recordset.


Delete a record from the recordset.


Clone the recordset.


Navigate through the recordset Clone using the MoveNext method.


may generate the following error:
Run-time error '-2147217885(80040e23)': A given HROW referred to a hard- or soft-deleted row
Performing the following steps on a disconnected ADO recordset may also generate error -2147217885:
Delete a record from the recordset.


Clone the recordset.


Filter the recordset Clone.


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.

This problem has been fixed in MDAC 2.5.



MORE INFORMATION

Steps to Reproduce Behavior
The following example uses the Pubs database that comes with SQL Server.
Create a new Visual Basic project. Form1 is created by default.


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


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


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
Run the project. You may get error -2147217885.


Comment out either of the following lines of code:


..Sort = "pub_id"
-or-
rsClone.Filter = "pub_id <= 1000"
Run the project. You should now be able to run without receiving error -2147217885
0
 
jasonboetcherAuthor Commented:
Thanks, the MoveNext after the Delete worked great!
0
 
Brendt HessSenior DBACommented:
No problem - that's why we're here :)
0
All Courses

From novice to tech pro — start learning today.