Step 5: Update the Data (ADO Tutorial)

See Also   

You are Here...

• Make a connection to a data source.
  
  
• Optionally, create an object to represent an SQL query command.
  
  
• Optionally, specify values in the SQL command as variable parameters.
  
  
• Execute the command. If the command is row-returning, store the rows in a storage object.
  
  
• Optionally, navigate, examine, manipulate, and edit the data.
  
  
• If appropriate, update the data source with changes from the storage object. Optionally, embed the update in a transaction.
  
  
• If a transaction was used, accept or reject the changes made during the transaction. End the transaction.

Discussion

You've just changed the data in several rows of the Recordset. ADO supports two basic concepts for the addition, deletion, and modification of rows of data.

The first notion is that changes aren't immediately made to the Recordset; instead, they are made to an internal copy buffer. If you decide you don't want the changes, then the modifications in the copy buffer are discarded. If decide you want to keep the changes, then the changes in the copy buffer are applied to the Recordset.

The second notion is that changes are either propagated to the data source as soon as you declare the work on a row complete (that is, immediate mode) or else all the changes for a set of rows are collected until you declare that the work for the set is complete (that is, batch mode). These modes are governed by the CursorLocation and LockType properties.

In immediate mode, each invocation of the Update method propagates the changes to the data source. In batch mode, each invocation of Update or movement of the current row position saves the changes to the Recordset, but only the UpdateBatch method propagates the changes to the data source. You opened the Recordset in batch mode, so you'll update in batch mode.

Note   There is a convenience form of Update in which you apply a change to a field, or an array of changes to an array of fields, then perform the update, all in one step.

Optionally, you can perform your update in a transaction. In practice, you would use a transaction to ensure that several related operations that depended on each other either all occurred successfully, or else were all canceled. In this case, a transaction isn't really necessary.

Transactions typically allocate and hold limited resources on the data source for long periods of time. For that reason it is advisable that a transaction exist for as brief a period as possible. (That's why this tutorial didn't begin the transaction as soon as you made a connection.)

For the tutorial, bracket your batch update in a transaction:

conn.BeginTrans
rs.UpdateBatch
...

Next   Step 6