ADO - ADO Recordset CancelUpdate Method

The CancelUpdate method in ADO is used to cancel changes made to the current record in a Recordset before those changes are saved to the data source. It is particularly useful when a user starts editing a record but decides not to save the modifications.

When a record is edited using ADO, the changes are initially held in memory. They are not necessarily written to the database immediately. The CancelUpdate method allows the application to discard those pending changes and restore the record to its previous state.

Purpose of CancelUpdate

The main purpose of CancelUpdate is to prevent unwanted modifications from being saved.

For example, consider a database containing employee information:

Employee ID Name Department Salary
101 Rahul Sales 30000
102 Priya HR 35000

Suppose an application allows the user to edit Priya's salary. The user changes the salary from 35000 to 40000. Before saving, the user decides that the change was incorrect.

The application can call:

rs.CancelUpdate

The pending modification is discarded, and the salary returns to its original value of 35000.

How CancelUpdate Works

The CancelUpdate method is normally used after an application begins editing a record with the Edit or equivalent update process.

A typical sequence is:

  1. Move to the required record.

  2. Begin editing the record.

  3. Change one or more field values.

  4. Decide whether the changes should be saved.

  5. If the changes should be discarded, call CancelUpdate.

  6. The Recordset returns to the state it had before the editing operation.

Conceptually, the process looks like this:

Existing Record
      |
      v
Begin Editing
      |
      v
Modify Field Values
      |
      v
Changes Held Temporarily
      |
      v
CancelUpdate
      |
      v
Original Values Restored

Basic Syntax

The syntax is:

RecordsetObject.CancelUpdate

Here, RecordsetObject represents the ADO Recordset whose pending changes need to be cancelled.

For example:

rs.CancelUpdate

Example

Consider the following VBScript-style example:

Set rs = CreateObject("ADODB.Recordset")

rs.Open "SELECT EmployeeID, Name, Salary FROM Employees", conn, 1, 3

rs.MoveFirst

rs("Salary") = 50000

rs.CancelUpdate

In this example, the salary field is changed while the record is being edited. CancelUpdate then cancels the pending modification.

The important point is that CancelUpdate is intended for discarding pending changes to the current record, rather than deleting the record from the database.

CancelUpdate vs Update

CancelUpdate and Update perform opposite actions during record editing.

The Update method is used when the application wants to save the changes:

rs("Salary") = 50000
rs.Update

The modified value is submitted to the underlying data source.

In contrast:

rs("Salary") = 50000
rs.CancelUpdate

discards the pending modification.

Therefore:

Method Purpose
Update Saves pending changes
CancelUpdate Discards pending changes

This distinction is important when developing applications with editable forms.

CancelUpdate and User Forms

One common use of CancelUpdate is in database forms.

Suppose an employee-management application has an Edit Employee form. A user changes several fields but clicks the Cancel button instead of Save.

The application can use:

rs.CancelUpdate

This allows the form to abandon the current editing operation without applying the changes to the data source.

This approach is useful because users may accidentally enter incorrect information or change their minds before saving.

CancelUpdate Does Not Delete a Record

It is important not to confuse CancelUpdate with the Delete method.

For example:

rs.Delete

is used to mark the current record for deletion.

CancelUpdate, on the other hand, cancels pending modifications to the current record.

Therefore, if a user changes:

Salary = 30,000

to:

Salary = 50,000

and then calls:

rs.CancelUpdate

the record remains in the database. The pending salary modification is simply abandoned.

CancelUpdate with Multiple Field Changes

A record may contain several fields that have been modified.

For example:

rs("Name") = "Anita"
rs("Department") = "Finance"
rs("Salary") = 45000

If these changes are still pending, calling:

rs.CancelUpdate

cancels the pending modifications associated with the current record.

This makes the method useful when an application provides an all-or-nothing editing experience at the record level.

Important Considerations

CancelUpdate should be understood in the context of the Recordset's current editing state. Its behavior is related to changes that are still pending in the Recordset.

If changes have already been successfully submitted to the database using Update, calling CancelUpdate afterward does not function as a general database rollback mechanism.

For larger operations involving several database changes, ADO transactions should be used instead. Transactions provide mechanisms such as BeginTrans, CommitTrans, and RollbackTrans for controlling a group of database operations.

Therefore, CancelUpdate is primarily an editing-level operation, while transaction rollback is a transaction-level operation.

Advantages of CancelUpdate

The CancelUpdate method provides several benefits:

  1. Prevents unwanted record modifications
    Users can abandon changes before they are saved.

  2. Supports editable forms
    It is useful for implementing Cancel buttons in database applications.

  3. Keeps original values available
    Pending changes can be discarded without deleting the record.

  4. Simplifies record editing
    Developers can provide users with a straightforward way to exit an editing operation.

  5. Reduces accidental updates
    Users can review or modify information before committing it.

Difference Between CancelUpdate and CancelBatch

These two methods should also be distinguished.

CancelUpdate deals primarily with pending changes to the current record.

CancelBatch is associated with cancelling pending batch updates in a Recordset.

For example:

CancelUpdate
    |
    +-- Cancels pending changes for the current record

CancelBatch
    |
    +-- Cancels pending batch updates

This distinction becomes particularly important when working with disconnected Recordsets and batch updating.

Summary

The ADO Recordset CancelUpdate method is used to discard pending changes made to the current record before those changes are permanently applied to the data source. It is especially useful in applications that allow users to edit database records and provides a way to implement a Cancel operation.

The basic syntax is:

RecordsetObject.CancelUpdate

The key distinction to remember is:

Update        → Save the pending record changes
CancelUpdate  → Discard the pending record changes
Delete        → Delete the current record

Thus, CancelUpdate is an important ADO feature for safely managing record-level editing and preventing unwanted updates.