ADO Recordset Object CancelUpdate Method

Cancels any changes made to the current record or to a new record prior to calling the Update method.

CancelUpdate Method Syntax

recordset.CancelUpdate

CancelUpdate Method Remarks

Use the CancelUpdate method to cancel any changes made to the current record or to discard a newly added record.

Note

You cannot undo changes to the current record or to a new record after you call the ADO Recordset Object Update Method unless the changes are either part of a transaction that you can roll back with the RollbackTrans method or part of a batch update that you can cancel with the ADO Recordset Object CancelBatch Method.

If you are adding a new record when you call the CancelUpdate method, the record that was current prior to the ADO Recordset Object AddNew Method call becomes the current record again. If you have not changed the current record or added a new record, calling the CancelUpdate method generates an error.

CancelUpdate Method Examples

These Visual Basic examples demonstrate the ADO Recordset Object Update Method in conjunction with the CancelUpdate method.

Public Sub UpdateX()

Dim rstEmployees As ADODB.Recordset

Dim strOldFirst As String

Dim strOldLast As String

Dim strMessage As String

` Open recordset with names from Employee table.

strCnn = "driver={SQL Server};server=srv;" & _

"uid=sa;pwd=;database=pubs"

Set rstEmployees = New ADODB.Recordset

rstEmployees.CursorType = adOpenKeyset

rstEmployees.LockType = adLockOptimistic

rstEmployees.Open "SELECT fname, lname " & _

"FROM Employee ORDER BY lname", strCnn, , , adCmdText

` Store original data.

strOldFirst = rstEmployees!fname

strOldLast = rstEmployees!lname

` Change data in edit buffer.

rstEmployees!fname = "Linda"

rstEmployees!lname = "Kobara"

` Show contents of buffer and get user input.

strMessage = "Edit in progress:" & vbCr & _

" Original data = " & strOldFirst & " " & _

strOldLast & vbCr & " Data in buffer = " & _

rstEmployees!fname & " " & rstEmployees!lname & vbCr & vbCr & _

"Use Update to replace the original data with " & _

"the buffered data in the Recordset?"

If MsgBox(strMessage, vbYesNo) = vbYes Then

rstEmployees.Update

Else

rstEmployees.CancelUpdate

End If

` Show the resulting data.

MsgBox "Data in recordset = " & rstEmployees!fname & " " & _

rstEmployees!lname

` Restore original data because this is a demonstration.

If Not (strOldFirst = rstEmployees!fname And _

strOldLast = rstEmployees!lname) Then

rstEmployees!fname = strOldFirst

rstEmployees!lname = strOldLast

rstEmployees.Update

End If

rstEmployees.Close

End Sub

This example demonstrates the Update method in conjunction with the AddNew method.

Public Sub UpdateX2()

Dim cnn1 As ADODB.Connection

Dim rstEmployees As ADODB.Recordset

Dim strEmpID As String

Dim strOldFirst As String

Dim strOldLast As String

Dim strMessage As String

` Open a connection.

Set cnn1 = New ADODB.Connection

strCnn = "driver={SQL Server};server=srv;" & _

"uid=sa;pwd=;database=pubs"

cnn1.Open strCnn

` Open recordset with data from Employee table.

Set rstEmployees = New ADODB.Recordset

rstEmployees.CursorType = adOpenKeyset

rstEmployees.LockType = adLockOptimistic

rstEmployees.Open "employee", cnn1, , , adCmdTable

rstEmployees.AddNew

strEmpID = "B-S55555M"

rstEmployees!emp_id = strEmpID

rstEmployees!fname = "Bill"

rstEmployees!lname = "Sornsin"

` Show contents of buffer and get user input.

strMessage = "AddNew in progress:" & vbCr & _

"Data in buffer = " & rstEmployees!emp_id & ", " & _

rstEmployees!fname & " " & rstEmployees!lname & vbCr & vbCr & _

"Use Update to save buffer to recordset?"

If MsgBox(strMessage, vbYesNoCancel) = vbYes Then

rstEmployees.Update

` Go to the new record and show the resulting data.

MsgBox "Data in recordset = " & rstEmployees!emp_id & ", " & _

rstEmployees!fname & " " & rstEmployees!lname

Else

rstEmployees.CancelUpdate

MsgBox "No new record added."

End If

` Delete new data because this is a demonstration.

cnn1.Execute "DELETE FROM employee WHERE emp_id = '" & strEmpID & "'"

rstEmployees.Close

End Sub