How To Undo Updates for a Single Row with CancelBatch (172375)

SUMMARY

The Visual Basic documentation states that the Status property of an rdoResultset object can be modified to arrange whether the current row will be updated and if it is updated, how it will be updated. It is a common desire to set the Status to rdRowUnmodified so that no updates are made to the current row. However, this is an error in the documentation because the Status property is read-only and cannot be modified at run-time.

Although less intuitive, undoing changes for the current row of an rdoResultset object can be accomplished with the CancelBatch method using the optional parameter, SingleRow.

MORE INFORMATION

When you use the optimistic batch cursor library, the CancelBatch method of the rdoResultset object cancels all uncommitted changes in the local cursor. By using the optional parameter, SingleRow, you can cancel a change to a particular row only.

Step-by-step example

Start Visual Basic and create a standard EXE. Form1 is added by default.
Add a reference to "Microsoft Remote Data Object 2.0" in Project/ References.
Add two CommandButtons, called Command1 and Command2, to Form1.
Set the Caption property of Command1 to "Edit and Undo" and the Caption property of Command2 to "Delete and Undo".

Copy and paste the following code to the General Declarations section of Form1.

Note You must change UID <username> and PWD <strong password> to the correct values before you run this code. Make sure that UID has the appropriate permissions to perform this operation on the database.

     Dim cn As New rdoConnection
     Dim rs As rdoResultset

     Private Sub Form_Load()

         cn.CursorDriver = rdUseClientBatch
         cn.Connect = "dsn=my_pubs_server;uid=<username>;pwd=<strong password>"
         cn.EstablishConnection rdDriverNoPrompt, False
         Set rs = cn.OpenResultset("select * from authors", rdOpenStatic, _
                                   rdConcurBatch)

     End Sub

     Private Sub Command1_Click()

         rs.MoveFirst
         rs.Edit
         rs(1) = "New Value"
         rs.Update

         MsgBox "Status before CancelBatch: " & rs.Status
         rs.CancelBatch SingleRow:=True
         MsgBox "Status after CancelBatch: " & rs.Status

     End Sub

     Private Sub Command2_Click()

         rs.MoveFirst
         rs.Delete
         rs.MoveNext

         On Error Resume Next
         rs.AbsolutePosition = 1
         MsgBox "Status before CancelBatch: " & rs.Status
         rs.CancelBatch SingleRow:=True
         MsgBox "Status after CancelBatch: " & rs.Status

     End Sub

     Private Sub Form_Unload(Cancel As Integer)

         rs.Close
         cn.Close

         Set rs = Nothing
         Set cn = Nothing

     End Sub

Finally, press the F5 key to run.

NOTE: In Command2_Click, the Move methods do not recognize deleted rows. Therefore, the AbsolutePosition property was used to position the current row to the deleted row. Also, run-time error "40056" occurs when the current row positions to a deleted row. By using "On Error Resume Next", this error can be trapped and ignored.