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
Solved

ADO Recordset Error

Posted on 2000-04-11
7
712 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
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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:
bhess1 earned 25 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:bhess1
ID: 2708700
No problem - that's why we're here :)
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

789 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