Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

ADO Recordset Error

Posted on 2000-04-11
7
Medium Priority
?
732 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
7 Comments
 

Expert Comment

by:bsmith11
ID: 2705177
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
ID: 2705275
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
ID: 2705308
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Expert Comment

by:sramkris
ID: 2705613
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:
Brendt Hess earned 100 total points
ID: 2705623
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
ID: 2707498
Thanks, the MoveNext after the Delete worked great!
0
 
LVL 32

Expert Comment

by:Brendt Hess
ID: 2708700
No problem - that's why we're here :)
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

604 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