Professional ADO RDS Programming with ASP by Matt Brown, Charles Caison, John Papa, Peter DeBetta, Eric Wilson Published by Wrox Press 1999; $49.99US; ISBN: 1861001649 500 pages Sample Chapter 6: Building on the Recordset Foundation Copyright 1999. Wrox Press, reprinted with permission. The text format was modified to fit the format of this web site.

Related Links
• In-depth review of the book

Batch Updates

Batch updating allows you to request records, bring them to the client, make updates to the records on the client side, and then send the record updates back to the data source at some other time. We use batch updating when we cannot or do not wish to maintain a constant connection to the database. For example, if you write an employee expense system that can be accessed from anywhere around the world, you will fetch data from a data source and carry it back to the application. In an application like this, you neither want to nor can you keep a connection open to your database. Records must be sent back and forth in batches. Scalable applications require that connections be released as soon as possible; therefore updates should be done either through action queries or via disconnected recordsets. In this venue, and in multi-tier applications, batch updates become very important.

Cursor Location

For batch updating your cursor location must be client-side. By default, cursors are built on the server – so you must explicitly request that the cursor location be set to the client. You can set the cursor location on a Connection object, like this

Set objRS = CreateObject("ADODB.Recordset")
objRS.CursorLocation = adUseClient

Alternatively, you can set the cursor location on a Recordset object:

Set objConn = CreateObject("ADODB.Connection")
objConn.CursorLocation = adUseClient

When you use client-side cursors, note that only static cursor types are supported. If you choose another cursor type, then ADO will silently use a static type instead.

Lock Type

The next setting that is required to enable batch updating is the correct record locking type. Recall that record locking has to do with the concurrency of the data that you are changing. When we are requesting batch updates, we don't want a user to lock records back in the database while he's holding the records. That would be bad – a second user who need to access the same records would be unable to get at them. Even worse, if the first user failed to return and unlock the records, our other users might never be able to get at them.

Thus, we have a special setting for record-locking – adLockBatchOptimistic. Optimistic updating means that we will only lock records when a change comes through from a user (during the BatchUpdate call).

The lock type can be set one of two ways on the recordset. We can set the LockType property on the Recordset object:

Set objRS = CreateObject("ADODB.Recordset")
objRS.LockType = adLockBatchOptimistic

Alternatively, when we open the recordset we can send in the LockType as the fourth parameter of the Open method (notice that we also have a static cursor type):

objRS.Open strSQL, strConnect, adOpenStatic, adLockBatchOptimistic

When you are ready to send changes to the database you will call the UpdateBatch method of the recordset. If you need to cancel the changes that you made thus far to your recordset you use the CancelBatch method.

Here is an example that will tie together all of these steps for updating your data:

Set objRS = CreateObject("ADODB.Recordset")

'Use the correct location and cursor
objRS.CursorLocation = adUseClient
objRS.CursorType = adOpenStatic

'Open the recordset
objRS.Open "select Done from Tasks where date='12/12/98'", strConnect

Do While Not objRS.EOF
  objRS("Done") = "Y"
  'Use Update to update local recordset
  objRS.Update
  objRS.MoveNext
Loop

Now the user decides if the changes should go through:

If Request("CommitChanges") Then
  'Update the entire batch if the user has decided to
  objRS.UpdateBatch
  Response.Write "Changes sent"
Else
  'Cancel the update if the user changes his mind
  objRS.CancelBatch
  Response.Write "Changes canceled"
End If

'Close the recordset
objRS.Close

Remember that if some of the updates fail you can use the filter setting adFilterConflictingRecords to find out what records failed to update. There's more about conflicts in Chapters 5 and 8.