?
Solved

ADO Recordset Error

Posted on 2000-04-11
7
Medium Priority
?
726 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
Industry Leaders: 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
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…
Suggested Courses
Course of the Month10 days, 22 hours left to enroll

770 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