14.2.2- Updating Existing Data

Updating Existing Data

Updating an existing database record involves almost the same process, except there's no new record to add so you don't need to issue an AddNew call. Once you've got a recordset containing the data that you want to change, you make the changes within the recordset (by maneuvering the cursor to the record in question, and applying the changes to the record) and then using the Update method to update those changes to the database itself:

For example, this amends the record in the recordset by changing the value of the Genre field to Thriller, and then updates the database with that change:

objRS("Genre").Value = "Thriller"

objRS.Update

It's also worth noting that if you make changes to your recordset and then call any of the Move… methods, the Move… method call will implicitly call the Update method and update the data store. So for example, the following two lines have the effect of changing the value in the Genre field, updating that change in the database and then moving the cursor to the first record in the recordset:

objRS("Genre").Value = "Thriller"

objRS.MoveFirst

There's one exception to this, which is when you're working in batch update mode.

Batch Updates

If you're working in batch update mode, it means that you can make multiple changes to multiple records in the recordset and then send all of those changes back to the data store in one go. In other words, ADO doesn't update the data store every time you move from one record to another – it just waits until you use the UpdateBatch method to update the entire batch of changes.

If you want to use batch update mode, you need to set the LockType property of the recordset to be adLockBatchOptimistic. For example, you can do this in the fourth parameter of the Open method when you open a recordset:

objRS.Open "Movies", strConnect, adOpenStatic, adLockBatchOptimistic, adCmdTable

Having made some changes to the recordset, you can choose to update the data store by calling the UpdateBatch method. The UpdateBatch method has one optional parameter, which (if you use it) can take one of the following ADO constant values:

• adAffectCurrent, which means that the update will note which record the cursor is currently pointing to, and will only write changes relating to that record
• adAffectGroup, which means that the update will write changes to records that match the current Filter property
• adAffectAll, which means that the update will write all pending changes (this is the default value)

So you could have some code like this:

objRS.Open "Genres", strConnect, adOpenStatic, adLockBatchOptimistic, asCmdTable

objRS("Genre").Value = "Horror"

objRS.MoveNext ' doesn't update

objRS("Genre").Value = "Comedy"

objRS.UpdateBatch adAffectAll ' updates all changes since the previous update

Cancelling Changes

You'll notice that, when you make changes to a record in the recordset, those changes aren't updated on the data store immediately. In fact, they're not made until you call another method (such as Update or MoveFirst, for example). This means that, having made the changes on the recordset, you have a chance to undo those changes before they reach the recordset. In order to undo those changes, we use the CancelUpdate method:

If blnMakeTheChange = "True" Then

objRS.Update

Response.Write "Data store updated"

Else

objRS.CancelUpdate

Response.Write "Changes to recordset undone; changes to data store not made "

End If

The CancelUpdate method undoes all the changes made to the recordset since the last time the data store was updated.

If you're making changes in batch update mode, the get-out clause involves the CancelBatch method instead:

objRS.CancelBatch adAffectAll

Like UpdateBatch, the parameter is optional and can take any of the values adAffectCurrent, adAffectGroup or adAffectAll.