Microsoft KB Archive/172375

= How To Undo Updates for a Single Row with CancelBatch =

Article ID: 172375

Article Last Modified on 7/13/2004

-

APPLIES TO


 * Microsoft Visual Basic 5.0 Enterprise Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition

-



This article was previously published under Q172375





For a Microsoft Visual Basic .NET version of this article, see 310370.



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 and PWD 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= ;pwd= " 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.</li></ol>

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.

Additional query words: kbVBp500 kbVBp600 kbdse kbDSupport kbVBp kbRDO

Keywords: kbhowto KB172375

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.