Solved

ADO Recordset Error

Posted on 2000-04-11
7
703 Views
Last Modified: 2013-11-25
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.
0
Comment
Question by:jasonboetcher
  • 2
  • 2
  • 2
  • +1
7 Comments
 

Expert Comment

by:bsmith11
Comment Utility
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
 

Author Comment

by:jasonboetcher
Comment Utility
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
 

Expert Comment

by:bsmith11
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 1

Expert Comment

by:sramkris
Comment Utility
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
 
LVL 32

Accepted Solution

by:
bhess1 earned 25 total points
Comment Utility
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
 

Author Comment

by:jasonboetcher
Comment Utility
Thanks, the MoveNext after the Delete worked great!
0
 
LVL 32

Expert Comment

by:bhess1
Comment Utility
No problem - that's why we're here :)
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now