Working with Recordset Objects
After the Recordset object is constructed, you can set its properties and execute its methods. As long as the Recordset object remains "unopened," you can continue to make changes to its properties. You can even build your own Fields collection, thus describing the data structure itself. However, once the Execute or Open method opens the Recordset based on your design or constructs the Recordset for you (as with the Execute method), all of the Recordset properties except Value are frozen, so it's too late to code changes. A key point here is that the Execute method manufactures a brand new Recordset on its own, using the default Recordset configuration. While many design strategies make efficient use of the default cursor, there are occasions when you must change the base configuration. This is when you must use the Open method or one of the other methods I describe later that leverage an existing Recordset to define the desired Recordset behavior.
Let's look at the Recordset properties and methods and how to make best use of them by discussing them in the following functional groupings:
- Query Management—How the query is specified and executed against a specific data provider.
- Cursor management—How the Recordset cursor is specified prior to execution and how it's manipulated after the rowset is returned.
- Current row management—How the rowset is accessed and how the current row pointer is changed, saved, and moved.
- Update management—How the changes are made to the Recordset object's rowset.
- Concurrency management—How the cursor impacts other competinguser's attempts to access and update the data.
- Transaction management—How you can initiate and commit o rollback transactions from your application or deal with transactions managed elsewhere.
- Status management—How ADO informs you of the condition (state) of the Recordset.
- Recordset persistence—How you can save your Recordset to a file, stream, or retrieve it once it has been saved.
Building Efficient Recordset Objects
All too often, we ask for more than we need when building Recordsets. The default ADO Recordset is very efficient, so ADO's behavior helps you construct a high-performance application or component. Each option you choose that varies from this default behavior costs time and resources to implement.
For example, asking for a scrollable Keyset, Static, or Dynamic cursor forces ADO to spend more time preparing keysets and the mechanisms to scroll around in the result set. All of the "complex" bound controls already know how to scroll through the result set, and you don't have to bind to them to get this functionality. Asking ADO to (try to) create an updatable result set against a complex or stored procedure-based query is simply a waste of time—it can't do it. And all too often, your SA doesn't want you to make direct changes to the underlying tables. These changes are reserved for stored procedures that can run business logic, more carefully guard secure data, and maintain referential integrity.
Using the Recordset Open Method
The ADO Recordset Open method is probably the most flexible of all ADO methods. It opens a connection (if you provide a valid connection string instead of a reference to a Connection object), and it executes any given Source string be it the name of a table (really inefficient) or a complex parameter-driven query. You can even execute parameter-queries with the Open method. Here's an example:
Set rs = New Recordset rs.Open "Select title, Year_Published from titles " _ & "where title like '" & txtParm1 & "'" _ & "and year_published between " & txtParm2 & " and " _ & txtParm3, cn, , , adCmdText Set MSHFlexGrid1.Recordset = rs
In this example, I concatenated parameters into the query string passed to the Open method's Source argument.
As I mentioned in Chapter 5, the Open method's Source argument can contain anything that the Command object's CommandText property can contain:
- A SQL query as shown in the preceding code example. This is basically a SELECT (row returning) SQL query.
- The name of a table. This is converted by ADO to a SELECT * FROM <table> query.
- The name of a stored procedure, assuming that the stored procedure does not use input or output parameters.
- The name of a file containing a structure that can be converted to a Recordset, such as an XML, ADTG, or Stream file.
- A URL pointing to a page that can construct a Recordset and pass it back.
By default, ADO goes a long way toward choosing an efficient strategy to fetch your rows. Its default Recordset object is created as a read-only (RO), forward only (FO), cursorless (or firehose) result set.
The Open method assumes your Recordset is already instantiated and ready to go. So, before you use the Open method, you can set the Recordset LockType, CursorType, MaxRecords, CacheSize, and other pertinent properties to optimize the operation.
Set rs = New Recordset SQL = "Select title, Year_Published from titles where title like 'Hitch%' " rs.ActiveConnection = cn rs.Source = SQL rs.CursorType = adOpenStatic rs.LockType = adLockOptimistic rs.Open Options:=adCmdText rs.Open SQL, cn, Options:=adCmdText
| Tip | Before you call the Recordset Open method, make sure that ADO and the Recordset are not "busy" managing the results of a previous query—even one that does not return a rowset. For example, if you execute the Open method using an INSERT statement as the Source argument, and then follow this execution immediately with another Open, ADO would still be busy dealing with the first Recordset and would not permit another operation. In any case, it does not make sense to use the Open method unless you expect to get a rowset. If the query does not return a rowset, simply use the Execute method. |
If all you want to change is the lock type or cursor type, you can pass these property settings as arguments to the Recordset object's Open method. The following example illustrates setting Recordset properties and using the Open method. However, the Source, CursorType, LockType, and CommandType (options) can all be set as Open method arguments, so setting individual properties in this manner is not particularly efficient.
Set rs = New Recordset SQL = "Select title, Year_Published from titles where title like 'Hitch%' " rs.Open SQL, cn, adOpenStatic, adLockOptimistic, adCmdText
If you construct an ADO Command object (declared as cmd) and simply want to create a Recordset based on the query specified in its properties, you can simply execute:
rs.Open cmd
| Tip | Using the Recordset object's Open method has one side effect—an ADO Command object is constructed behind the scenes to manage the query from the Recordset properties and options you provide in code before using the Recordset object's Open method. As far as I'm concerned, this means that you won't gain any COM efficiency by trying to avoid constructing an ADO Command object—it's created anyway. |
When you use the Recordset object's Open method, be sure to set the last argument (Options) to match the type of command in the Source argument. Choose from one of the CommandType enumerated options. For example, if you are executing a stored procedure, use:
rs.Open MySPNoParms, cn, adOpenStatic, , adCmdStoredProc
You might want to use the named argument approach, which helps eliminate problems associated with not being able to count commas at 3:00 a.m.
rs.Open MySPNoParms, cn, adOpenStatic, Options:=adCmdStoredProc
| Note | You can't use named arguments with Visual Basic Script (as in ASP). At least not until ADO 2.6. Stick with the commas and count carefully until SQL Server 2000, which is when ADO 2.6 arrives. |
specifying the command type in the Options argument eliminates the need to ask the provider (or server) to identify what you are passing in the Source argument. This simple addition will make a measurable difference in your query's performance. And, no, ADO is not heuristic. It does not "learn" or "remember" that the last 200 times you ran this query it ended up being a stored procedure or a table reference. ADO still goes through the steps of trying to figure out what you're doing. It's better to give ADO's query processor a hint.
| Note | If your query fails to recognize one of your tables, but you know that the query works, you might have forgotten to set the initial catalog (the default database). If your SA didn't set the correct default, you might be pointing at the MASTER database.You need to either set it yourself in the ConnectionString or send your SA a dozen roses and ask to have this fixed. |
You can also execute parameter queries with the Open method. All you have to do is build a query containing the needed parameters—one way or another. This might seem to save you time because you don't have to create a Command object. However, in this case, you do have to manage the framing quotes yourself, and ADO will create a Command object behind the scenes anyway. For example, try this query:
strS = "Select Author from authors where year_born = " & txtYearWanted rs.Open strS, cn, Options:=adCmdText
Now inspect the Visual Basic IDE Locals window. Notice that the rs.ActiveCommand property is set, pointing to a constructed ADO Command object built to manage the query. The locals window is a good way to debug your query, because you can see what options ADO set for your constructed Command object.
Accessing the Jet Table Object
When you create a Jet-based (.MDB) database, you often lean toward using the Jet Table object. This approach simply permits Jet to access a base table with one of the predefined Jet table indexes. Because Jet is especially good at fetching rows using this technique, it was a shame that you could not get at this technology until ADO 2.1. The downside to this approach is that there is no equivalent operation in SQL Server. This means that if you plan to scale to SQL Server at some point in the future, it is probably best to figure out another approach.
Using the Table object won't work unless you have both a Jet 4.0 database and the OLE DB Jolt 4.0 data provider. When you have these basic ingredients, accessing base tables via a selected index is easy. First, create a new Recordset object against the Jet 4.0 .MDB using the new ADO 2.1 version of the Jolt provider. In this case, you need to create a server-side cursor that Jet will manage for you. The Source property points to the table. Use the Recordset Open method with the adCmdTableDirect option flag. Now point to one of the table's indexes. Each time you use the Seek method, the Recordset is repositioned to the first row matching the Seek argument based on pointers in the selected index.
In the following example, I pass the index name in via a TextBox control:
Dim cn As Connection Dim rs As Recordset Private Sub Form_Load() Set cn = New Connection cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\My Documents\Beta V Corporation\Articles\ODBC API\Jet Direct\New40.mdb;" Set rs = New Recordset With rs .CursorLocation = adUseServer .ActiveConnection = cn .Source = "Customers" .CursorType = adOpenKeyset .LockType = adLockOptimistic .Open Options:=adCmdTableDirect End With End Sub Private Sub SeekButton_Click() Dim intAP As Integer With rs .Index = txtPK.Text .Seek txtSearch.Text End With intAP = rs.AbsolutePosition If intAP > 1 Then With MSHFlexGrid1 Set .Recordset = rs .Row = intAP .RowSel = intAP + 1 .Col = 0 .ColSel = .Cols − 1 End With Else MsgBox "No hits" End If End Sub
| Tip | If you find that the Recordset Index property does not seem to be exposed, remember to set your Visual Basic project reference to ADO 2.1 or later. |
Creating and Opening Recordsets in Memory
ADO introduced a concept never before seen by DAO or RDO developers—the capability to create a Recordset entirely in memory. That is, you can create an almost fully functional Recordset object, populate the Field objects, and add, change, and delete records as necessary without using a database. You can also create a Recordset object from a database but disconnect it from the ActiveConnection (by setting it to Nothing) and keep it in memory as long as necessary.
You can also save (persist) this Recordset to disk, stream, XML, or binary Advanced Data Tablegram (ADTG) format. Be sure to visit Chapter 8, which discusses marshaling Recordsets from tier to tier.
The following code example illustrates the construction of an in-memory Recordset. The application is designed to capture the mouse events that occur on Form1 and record them to a Recordset. When the user clicks a stop button on the form, the Recordset is persisted to a file—in both ADTG and XML formats.
Private Sub Form_Load() Set rs = New Recordset rs.Fields.Append "Time", adDBTimeStamp, , adFldRowID + adFldKeyColumn rs.Fields.Append "Delta", adInteger rs.Fields.Append "Button", adInteger rs.Fields.Append "Shift", adInteger rs.Fields.Append "X", adSingle rs.Fields.Append "Y", adSingle rs.Open ts = GetTickCount bolDoor = True varFields = Array("Time", "Delta", "Button", "Shift", "X", "Y") End Sub The next section of code illustrates two techniques for adding rows to the Recordset, using variations on the AddNew method. The first technique passes two Variant arrays to the AddNew method. The first argument is a Variant array that simply contains the Field names of the columns whose data is to be added. The second argument is another Variant array containing the values associated with each field name in the first array. In the following code example, when an incorrect value is passed to the Field object's Value property, ADO returns the message "-2147217887 Multiple-step operation generated errors. Check each status value." to warn us that one of the fields is incorrect. We'll have to check the Status property of each field to see which Field object is in error, and that can be a real pain to code. Incidentally, this error message occurred in the sample application as I tried to assign a string value to a Field object declared as single.
The second technique does the same thing and is also shown in the following code, but it's commented out. This approach uses a more traditional approach and does not depend on COM having to "look up" the names of the fields. However, recent tests show this approach is actually slower than the array technique. And not only that, but your Value property validation for each Field object is somewhat simpler—at least the errors occur when the field is referenced.[4]
Private Sub Form_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single) If optStart And bolDoor Then bolDoor = False ' Technique one: Using Variant arrays. rs.AddNew varFields, Array(Now, GetTickCount - ts, Button, Shift, X, Y) ' ' Technique two: Using Field-by-Field referencing ' rs(rsFld.Time) = Now ' rs(rsFld.Delta) = GetTickCount - ts ' rs(rsFld.Button) = Button ' rs(rsFld.Shift) = Shift ' rs(rsFld.X) = X ' rs(rsFld.Y) = Y rs.Update i = i + 1 txtEvents = i DoEvents bolDoor = True End If End Sub
One dramatic difference between DAO, RDO, and ADO is how ADO handles pending changes. That is, in both DAO and RDO you have to use the Update method after an AddNew or Field object change, or your changes are lost when you navigate to another row. In ADO, your changes are saved when you navigate to a new row, start another AddNew, or close the Recordset, and, of course, when you execute Update or UpdateBatch. For example, the following code adds two rows to the ValidStates table:
Option Explicit Dim cn As Connection Dim cmd As Command Dim rs As Recordset Dim varStateFlds As Variant Private Sub Form_Load() Set cn = New Connection cn.Open "dsn=localserver", "admin", "pw" varStateFlds = Array("State", "StateCode") Set cmd = New Command Set rs = New Recordset rs.Open "ValidStates", cn, adOpenStatic, adLockOptimistic, adCmdTable rs.AddNew varStateFlds, Array("Unknown", "UK") rs.AddNew varStateFlds, Array("Not Specified", "NS") rs.Close End Sub After the data has been captured, we save the Recordset to disk to two separate files, but just to illustrate a point. The ADTG file format is less than half the size of its XML equivalent. Notice that the Save method does not know how to deal with a file that already exists, so we have to use the Kill function to remove any existing file.
Private Sub optStop_Click() On Error Resume Next Kill "c:\MouseDroppings.adg" Kill "c:\MouseDroppings.xml" On Error GoTo 0 rs.Save "c:\MouseDroppings.adg", adPersistADTG rs.Save "c:\MouseDroppings.xml", adPersistXML End Sub
But what if you wanted to create a stand-alone version of a Recordset but have it clone a data structure derived from a data provider—such as a database? You can still open the Recordset using a SELECT statement that returns the schema desired but no rows. Simply use a WHERE clause that returns no rows.
SELECT … WHERE 1=0
After this empty Recordset is opened, you can dissociate it from the Connection by setting the ActiveConnection property to Nothing and begin to use it as you would any other Recordset.[5] You'll have to specify adUseClient for the CursorLocation to get this to work.
Using Command Objects with the Recordset Open Method
Remember that I said that the Open method was one of the most flexible ADO methods? Well, it can also accept a preconstructed Command object as its Source argument. In this case, ADO takes the Connection and CommandText from the Command object and uses the properties of the Recordset to construct the cursor. Just remember to preset the Command parameters before you use the Open method. The CursorType and LockType properties can also be set as arguments in the Open. Note, however, that the Connection and CommandType is set through the Command object—ADO won't let you override it with Open arguments.
cmd(0) = txtYearWanted cmd.Parameters(0).Value = txtYearWanted Set rs = New Recordset rs.CursorLocation = adUseServer rs.Open cmd, , adOpenKeyset, adLockOptimistic
Setting the Connection in the Open Method
Not only can the Open method set the Source argument (the query) on the fly, but it can also specify the connection. The ActiveConnection argument of the Open method can be set to:
- A valid Connection object
- A valid Connection string
However, you don't need to set the ActiveConnection argument, assuming that the Connection is specified elsewhere, as in the Source argument's reference to a Command object. If you specify a Command object as the Open method's Source argument, you can't override the Command object's ActiveConnection with the Open method's ActiveConnection argument.
This means that our Recordset Open method sample that we used in the previous example or two can be boiled down to one line:
rs.Open "select title from titles where title like 'Hi%'", "dsn=localserver", Options:=adCmdText
This approach does not really cause any serious problems. It constructs the default (firehose) Recordset against the trusted ODBC connection localserver.
Setting Other Recordset Properties before Using Open
If you don't want to accept the default Recordset, you'll have to set some properties using Open method arguments or by setting Properties before the Open method is executed. These control how, where, and when Cursors are created, how ADO handles concurrency issues, how data binding is handled, and how much data is fetched.
The Source Property
The SQL query you want to execute can be placed in the Source property before you use the Open method or can be found there afterwards. That is, if you execute a query using the Recordset.Open method and pass in the SQL to execute as a method argument, the Source property is populated by ADO from the method argument. Because you're working with a Recordset, ADO assumes that the query returns a rowset that will be exposed as an ADO Recordset object, so using action query commands in the Source property (or the Open method's Source argument) does not make sense.
ADO does not care what you put in the Source. It simply passes this through to the data provider as a string to be processed. The data provider might think your query's syntax is incorrect or it might fail to run it as expected for a bazillion other reasons.
We give ADO a hint about what's in the Source argument by setting the CommandType property or by using the Options argument in the Open method. This helps ADO determine whether the Source argument is a SQL command (adCmdText), the name of a table (adCmdTable), the name of a stored procedure (adCmdStoredProc), or a letter to your mom (adCmdUnknown). No single property does more to determine how scalable or fast your application or component is than the Source. It's critical that you make sure you're using the most efficient query possible. I revisit this property repeatedly in this book; for more information review the "Development Strategies" section earlier in this chapter.
Here's a simple example of how you can code the Recordset Open method using a preset Source property:
With rs .CursorLocation = adUseClient .Source = "select author, title from titles t, title_author ta, authors a"_ "where a.au_id=ta.au_id and t.isbn=ta.isbn order by t.type" .Open , cn, adOpenStatic, adLockOptimistic, Options:=adcmdText End With
| Tip | If your query or code has to reference table or field names with embedded spaces, enclose the names in brackets. |
rs.Open "Select [long field name] from [long table name] ", cn Debug.Print rs(0).Name Do Until rs.EOF Debug.Print rs("long field name") ' No, I don't approve of this technique rs.MoveNext Loop
The CursorLocation Property
We discussed the CursorLocation property in Chapter 4, which discussed Connection objects (see the "Setting the CursorLocation Property" section). Each Recordset you create inherits the CursorLocation from the Command object associated with it, and yes, you can change it after the Connection is open. Of course, if your Recordset Open method opens a new connection, creating a new Connection object in the process, the new Connection object's CursorLocation is set from the Recordset CursorLocation property. Make sense?
The CursorType Property
The CursorType property tells ADO how to manage and update the rows returned to your Recordset. The default Recordset behavior is a cursorless (RO/FO) result set, so if you want ADO to manage updates, concurrency, and scrollability, you have to choose an appropriate CursorType.
ADO has a number of limitations on the type and complexity of the cursors its providers can create, based on where the cursors are created. For example, client-side cursors (CursorLocation set to adUseClient) only support Static cursors (adOpenStatic), regardless of what you set the CursorType to. This is expected because, according to the ADO specification, if you set the CursorType to an unsupported value, the closest supported CursorType is used instead—ADO changes the CursorType property to reflect the type of cursor supported by the provider.[6] So, if a provider does not support the requested cursor type, the provider returns a similar type that it does support. If a different cursor type is returned, the CursorType property temporarily changes to match the actual cursor type in use once the Recordset object is open. After you close the Recordset, the CursorType property reverts to its original setting. Confusing?
So, how do you know what's supported by your provider? It's easy. Use the Supports method to determine whether the provider knows how to support specific functions with the returned cursor. For each of the cursor types in the following list to be supported by the provider, the Supports method must return True for all of the Supports method constants that follow:
-
adOpenKeyset: adBookmark, adHoldRecords, adMovePrevious, adResync
-
adOpenDynamic: adMovePrevious
-
adOpenStatic: adBookmark, adHoldRecords, adMovePrevious, adResync
-
adOpenForwardOnly: none
| Note | Although Supports(adUpdateBatch) might be True for Dynamic and Forward-only cursors, you should use either a Keyset or Static cursor for batch updates. Set the LockType property to adLockBatchOptimistic and the CursorLocation property to adUseClient to enable the Cursor Service for OLE DB, which is required for batch updates. |
So, what's the difference between the various types of cursors? Let's look at each in turn.
adOpenForwardOnly
The adOpenForwardOnly cursor is the default CursorType. In a FO cursor, database rows are fetched serially—basically, one at a time from the data source (but in CacheSize groups, at the provider level). As with a Static cursor, the actual data rows are fetched, but with a FO cursor, you can't reposition the current row pointer in any direction except forward (thus forward-only), and only one row at a time. Some providers simply reexecute the query and start over if you attempt to reposition to the first row. You can use the GetRows or GetString method against FO cursors to fetch all of the rows in a single operation.
FO cursors have a fixed membership. That is, rows added to the database after the cache is constructed are not returned as Recordset members. Changes (additions, updates, and deletions) made to the database after the cursor is populated are not reflected in the membership. Changes made to the Recordset by your client affect the locally cached membership and can be posted to the database—depending on the updatability of the Recordset.
Client-side cursor providers do not support FO cursors.
adOpenStatic
For adOpenStatic cursors, as with FO cursors, ADO copies database records into client Static cursor memory in CacheSize groups. However, Static cursors are fully scrollable. That is, after the Recordset is open, you can use any of the Move methods to reposition the current record pointer to any row in the result set.
Static is the only type of cursor supported by the client-side cursor location providers. It is also supported on server-side providers.
adOpenKeyset
The adOpenKeyset option generates a Keyset cursor. In this case, ADO copies only enough key information to the cursor for ADO to fetch specific records based on a WHERE clause generated from the key. Keyset cursors are fully scrollable and updatable. Most server-side providers support Keyset cursors.
| Note | Sybase drivers don't support Keyset or Dynamic cursors—only Static cursors are supported. |
adOpenDynamic
The adOpenDynamic option generates Dynamic cursors that behave very much like Keyset cursors, except for dynamic membership (discussed next). They are fully scrollable but do not support Bookmark positioning. This type of cursor is not supported on more complex result sets. For example, a three-table join prohibits creation of a Dynamic cursor.
If you want to order a Dynamic cursor, you have to do so with an existing index—the data provider can't reorder using an on-the-fly sort because it uses TempDB in a rather non-dynamic way.
Examining Recordset Properties after Opening
When you set a Recordset property and use the Open method, ADO passes these properties along as parameters to low-level OLE DB calls to the data providers. However, the provider might not be capable of implementing the requested option. In this case (and it's pretty typical), the provider usually does not complain (unless it's had a bad day); instead, it substitutes a supportable alternative. These "corrections" are applied to the Recordset properties after the Open method returns the Recordset. If you want to know what was implemented, check the property after opening the Recordset.
For example, if you ask for a Dynamic (adOpenDynamic) CursorType and the provider can't implement this type of cursor (many can't), it might substitute a Keyset cursor instead. In this case, the Recordset object's CursorType property is set to adOpenKeyset after the open. No, a trappable error is not (generally) tripped in this case. However, if you set the CursorType in the Recordset property before opening the cursor, ADO opens whatever cursor it can, based on the provider's capability, and when the Recordset is subsequently closed, the Recordset CursorType property reverts back to the original value.
Using the Supports Method
There are a number of ways to see how ADO constructed the Recordset you requested through the Recordset properties or Open method arguments:
- Inspect the individual Recordset properties after opening the Recordset.
- Inspect the Recordset Properties collection. These properties are referenced (only) by name. The number of properties and their names vary somewhat from provider to provider. I discuss a number of the most interesting Property objects when I discuss situations where they are needed.
- Use the Recordset Supports method. In this case, ADO exposes a number of clearly defined enumerations that the provider returns as Boolean values. For example, you can ask the provider if the Recordset "supports" updatability or scrollability.
The Supports method accepts the ADO cursor option enumerations shown in Table 6-2 and in Table 6-3. The first set of enumerations (Table 6-2) helps determine how the Recordset will handles changes. For example, you can see if the Recordset is updatable (adAddNew or adUpdate) or supports optimistic batch updates (adUpdateBatch).
| CONSTANT | VALUE | DESCRIPTION |
|---|---|---|
| adAddNew | 0×1000400 | Supports the AddNew method to add new records. |
| adUpdate | 0×1008000 | Supports the Update method to modify existing data. |
| adDelete | 0×1000800 | Supports the Delete method to delete records. |
| adUpdateBatch | 0×10000 | Supports batch updating (UpdateBatch and CancelBatch methods) to transmit groups of changes to the provider. |
| adResync | 0×20000 | Supports the Resync method to update the cursor with the data that is visible in the underlying database. |
| adHoldRecords | 0×100 | Retrieves more records or changes the next position without committing all pending changes. |
| adNotify | 0×40000 | Indicates that the underlying data provider supports notifications (which determines whether Recordset events are supported). |
The next set of enumerations (Table 6-3) lists the supported scrolling and repositioning features.
| CONSTANT | VALUE | DESCRIPTION |
|---|---|---|
| adApproxPosition | 0×4000 | Supports the AbsolutePosition and AbsolutePage properties. |
| adBookmark | 0×2000 | Supports the Bookmark property to gain access to specific records. |
| adFind | 0×80000 | Supports the Find method to locate a row in a Recordset. |
| adIndex | 0×100000 | Supports the Index property to name an index. |
| adMovePrevious | 0×200 | Supports the MoveFirst and MovePrevious methods, and Move or GetRows methods, to move the current record position backward without requiring bookmarks. |
| adSeek | 0×200000 | Supports the Seek method to locate a row in a Recordset. |
In the next few pages, I illustrate how to use the Supports method in several useful ways.
Using the Recordset Clone Method
If you want to maintain a separate copy of your Recordset, you can use the Clone method. This technique is used to create multiple, duplicate Recordset objects and is especially valuable when you want to maintain more than one current record in a given set of records. Using the Clone method is more efficient than creating and opening a new Recordset object with the same definition as the original. The current record of a newly created clone is set to the first record.
Changes you make to one Recordset object are visible in all of its clones, regardless of cursor type. However, after you execute Requery on the original Recordset, the clones are no longer synchronized with the original. Closing the original Recordset does not close its copies, nor does closing a copy close the original or any of the other copies.
You can only clone a Recordset object that supports bookmarks. (See the preceding discussion, "Using the Supports Method.") Bookmark values are interchangeable; that is, a bookmark reference from one Recordset object refers to the same record in any of its clones.
Using the Recordset Close Method
You use the Close method to release the Recordset object's result set. This does not destroy the object, but clears out any pending fetches that might still be going on. You won't be able to close the Recordset if there are pending Update operations. In this case, use the Cancel or CancelUpdate method to clear these out before trying to use the Close method. You also won't be able to use the Close method if the Recordset is already closed or when the Recordset is set to Nothing. Closing the Recordset changes its State property from adStateOpen to adStateClosed.
| Tip | If you're trying to fetch the Return status integer or OUTPUT parameters from a stored procedure, you have to use the Close method before referencing the Command object. |
Managing Cursor Membership
Any time you execute a query that includes a SELECT statement, ADO knows that you're going to need a Recordset to manage the rowset returned from the provider. Remember, the default configuration is a firehose (RO/FO, CacheSize=1) cursorless Recordset. Every layer of the data-access interface treats firehose Recordsets differently—they bypass virtually all of the routines needed to provide updatability, scrollability, and caching. On the other hand, if you need to create a true cursor to support scrollability, updatability, or caching, ADO fires up a number of routines to make it happen.
When the data provider receives a SELECT query to execute, it searches for rows that qualify based on the query's WHERE clause. If you create a cursor against a table, all of the rows currently in the table qualify as members. As qualifying rows are located, they are frozen in time using a "share" lock to prevent updates from changing them before the row can be copied to the client. This means an entire table can be locked while its rows are fetched in CacheSize sets. In more recent versions of SQL Server and Jet, the query processor might lock individual rows instead of the entire page where the row is stored. However, as the number of rows involved increases, the chances that the query optimizer (QO) will escalate locks to higher granularity increases—to the point where the entire table is share locked.
As the client accepts rows by populating its cursor or result set, the share locks are gradually released. The last locks are released as the last qualifying row is sent to the client, and not until then. Delaying rowset population can impede other database operations. While other clients can fetch rows with share locks, no client is permitted to change rows while share locks are in place on the row, page, or table. These clients are blocked for CommandTimeout seconds, waiting for the locks to be released.
As the server passes through the result set looking for qualifying rows, the query processor adds any rows it locates to the result set membership list. After the process has begun, if a row is added to the database that would have qualified, but the server has passed where it would have fit in the result set order, the row is not included as a member. However, if a row is added to the database in time to qualify in its correct place, it is added to the Recordset.
After the process of locating the qualifying rows is complete, the membership is frozen for Static and Keyset cursors. This means that rows added to or deleted from the database by other users are not reflected in the frozen cursor membership. Changes to your Recordset are saved in your client's Recordset cache and in the database, if you aren't using batch mode. However, other frozen cursors do not see these changes—not even other Recordsets in your own client. That is, if another user has opened and populated (frozen) a cursor containing the same rows, they won't see your changes until they reference the specific row again. Because Static cursors do not refetch from the server to get the most current data, they won't see your changes at all.
| Note | All client-side cursors are Static. You have to use server-side cursors to get the providers to implement Keyset, Forward-only, or Dynamic cursors. |
Rows are fetched in CacheSize sets, so cached rows have a tendency to lose currency with the membership in a rapidly changing database. That is, rows in the cache might include rows deleted from the database and would not include rows added after the cursor was opened.
For Dynamic cursors, Recordset membership is never frozen because the query is reexecuted each time a new CacheSize set of rows is required, making it very costly to implement and maintain Dynamic cursors.
 |
IMHO IMHO cursors are fundamentally evil. They cause more problems than any other single feature of ADO (or DAO and RDO, for that matter). Generally, this is because cursors are created incorrectly, either with too many rows or with options that only degrade system scalability and performance.
|
Recordset Row Order
I saw an interesting comment on one list I monitor that made me realize that some people don't know the order in which rows are returned to the client. Remember that the DBMS writes new rows to a table in the order in which they are written, unless you define a clustered index for the table. When it comes time to return the rows, unless you specifically sort the result set using an ORDER BY clause, Recordset rows are returned in the order they are located in the table.
If you told the DBMS to create a clustered index, the physical order in the DBMS table would be determined by that index. However, an "ordinary" (nonclustered) index does not affect how rows are stored in the table—simply how they are retrieved. When the QO builds a query plan to fetch rows to satisfy your query request, the QO might (just might) use one of the indexes to fetch the rows if it makes sense. For example, if you fetch too many rows, the QO might decide that navigating the tables through the indexes is more expensive than simply dumping rows from the table. If you just access an indexed table, the order should be based on that index, or on the clustered index, if you have specified one. But fetching rows based on a join (without an ORDER BY clause) can result in rows being returned in pseudo-random order.
When you add rows to your Recordset, the rows are added to the membership and appear at the end of the Recordset—not necessarily where the rows would fit in a sorted order, regardless of any clustered or "ordinary" indexes. Once you refresh your Recordset (reexecute the query), the newly added rows will appear in the correct sequence.
Managing the Recordset Cache Size
When ADO goes out for data from the provider, it does not come back until its bucket is full or until the data can all be fetched at once. You can change the size of that bucket by changing the CacheSize property. This value specifies how many rows are to be managed in the in-memory cache buffer. That is, the providers fetch CacheSize rows from the data source into memory before returning to ADO and your program. This makes ADO and the data provider more efficient.
However, ADO's default CacheSize setting is 1, which would seem to fly in the face of efficient data transfers. But the characteristics of the firehose cursor (FO/RO/CacheSize=1) cause the data provider to bypass ADO's cursor management schemes that take advantage of cache management; thus, the CacheSize setting of 1 has no role in the fetch operation.
| Tip | Your application is blocked (frozen) while the first CacheSize rows are fetched—even if you execute asynchronously. If the server spends considerable time filling the cache, you might consider using a smaller CacheSize setting and asynchronous queries to keep from blocking your application. |
As you move the current row pointer through the Recordset object, the provider returns the data from the local memory buffer. As soon as you move past the last record in the memory cache, the provider retrieves the next CacheSize records from the data source into the cache. If there are fewer than CacheSize records to retrieve, the provider returns the remaining records. If you position to rows previously fetched but no longer in the cache, the provider might have to refetch rows from the server, depending on whether you're using client-side or server-side cursors.
The CacheSize can be adjusted during the life of the Recordset object, but changing this value only affects the number of records in the cache for subsequent retrievals from the data source. Changing CacheSize does not change the current contents of the cache.
Records retrieved from the cache don't reflect concurrent changes that other users made to the source data. That is, if another user in the database deletes a cached row, you won't know until you try to refresh this row from the source table. To force an update of all the cached data, use the Resync method.
Tuning CacheSize
There have been a number of questions on the lists I monitor (internally and externally at Microsoft) concerning how ADO caches rows fetched from the database. When building a client-side cursor (not the default behavior), ADO caches as many rows as the client system can hold. Once the cache is full, the overflow is written to disk. This means that if you are using client-side cursors, after ADO exhausts allocated RAM, it will fill all available disk space on the drive accessed through the \TEMP environment variable.
Frankly, if you are fetching enough rows to fill your disk, you have already made a fundamental mistake. Adjusting the CacheSize property has no effect on this behavior because ADO uses the Recordset CacheSize property to determine the number of rows to fetch and cache from the data provider. While the currently requested row pointer is within the range of cached rows, ADO and the provider just return data from the cache. When you scroll out of the range of cached rows, the cache is released when the next CacheSize rows is fetched. So what CacheSize should you use in your application?
Unfortunately, there isn't a single optimal CacheSize that is appropriate for all applications. You should tune your application with different CacheSize values and use the one that offers the best performance. Using a small CacheSize value significantly improves performance for fetching data from an Oracle data store. Many ADO gurus recommend just leaving the CacheSize set to its default value: 1.
| Tip | You can use the Resync method to flush and refresh the cache. |
Setting the MaxRecords Property
Whenever you fetch rows, your query's WHERE clause should limit the scope of the query to a manageable number. However, you can't always predict how many rows will return. The ADO MaxRecords property can play an important role in limiting the size of result sets as well as the time it takes to fetch them. MaxRecords simply tells ADO to stop fetching rows once n rows have arrived. There might be more rows that qualify, based on your SELECT statement, but no more than MaxRecords will be fetched from the data provider. In some cases, ADO simply passes the MaxRecords value to the provider, so it limits the number of rows to fetch. For example, setting MaxRecords to a nonzero value (e.g., 10) adds the following statement to your query (at least when using the SQL Server data provider):
SET ROWCOUNT 10
| Note | The Jet providers do not recognize the MaxRecords property. The MDAC team recommends that you add a TOP clause to your SELECT statement to limit the number of rows returned. |
Using Cached Data Pages
Not only does ADO manage the physical transportation of data rows from the provider to the client data cache, it also can break up the cache into logical groups, that is, pages. You determine the logical page size by setting the Recordset PageSize property. The PageCount property then returns the number of pages in the Recordset. It's computed by dividing the number of rows by PageSize. If your provider does not support the PageSize property, ADO returns −1 for PageSize.
After you set up the PageSize, you can use the AbsolutePage property (1-based) to reposition the current row pointer to a specific set of rows—n pages into the result set. Cool? Well, almost.
What we really need is logical page management deeper in the query-generation process. The current implementation of logical pages assumes the cursor (evil) is so large (double evil) that your client will want to fetch it in logical sets. That is, for this technology to work, your Recordset has to be large enough to page. This means that the query processor on the server has constructed this large result set and has started sending it to the client. I think a better approach is to have the server's query processor locate the nth page of qualifying data rows. Perhaps in a later version of SQL Server.
Using the Resync Method
Suppose you just added to or updated your Recordset, or you simply want to make sure the data in your Recordset contains the most current information for the current row. Or perhaps you find that records retrieved from the cache don't reflect concurrent changes that other users made to the source data. To force an update of all the cached data, use the Resync method. The Resync method is also useful if you are using either a Static or Forward-only cursor, but you want to see changes in the underlying database. (With Keyset cursors, as you position to specific rows in the result set, ADO refetches current information from the database or the cache. Static and Forward-only cursors don't attempt to keep in sync with the database.)
Unlike the Requery method, the Resync method does not reexecute the Recordset object's underlying command. New records in the underlying database are not visible, so the Recordset membership is unchanged. If the attempt to resynchronize fails because of a conflict with the underlying data (for example, a record has been deleted by another user), the provider returns warnings to the Errors collection and a runtime error occurs. Use the Filter property (adFilterConflictingRecords) and the Status property to locate records with conflicts. Resync is only available for updatable (non-RO) Recordset objects with client-side cursors.
| Note | Support for the Resync method on server-side cursors was dropped after ADO 1.5. Resync is only available for client-side cursors on ADO 2.0 and later. |
If the Unique Table and Resync Command dynamic properties are set, and the Recordset is the result of executing a JOIN operation on multiple tables, the Resync method will execute the command given in the Resync Command property only on the table named in the Unique Table property. Table 6-4 shows how you can modify the Resync method behavior using the optional arguments.
| CONSTANT | DESCRIPTION |
|---|---|
| adResyncAllValues | Default. Overwrites client-side Value property data and pending updates are canceled. |
| adResyncUnderlyingValues | Does not overwrite client-side Value property data and pending updates are not canceled. |
Managing the Current Row
When you run a query that returns a rowset, ADO constructs a Recordset to manage it—even if there are no qualifying rows. That is, a SELECT statement might return no qualifying rows, but ADO still constructs a Recordset to manage the rowset. However, there are a number of situations unique to ADO that you should be aware of. For example, many of the status properties of ADO can't be tested unless the Recordset contains rows, and then they can be tested only if the current row pointer is addressing one of these rows.
The following routine simply tries to test to determine whether an edit is in progress. Because the routine cannot be sure that a valid Recordset exists when it begins, it has to jump through a number of hoops before testing the EditMode property. This was simpler in DAO or RDO.
First, we test to determine whether the Recordset object even exists, by testing it for Nothing. Note that this syntax won't work if you declared your Recordset object variable (rs) using the Dim rs as New Recordset technique.
Private Sub cmdSearch_Click() If rs Is Nothing Then
Next, because we know that the Recordset exists, we test the State property to make sure the Recordset is open—we can't test the EOF or BOF properties until the Recordset exists and is open. Note use of positive instead of double-negative logic here. It's a lot less confusing for old eyes.
ElseIf rs.State = adStateOpen Then If rs.EOF Or rs.BOF Then
Okay, we know that the Recordset object exists and that it's open and not at EOF (or BOF). Now we can test the EditMode property. Whew!
ElseIf rs.EditMode = adEditInProgress Then …
When working with Recordset objects, you need to be prepared for the occasional empty rowset and other invalid current row situations. This means you might have a Recordset object variable point to:
- A Recordset set to Nothing
- A closed Recordset—its State property is adStateClosed
- An open Recordset, but have the current row positioned beyond the data rows (either side of BOF or EOF)
- A deleted row—either you or another user deleted the row
It's up to your code to manage the current row pointer (CRP); and ADO makes it fairly easy to do. You can move the CRP using any of these techniques:
- Use the Move methods—MoveNext and MovePrevious to move forward or backward one row; MoveLast and MoveFirst to move the CRP to the last or first row of the Recordset. There is also a Move method to move forward or backward n rows.
Tip Once you are positioned at BOF, using MovePrevious causes a trappable error, just like using MoveNext when EOF is true. If you permit your users to navigate through a Recordset, be sure to code the error traps to prevent errors associated with navigating off the ends of your Recordset.
- Use the BookMark property to save a CRP and reset it later.
- Use the AbsolutePosition property to move to the nth row of the populated Recordset—but only on client-side cursors.
- Add a new row to the Recordset using the AddNew method. This moves the CRP to the last row in the Recordset. The row is added to the target table in the appropriate sequence, as determined by the data source indexes.
- Use the CancelUpdate method after using AddNew. This moves the CRP to the CRP setting before the AddNew operation. For example, if the CRP is at row 10 of 15 and you add a new row, the CRP is moved to row 16 (to the new row). CancelUpdate returns the CRP to row 10.
Managing Recordset Inserts
Adding rows to an existing Recordset implies that when the Recordset is persisted, these new rows should become part of the existing data store. This might mean that ADO will write the row to a specific table, or that it will simply add the row to a memory-resident Recordset that's persisted to a file, XML, binary stream, or other data store. No, ADO won't update or add data to more than one table at a time, so if the query is the result of a join, you have to be careful about which columns you change, how you construct new rows, and how ADO deals with these changes on the data provider.
To get ADO to add a new row to your cursor, simply use the Recordset's AddNew method. This method creates an empty Recordset (with no Value properties set). After it is created, you can address the individual Field object values, one by one. Another (faster) approach is to pass the new Field values in a Variant array to apply to the Field values. ADO is different than DAO and RDO when it comes to the AddNew and Update methods. In these older technologies, you had to use the Update method to persist the new row or other changes to the Recordset—if you moved off the row, you lost the changes. ADO takes a radically different approach—you have to purposefully cancel the changes if you don't want ADO to save new or changed rows to the Recordset and the database. If you move the CPR to another row, or close the Recordset, the effect is the same as using the Update method. This means that if your user changes his or her mind, you have to use the Cancel or CancelBatch method to flush the current row's changes or all changes made to the batch.
The following example shows the use of this Variant array technique. Sure, you can reference the individual Recordset Field objects one-by-one to fill in the Value properties, but the Variant array technique seems awfully easy—and the engineers tell me it's faster.
Option Explicit Dim cn As Connection Dim cmd As Command Dim rs As Recordset Dim varFields As Variant Dim varValues As Variant Private Sub Form_Load() On Error GoTo FLEH Set cn = New Connection cn.Open "dsn=localserver", "admin", "pw"
The following code opens the Recordset with the intent to simply add rows, so only the DDL information is fetched, not any rows.
Set rs = New Recordset rs.Open "Select Title,type,price,pubid, ISBN from titles where 1=0", _ cn, adOpenKeyset, adLockOptimistic, adCmdText
In the code shown next, we fill in the two Variant arrays used to pass in the Field names and Values. Don't worry about putting quotes around the numbers—Visual Basic and ADO coerce these to the right datatypes when moving the values into the arguments sent to the data provider. ADO uses these arguments to construct an update cursor statement. ADO does not use a constructed INSERT statement for this operation—it uses the sp_cursor ADO system stored procedure.
varFields = Array("Title", "type", "price", "pubid", "ISBN") varValues = Array("New title", "MI", 90.99, 90, "bogus") Okay, now we're ready to add the first new row. We could get an error here (before the Update) if there is a collision with the primary key or if the Field values don't match the Field property constraints.
rs.AddNew varFields, varValues rs.Update
Next, we add two more rows using the same technique, but we'll try to fool ADO with incorrectly formatted values. ADO rejects this next AddNew because the price is not a number.
varValues = Array("Another New title", "MI", "bad", 90, "bogus2") rs.AddNew varFields, varValues rs.Update The next row will be rejected as well; the Type column is constrained in the database with a foreign key. It has to match one of the known ValidTitleTypes table entries—it doesn't.
varValues = Array("A third New title", "XX", "−86", 90, "bogus3") rs.AddNew varFields, varValues rs.Update rs.Close Next, we clean up the mess we made in the Titles table by deleting any of the bogus ISBN rows. We can make sure this does not affect more than the single row that actually worked by wrapping the whole operation in a transaction. We could have wrapped the whole set of AddNew operations in a transaction, but that would have confused what you saw in the Profiler—assuming you were watching.
cn.BeginTrans cn.Execute "Delete titles where ISBN like 'bogus%'", intRecordsAffected If intRecordsAffected > 3 Then cn.RollbackTrans MsgBox "Nope, something went wrong with the delete!" Else cn.CommitTrans End If rs.Close Quit: Exit Sub FLEH: Debug.Print Err.Number, Err.Description Select Case Err Case -2147217873 ' Violation of PRIMARY KEY constraint 'aaaaaTitles_PK'. 'Cannot insert duplicate key in object 'Titles'. ' or ... [Microsoft][ODBC SQL Server Driver][SQL Server]INSERT 'statement conflicted with COLUMN FOREIGN KEY constraint 'FK_Titles_ValidTitleTypes'. The conflict occurred in database 'biblio', table 'ValidTitleTypes', column 'Type'. MsgBox "Can't insert a duplicate row into titles table " _ & or invalid title type. " & Mid(Err.Description, InStrRev(Err.Description, "]") + 1) rs.CancelUpdate Resume Next Case -2147352571 'Type mismatch. MsgBox "Field type(s) not correct." rs.CancelUpdate Resume Next Case 3219, 3704: Resume Next Case Else Stop Resume End Select End Sub
| Note | Note that the Error.Number returned is the same for a Primary key violation (when a duplicate record already exists) as for when the primary key/foreign key is incorrect.You have to parse the string to figure out which occurred. Remember that these error messages are translated to French, German, Spanish, and a bunch of other languages, so this might be an issue for those trying to create an application that might have to run on a number of localized ADO platforms. |
Working with System-Generated Primary Keys
Adding rows to a Recordset is almost as easy as Updating existing rows. In this case, the collisions can occur because other clients have added rows with the same primary key (PK). Using a system-generated primary key easily prevents this. SQL Server, Jet, and Oracle all have ways to do this using columns defined as Identity, AutoNumber, and so forth. For more information on SQL Server Identity columns, look up "Auto-Numbering and Identifier Columns" in MSDN.
There are a couple of best practice approaches for adding records that you need to consider when handling system-generated primary keys. First, you need to decide whether the generated keys must be unique on a single server or across an indeterminate number of servers. If it's the latter, you'll need to define the table column to use a GUID instead of an integer. This guarantees that the key is unique regardless of where or when it is created. What's cool about the GUID technology is that GUIDs can be traced back to their origin—conventional identity integers can't.
After you've chosen a unique row identifier strategy, you need to figure out how to retrieve this new system-generated PK after new rows are added. If you use Recordset cursors, consider using the Resync method to get the latest identity value for the new row. ADO can do this automatically if you set the right arguments on the Update Resync property. Remember that new rows are always added to the end of your Recordset, regardless of their correct order in the Recordset membership. Using the Resync method does not affect the order of the rows in the database or the Recordset.
If you use a SQL Server stored procedure to do the INSERT, the procedure can return the new identity value by using the @@IDENTITY global variable—assuming that no other INSERT statement reset the value. Remember that the TSQL RETURN function can only return an Integer—not a GUID—so you have to use an OUTPUT parameter to return the latest GUID value.
However, this approach is also dangerous. If the stored procedure maintains referential integrity by adding rows to other tables, the @@IDENTITY value is reset by any operation that adds a row to a table with or without an Identity column. For instance, suppose your stored procedure adds a row to Table1 using a TSQL INSERT statement. In turn, the INSERT Trigger on Table1 might add a row to Table2, which also has a system-generated identity value. This second identity value is applied to the current @@IDENTITY value. If the second table had no identity column, SQL Server would have set @@IDENTITY to zero.
| Tip | One of the problems here is that ADO can't find the new row in the database because it does not have its primary key and won't have it until the cursor is rebuilt. That is, when you add a row and the system generates the PK, it's recorded in the database table, but not in the cursor. That's because ADO simply uses an INSERT statement to add the row, and there is no built-in mechanism to return the new row's PK. |
The problem here is that you want to be able to reference newly added rows (new customers or new order items, for example) at the client without having to completely requery the database, which is always an option. That is, the newly added rows are fully populated on the server, including the new PK values. In the 1960s, when my hair was longer and my pockets emptier, I tried to sell Electrolux vacuum cleaners door-to-door. While I only sold one (to my mother), I did meet some nice people who simply couldn't afford to buy the machine and I didn't have the heart to con them into it. But I digress. In the process of selling these machines, I used a preprinted multipart sales book. Each page of the book had a number, and no other salesperson (there were no PC "sales people" back then) had the same set of numbers. When sales were completed, the customer got one copy of the form and I submitted the carbon to the home office to be filled. All further references were made with that unique customer number. I'm sure you've used similar forms in your office for expense accounts or other office automation operations.
Okay, so how does this apply to your problem? Well, what if you followed the same practice with your applications? What if your application for managing customers, orders, and order items created empty customer records ahead of time, along with empty order and order item records? When it came time to add a new customer, all you would do is update an existing customer record that was preassigned to your application, and do the same for the orders and items. The downside here is that the database would be stretched somewhat by empty records. But this need not be a problem if you use VarChar datatypes that start out small and stretch to accommodate new data rows. You would also not have to worry about collisions. The empty customer records already "belong" to your application, and no other application has the right to alter them—at least not until they are populated the first time. When you run out of empty records, you simply execute a procedure that returns another set. You can also use a status field to show whether or not the customer is empty.
Managing Recordset Updates
There always seems to be any number of ways to accomplish your goals. Some are faster, some easier, some illegal. Using ADO to update your database is no exception—while some approaches are kinda silly, most aren't illegal (except in parts of Cleveland). You have (at least) these options:
- Create an updatable cursor using one of the valid LockType settings.
- Create a stored procedure to perform updates to one or more tables.
- Code a Command object that executes an Update statement.
- Hard-code an SQL statement that executes an Update statement.
While you can't always use all of these options, you usually have several to choose from. However, you'll find it a little tricky using two or more techniques at the same time. For example, suppose you use an updatable cursor to fetch a row, and try to use a stored procedure or UPDATE statement to perform the update. You'll discover that the Recordset can block your own UPDATE.
To illustrate the sum of the Update techniques, I wrote a rather long example program (see …Recordset Objects\Updating\Handling Errors on the CD). Let's step through some of the code. While some of the concepts have not been discussed yet, the code raises some interesting points that are clarified later in this chapter.
Walking Through the Update Example Application
The example application illustrates basic update error handling and permits you to simulate multiple users and how their interaction either blocks or permits updates. Once the application is started, the connection is opened in Form_Load and the valid ISBN list is populated. At this point, you need to select an ISBN from the ISBN Wanted drop-down list and click Fetch. The row matching the selected ISBN will be displayed in the Fetch frame. After a few seconds, a background event fires and uses the Resync method to fetch the database state of the row to show what changes other users have made. If another instance of the application (or another application, for that matter) changes the row, the lower "Underling" frame reports this activity. The application determines whether a row has changed by examining the TimeStamp field. Comparing TimeStamp datatyped columns proved to be a little harder than I anticipated.
You can select the CursorLocation (server or client) and the LockType (Optimistic or Pessimistic) by using the option buttons. If you choose Pessimistic locking, the application enables a slider bar to let you specify how long to wait before the application releases the row being edited. This is done with a "watchdog" timer routine that is enabled when the fetch is executed. A progress bar reminds you how much time is left to complete the change.
When you have made a change to the row in the Fetch data frame, you need to click either the Update or Cursor Update buttons—or Abandon Change. These buttons work as follows:
- Update—runs an ADO Command to perform an update on the fetched row. This routine cancels the pending update, closes the updatable Recordset, and executes the UPDATE statement through the Command object. If you don't close the updatable Recordset, the Update command (in Pessimistic mode) can't update the row. (Technically, they are two separate transactions because they happen on separate connections, so the Command UPDATE will have to wait until the cursor releases its locks.)
- Cursor Update—simply uses the Recordset Update method.
- Abandon Change—undoes the change to the row and releases the fetched row in Pessimistic mode.
Let's look at the code. First, notice that I manage the TimeStamp columns in Variants. I tried both Byte arrays and Variants, and settled on Variants, but both worked equally well.
Dim varTimeStamp As Variant Dim varTimeStampDB As Variant
The Form_Load event sets up two Command objects, as well as a couple of Recordset objects to be used later. The first, shown next, is a simple parameter query that returns a single row from the Titles table based on a specific ISBN.[7]
… With cmdSelect .Name = "TitleByISBN" .CommandText = "Select Title, Year_Published, ISBN, TimeStamp" _ & " from Newtitles where ISBN = ?" .CommandType = adCmdText .Parameters.Append .CreateParameter("ISBNWanted", adVarChar, adParamInput, 20) End With The second Command object is used to submit a stand-alone UPDATE statement using the current row's Field object values as parameters.
With cmdUpdate .Name = "UpdateTitle" .CommandText = "Update NewTitles set Title = ?, Year_Published = ? " _ & " Where ISBN =? and TimeStamp = ?" .CommandType = adCmdText .CommandTimeout = 5 ' Don't wait long... .Parameters.Append .CreateParameter("NewTitle", adVarChar, adParamInput, 255) .Parameters.Append .CreateParameter("Year", adSmallInt, adParamInput) .Parameters.Append .CreateParameter("ISBNWanted", adVarChar, adParamInput, 20) .Parameters.Append .CreateParameter("TimeStamp", adVarBinary, adParamInput, 8) End With The routine to open the database connection is handled outside of the Form load event to give the application an opportunity to switch between client-side and server-side cursors. This routine, shown next, also populates the drop-down ISBN list. The complete code for this routine is included in the sample on the CD.
Private Sub cmdOpenDB_Click() cn.CursorLocation = intOptCursorLoc cn.Open "provider=sqloledb;data source=(local);default catalog=biblio", "Admin", "pw" cmdSelect.ActiveConnection = cn cmdUpdate.ActiveConnection = cn
When the user clicks the Fetch Command button, the following routine executes the TitlesByISBN Command object, based on the ISBN row selected from the drop-down list of valid ISBNs. The State property determines whether there are pending edits that would be lost if the query were reexecuted.
Private Sub cmdFetch_Click() tmrResync.Enabled = False If rs.State = adStateOpen Then If rs.EditMode = adEditInProgress Then i = MsgBox("Abandon your changes? Click OK to fetch a new record.", _ vbOKCancel, "Edit in progress...") If i = vbCancel Then GoTo Quit End If rs.CancelUpdate rs.Close End If If the LockType is set to pessimistic, we can't just fetch and sit on the row, so the application starts a watchdog timer to release it in n seconds.
If intOptLockType = adLockPessimistic Then tmrWatchDog.Enabled = True lblUpdateWarning.Caption = "Warning... less than " & sldDelay.Value _ & " seconds to complete the change" Else tmrWatchDog.Enabled = False lblUpdateWarning.Caption = "" End If
In any case, we run the fetch Command and dump the row to the TextBox controls.[8]
Set rs = New Recordset rs.LockType = intOptLockType rs.CursorType = adOpenStatic cn.TitleByISBN cmbISBN.Text, rs ShowRsRow (Rows)
If the user changes one of the field values shown in the TextBox controls, the Validate event fires (when focus is lost). This routine, shown next, illustrates simple client-side field-level validation. Most of the emphasis here is on the Year_Published column. The client simply checks to see if the value is a number between 1900 and the current year. Note that the database rule for this database table column specifies no values outside of 1930 and 2000.
| Note | The hard-coding of these date ranges in the client application will probably make many of you more experienced developers a little antsy. I don't blame you. This is just the kind of problem that made us go back and deal with Y2K issues. I'm of the opinion that business rules such as these belong in central routines on the server where they can easily be adapted to changing business trends and requirements. However, that means that the client applications can't correctly validate the data before it gets crammed into data fields. The solution seems to be a data-driven business rules generator and filter that gets created on the client each time it starts. I write about this extensively in the Hitchhiker's Guide to Visual Basic and SQL Server, and I'll be writing about this again in a white paper or two later on—after I retire from Microsoft. |
To test how ADO handles rule violations, try to set the year value to 1928, which is valid as far as the client code is concerned, but invalid as far as the server-side rule is concerned. If you set up this test and click the Update button, the server returns an error to ADO. Note that a different error message number is returned when you use Update method, as opposed to the UPDATE SQL command. Both return the same error description that reports the rule and column that caused the error.
Private Sub ltbText_Validate(Index As Integer, Cancel As Boolean) Select Case Index Case enuTB.ISBN ' don't permit changes to primary key... Exit Sub Case enuTB.Title rs(enuTB.Title) = ltbText(Index).Text Case enuTB.Year ' Note these criteria do NOT match the DB Rule criteria With ltbText(enuTB.Year) If IsNumeric(.Text) Then If .Text >= 1900 And .Text <= Format(Now, "YYYY") Then Else: MsgBox "Invalid year" …
The update routine is invoked when any Update button is clicked. It updates the row either by using a direct UPDATE command or by simply using the Update method on the Recordset.
The first routine (Case 0) executes the ADO Command object set up to execute the UPDATE SQL statement. In this case, we have to release the row held by the Recordset cursor or our UPDATE statement won't succeed—at least when we use Pessimistic cursors. This really isn't necessary for optimistic cursors. Because this technique does not support automatic Resync, we simply reexecute the single-row query.
Private Sub cmdUpdateButton_Click(Index As Integer) On Error GoTo cmdUEH Select Case Index Case 0 ' Update Command rs.CancelUpdate ' The rs is holding the row rs.Close cmdUpdate.Execute intRecsAffected, Array(ltbText(enuTB.Title).Text, _ ltbText(enuTB.Year).Text, _ ltbText(enuTB.ISBN).Text, _ varTimeStamp), adExecuteNoRecords ' Update sucessful... cmdFetch = True ' Refetch the row (including the TimeStamp)
The second routine (Case 1) simply updates the Recordset using the Update method. If the Recordset object supports the Resync method, we activate it before the update. This saves us from doing extra work later to fetch the rows.
Case 1 ' Recordset Update method If rs.Supports(adResync) Then rs.Properties("Update Resync") = adResyncUpdates rs.Update ShowRsRow (Rows) … The update error handler is programmed to deal with a number of basic contingencies. An "Optimistic concurrency" failure is returned if some other user changed the row using either another application or another instance of this application.
The timeout error occurs if some other user has the row held. We set the CommandTimeout to five seconds, so this error occurs rather quickly—perhaps too quickly for the backend to resolve multiple update and select hits on the same row.
The two cases of error numbers (Err.Number) are returned if the server-side rules are violated. You will want to fully populate this error handler with other similar conditions on your own database. For example, when adding rows, you'll want to trap primary key violations or primary key/foreign key violations.
cmdUEH: Debug.Print Err, Err.Description Select Case Err Case -2147217864 ' Optimistic concurrency check failed. The row was 'modified outside of this cursor. MsgBox "Some other user has changed the record" _ & since you fetched it. The values stored" _ & in the database can be seen below." rs.CancelUpdate Resume Quit Case -2147217871 ' Timeout expired MsgBox "Some other user has the record locked. "Update did not take place.", vbCritical, "Update timeout." Resume Quit Case -2147217900, -2147467259 ' A column insert or update conflicts with a rule imposed 'by a previous CREATE RULE statement. MsgBox Err.Description, vbCritical, "Update failed—rule violation" Resume Quit Case 3704 ' Invalid rs state... just quit Resume Quit
Note the debug code that follows. I use this technique to stop execution of the application while it's being worked on, so I can easily insert new error case numbers and their resolutions. No, don't leave this handler in your production application.
Case Else Stop Resume End Select …
If the user chooses to abandon the update and release the row, the following routine tells ADO of the user's decision by executing the CancelUpdate method.
Private Sub cmdCancelUpdate_Click() If rs.State = adStateOpen Then rs.CancelUpdate rs.Close ShowRsRow (Clear) End If cmdCancelUpdate.Enabled = False End Sub
The next routine runs every few seconds to refetch the server-side values.[9] If we are using client-side cursors, we can simply use the Resync method to accomplish this. The server-side values are returned in the Field object's UnderlyingValue property. If we choose server-side cursors (CursorLocation = adUseServer), then we must run a new query with another, separate Recordset object.
Private Sub tmrResync_Timer() If cn.CursorLocation = adUseClient Then ' client-side cursors use Resync cmdFetch.Enabled = False ' Just bring in new underlying values rs.Resync adAffectCurrent, adResyncUnderlyingValues With rs If .EOF Then lblError.Caption = "The ISBN you are working with has been deleted by another user. (or ISBN has changed)" Beep Else ltbTextDB(enuTB.Title).Text = .Fields(enuTB.Title).UnderlyingValue ltbTextDB(enuTB.Year).Text = .Fields(enuTB.Year).UnderlyingValue ltbTextDB(enuTB.ISBN).Text = .Fields(enuTB.ISBN).UnderlyingValue varTimeStampDB = .Fields(enuTB.TimeStamp).UnderlyingValue
The routine also compares the server-side TimeStamp column with the TimeStamp fetched initially, as shown next. If they aren't the same, the database row is different (somehow) from the client-side version.
If IsEmpty(varTimeStamp) Then ElseIf (CStr(varTimeStamp) = CStr(varTimeStampDB)) = False Then lblError.Caption = "The current ISBN has been changed…" Beep End If End If End With
If we're using server-side cursors, we have to run a separate stand-alone Recordset. This Recordset is FO/RO for minimum impact and best performance.
Else ' Server-side cursors require manual requery. If intOptLockType = adLockPessimistic Then Else cmdFetch.Enabled = False If rsDB.State = adStateOpen Then rsDB.Close cn.TitleByISBN rs(enuTB.ISBN), rsDB With rsDB If .EOF Then lblError.Caption = "The ISBN has been deleted by …." Beep Else ltbTextDB(enuTB.Title).Text = .Fields(enuTB.Title) ltbTextDB(enuTB.Year).Text = .Fields(enuTB.Year) ltbTextDB(enuTB.ISBN).Text = .Fields(enuTB.ISBN) varTimeStampDB = .Fields(enuTB.TimeStamp) If IsEmpty(varTimeStamp) Then ElseIf (CStr(varTimeStamp) = CStr(varTimeStampDB)) = False Then lblError.Caption = "The ISBN has been changed…." Beep …
When using pessimistic locking, the user won't be permitted to camp on the row. After the row is fetched, the user has only n seconds to either change it or abandon the fetch. If the user waits longer than that, the watchdog timer releases it for the user, as seen in the following code:
Private Sub tmrWatchDog_Timer() Static intCount As Integer Exit Sub intCount = intCount + 1 If intCount > intWatchDogDelay Then lblUpdateWarning.Caption = "You have waited too long to change this record." "Changes discarded." Beep cmdCancelUpdate = True lblError.Caption = "Record released." …
| Note | This application fetches a single row and locks it using either optimistic or pessimistic locking. When using optimistic locking, other users are permitted to fetch and update the row; however, with pessimistic locking other users are locked out of the row (and all of the rows in the cache) as long as the cursor is open. That's why we cannot permit the user to dawdle while deciding what to change. |
Understanding Update-Related Properties
If you want ADO to manage updates for you, you have to choose the right property settings. Many of the combinations of CursorLocation, CursorType, and LockType don't yield updatable Recordsets. Even those that do might degrade to RO if the query is too complex—if ADO can't figure out how to address specific rows in the result set or the source table to be changed. Check out the chart in the Appendix, which lists all of the combinations for SQL Server (both ODBC and OLE DB providers) and Jet databases. The program that generates this chart is on the CD, so you can run the application against your own data provider.
ADO might do more than you expect, as far as update management is concerned. DAO, RDO, and ADO differ significantly in (at least) one respect. When you change a Field object's Value property in an ADO Recordset, the changes are persisted in a temporary buffer and are not written to the database. That much you know. However, in ADO, when you move the current row pointer to a new row (without using the Update or UpdateBatch method), ADO saves that changed row to the database in immediate mode[10] or commits the row to the persisted Recordset in batch mode. This means when you use MoveNext, MoveFirst, any of the other Move methods, or any other method that changes the current row position in the cursor, you trigger an update operation. The update also fires if you close the Recordset.
If you want to undo a change made to the Recordset, you can execute the Cancel or CancelUpdate methods, which backs out the changes—assuming they have not already been posted to the database.
When you execute the Update method, ADO moves the current Recordset changes from a temporary buffer to the Recordset itself. If the LockType property is set to Optimistic or Pessimistic locking, ADO submits a command to the data provider to persist the change to the database. If the LockType is set to adLockBatchOptimistic, ADO does nothing further and the database remains unaffected. If no changes are pending when you use the Update method, a trappable error occurs. Executing the UpdateBatch method in either immediate or batch mode flushes all changes in the Recordset to the database.
Updating Newly Inserted Rows
If you insert a new row, ADO won't be able to find it to make changes or to delete it unless you set the Change Inserted Rows property to True. If this is not set, ADO reports that it has lost the cursor position for the current row.
LockType
ADO spends considerable time trying to figure out how to update your Recordset. It might even submit extra DDL queries to capture additional information about the tables involved in the query. You can alter the way ADO performs updates by setting the LockType property. By default this value is set to adLockReadOnly, which prevents ADO from worrying about updating the Recordset. That's not to say you can't construct UPDATE, DELETE, or INSERT statements on your own to execute changes, or to call a stored procedure to do so—these techniques usually are more efficient than having ADO construct the needed overhead mechanisms to manage updates.
Not all ADO providers support all lock types. If you ask for a LockType your provider doesn't support, it switches to a type that it does support. To check whether your provider can provide specific update services, check the Supports method for True settings on adUpdate and adUpdateBatch. You'll find that pessimistic locking (adLockPessimistic) is not supported on the client-side ADO data provider.
There are three locking strategies supported by ADO—four, if you count adLockReadOnly:
-
adLockBatchOptimistic: This lock type is only available on the client-side provider. It enables deferred or batch updates.
-
adLockOptimistic: This lock type instructs ADO to create an updatable cursor (if possible) and to handle the updates without persistent locks.
-
adLockPessimistic: This option instructs ADO to create an updatable cursor and to lock the current CacheSize rows while changes are made.
The three locking strategies are all very different in their approach to data update management.
Batch Optimistic Updates When using client-side cursors (CursorLocation set to adUseClient), you must set your LockType to adLockBatchOptimistic if you want ADO to manage updates for your cursor. In this case, ADO constructs a Recordset and persists any changes that you make in the Recordset itself, but it does not send these changes to the database. When you're ready to post the changes to the database, ADO "batches" all persisted changes together and transmits them to the data provider as a block.
This approach to ADO Recordset update management was first introduced in RDO. It has been enhanced in ADO and now provides the core functionality for "disjoint" or "disconnected" Recordsets. Consider this scenario: Suppose you open a Recordset in Batch Optimistic mode using the client-side cursor provider. You can then dissociate the Recordset from the connection by setting the ActiveConnection to Nothing. At this point, the Recordset object is fully populated and is a stand-alone object. You can persist it to a file or to an XML stream and pass it to another layer. You can also update the Recordset wherever it's sent, using conventional AddNew/Update, Delete, or change/Update strategies (I discuss these a little later in this chapter). Once you reassociate the ActiveConnection to a valid connection, the UpdateBatch method can be used to post any changes made to the database. Cool? I think so.
The real point here is that depending on whether or not you use the disconnected technique, the Update method changes its behavior. That is, when you make a change to the Recordset in Batch Optimistic locking mode and use the Update method, the changes are recorded in the Recordset, but not sent to the database. These changes are simply made a part of the Recordset structure. When you finally use the UpdateBatch method against your Recordset, ADO gathers up all of these change requests and applies them to the source tables.
| Tip | In ADO 2.5, the Field object also exposes a new Status property. No, this is not used to indicate Field-by-Field object update status. It's used when working with the new Record object. However, in ADO 2.6 this functionality is expanded to include Field-by-Field object status. |
Updating with Optimistic Locking The adLockOptimistic LockType setting is ADO's most common update strategy. You can use this LockType when working with either server-side or client-side cursors. Typically, when the Update method is executed, ADO and the data provider construct a suitable UPDATE, INSERT, or DELETE SQL statement and submit it to the data engine for processing.
| Note | To see exactly how these cursors behave, I wrote a sample application that permits setting each of the CursorLocation, LockType, and CursorType properties, as well as a variety of query and update options. Look for ..\Sample Applications\Recordset\Updating on the CD. |
The type of update operation used by ADO depends on the type of operation used to fetch the rows. Remember the command strategy techniques we discussed in Chapter 5? For example, when we use the Recordset Open method to execute a Command object, ADO uses the (proprietary) system procedure sp_cursor to fetch the rows. In this case, ADO also uses the sp_cursor stored procedure to make changes. When I updated the Year_Published Field of the Recordset (I touched no others), ADO submitted the following sp_cursor call to SQL Server:
sp_cursor 531914844, 33, 1, N'titles', 1994
When I touched two columns, both were updated through the same mechanism. Notice that the cursor is referenced by number, but we don't really know what the other parameters are.[11]
sp_cursor 531914844, 33, 1, N'titles', 'High Integrity Compilation : A Case Study (New)', 1999
Let's walk through the query types (shown in Table 6-5) again, and see what mechanisms are used to update the rows—starting with Server-side CursorLocation. I set the LockType to adLockOptimistic and the CursorType to adOpenKeyset.
| COMMAND SYNTAX | GENERATED UPDATE QUERY |
|---|---|
| Cn.GetTitles | sp_cursor 532619356, 33, 1, N'titles', 1901 |
| rs.Open cmd | sp_cursor 544628828, 33, 1, N'titles', 1901 |
| rs.Open strSQL | sp_cursor 532643932, 33, 1, N'titles', 1901 |
| cmd.Execute | sp_cursor 544751708, 33, 1, N'titles', 1901 |
| Cn.GetTitlesByYear | (Not updatable) |
Notice that each of the generated queries is basically the same as the other generated queries, regardless of the technique used to fetch the data in the first place. Now let's switch to client-side cursors and the adLockBatchOptimistic LockType, as shown in Table 6-6. In this case, we have to use Static CursorType—that's the only type supported with client-side cursors.
| COMMAND SYNTAX | GENERATED UPDATE QUERY |
|---|---|
| Cn.GetTitles | sp_cursor 532619356, 33, 1, N'titles', 1901 |
| rs.Open cmd | sp_cursor 544628828, 33, 1, N'titles', 1901 |
| rs.Open strSQL | sp_cursor 532643932, 33, 1, N'titles', 1901 |
| cmd.Execute | sp_cursor 544751708, 33, 1, N'titles', 1901 |
| Cn.GetTitlesByYear | sp_cursor 544751708, 33, 1, N'titles', 1901 |
The stored procedure query is updatable when accessed through the client-side optimistic batch cursor provider; it is not updatable when accessed through the server-side provider.
I also tried making changes using the optimistic batch Recordset, and I used MoveNext to trigger the Update method (behind the scenes). When I finally executed UpdateBatch, ADO transmitted the following script to the server. This was sent as a single TSQL query.
sp_executesql N'UPDATE "biblio".."titles" SET "Year_Published"=@P1 WHERE "ISBN"=@P2 AND "Year_Published"=@P3; UPDATE "biblio".."titles" SET "Year_Published"=@P4 WHERE "ISBN"=@P5 AND "Year_Published"=@P6; UPDATE "biblio".."titles" SET "Year_Published"=@P7 WHERE "ISBN"=@P8 AND "Year_Published"=@P9', N'@P1 smallint,@P2 varchar(13),@P3 smallint,@P4 smallint,@P5 varchar(13),@P6 smallint,@P7 smallint,@P8 varchar(13),@P9 smallint', 1901, '0-1338103-9-9', 1900, 1991, '0-2015983-0-2', 1995, 1992, '0-4448922-4-9', 1991
The syntax ADO used to construct this query is determined by the Update Criteria property, which I discuss shortly in the "Building an Update WHERE Clause" section.
Updating with Pessimistic Locking Someone came into the room when I started this section and thought I was writing a book on positive mental attitudes. I assured her I wasn't. Pessimistic locking is not the opposite of optimistic locking—it's just different. The choice of one or the other must be made with careful consideration of the impact on other users and system scalability. Far too many developers choose pessimistic locking without really knowing how it works and the impact it has on the system. Because of this problem, I tell my students that they need a note from their mom before using it. One of these days I'll get one in the mail.
While optimistic concurrency control works on the assumption that resource conflicts between multiple users are unlikely (but not impossible), and that transactions execute without locking any resources, pessimistic locking attempts to lock resources as required and holds the locks for the duration of a transaction.
Only some server-side CursorLocation data providers support pessimistic locking—SQL Server providers do. When your application sets the Recordset LockType property to adLockPessimistic, the data provider attempts to place a lock on the underlying database rows at the time they are read into the cursor result set. If the cursor is opened in a transaction, the update locks are held until the transaction is either committed or rolled back. If the cursor is opened outside of a transaction, however, the lock is dropped when the next row is fetched. Therefore, a cursor should be opened inside a transaction any time full pessimistic concurrency control is wanted.
An update lock prevents any other task from acquiring an update or exclusive lock on the locked row (page or table), which prevents any other task from updating the row. An update lock, however, does not block a shared lock, so it does not prevent other tasks from reading the row unless the second task is also requesting a read with an update lock.
| Tip | SQL Server supports locking "hints" that instruct the server to manage the locks in specific ways. These hints can be used with a modicum of discretion with ADO. |
Cursor and Lock Types Available for Different Providers The Hitchhiker's Guide to Visual Basic and SQL Server includes a comprehensive table that lists several combinations of data sources and lock types to show which options resulted in updatable RDO Resultset objects. To create an equivalent and far more comprehensive table for this book, I wrote a new sample application, and the results are shown in the "Appendix—Supports Analysis."
This appendix clearly shows how the Supports method returns vital information about what features the provider is capable of implementing. For example, you can see that the client-side provider can only implement Static CursorType cursors. Note that when the application requests an unsupported CursorType or LockType, the provider switches to an implementation it can support.
Building an Update WHERE Clause
To uniquely identify a specific row to be fetched or updated, ADO depends on its capability to construct a WHERE clause that identifies one, and only one, row. If ADO cannot figure out how to locate specific rows, it degrades the cursor LockType to read-only and you'll have to use your own devices to change the database. You have significant control over how the WHERE clause is constructed, and it makes sense to alter the default behavior to match the schema of your base tables.
In addition, ADO needs to determine whether the row has changed since it was last fetched. If you set the CursorLocation to adUseClient and the LockType property to adLockBatchOptimistic, ADO adds additional criteria to the WHERE clause to compare initial client-side columns or time stamp values (before changes) with their server-side values. If columns don't match up when the update is attempted, the update process fails.
The Update Criteria Recordset property is used to instruct ADO how to figure out if the data has changed since it was fetched. You can choose to have ADO identify the specific row using combinations of the primary key, a time stamp, or the PK and selected columns. If the column values or time stamp value changed since the data was first fetched, the update operation fails because the data provider cannot locate a matching row.
For example, if the Update Criteria property is set to adCriteriaTimeStamp, ADO generates an UPDATE statement that simply tests to see whether the specified database row, based on the primary key (ID), contains the same time stamp value that it did when first fetched (passed in argument @P3). If this row is located and the time stamp values are unchanged, the row is updated—otherwise an error is returned to ADO. A typical UPDATE statement could look like this:
UPDATE "biblio".."students" SET "FirstName"=@P1 WHERE "ID"=@P2 AND "timestamp"=@P3
If the row has been deleted, the primary key won't be located and the UPDATE fails. If the row is found, but the tested columns don't match up, ADO can fetch the current database data values for you. This way you can decide if the current values in the table can be used instead of the values your user wants to use. If you set the Update Resync property in the Recordset object's dynamic properties to adResyncConflicts, ADO fetches the data-side values for you. When the update fails because someone else changed the data, the latest and greatest data appears in the UnderlyingValue property for each of the Field objects in your Recordset. The OriginalValue property is populated with the data values prior to the client changes.
Two warnings: First, I haven't tried this in a scenario where deletions are involved, for example where an update fails because the row was deleted, a deletion fails because the row was modified, or a deletion fails because the row was already deleted.
And second, ADO uses the newly retrieved data in the WHERE clauses the next time you try to update this row. Why is this a warning? Many people may have bad error-handling code and may attempt to update the same row again without realizing they're about to overwrite the changes made by another user—changes that caused the conflict in the first place.
Remember that if you want ADO to manage updates using the Update Criteria property, ADO requires that you set the CursorLocation property to use client cursors—adUseClient. This also means you'll be using Static cursors. The Update Criteria property can be set before the Open in ADO 2.0 or anytime in ADO 2.1. Valid settings for the Update Criteria property are:
-
adCriteriaUpdCols : (The default) This setting constructs an UPDATE WHERE clause that includes the primary key as well as (just) those Field objects whose values have been changed or reset by your code. This approach assumes you don't care if other table columns changed while you were editing your Recordset. So, if you don't want ADO to include a Field object's name in the WHERE clause, don't touch it—don't even reset it to the same value, because that constitutes a change.
UPDATE "biblio".."students" SET "FirstName"=@P1 WHERE "ID"=@P2 AND "FirstName"=@P3
-
adCriteriaAllCols : In this case, the WHERE clause includes all Fields object names in the Recordset (including any time stamps), whether they have changed or not.
UPDATE "biblio".."students" SET "FirstName"=@P1 WHERE "ID"=@P2 AND "FirstName"=@P3 AND "LastName"=@P4 AND "timestamp"=@P5
-
adCriteriaKey : This option instructs ADO to construct the WHERE clause based solely on the PK. This option ignores changes in the server-side columns and simply updates the row if it still exists in the table. This table has a time stamp field, which is also ignored.
UPDATE "biblio".."students" SET "FirstName"=@P1 WHERE "ID"=@P2
-
adCriteriaTimeStamp : This option has ADO construct the WHERE clause using the PK and the TimeStamp column. If there is no TimeStamp column, ADO reverts to adCriteriaCols—ah, excuse me, there is no such property setting as adCriteriaCols. The OLE DB SDK document is in error. This should be adCriteriaAllCols.
| Note | Specifying adCriteriaTimeStamp may actually use the adCriteriaAllCols option to execute the Update if there is not a valid TimeStamp field in the table. Also, the TimeStamp field does not need to be in the Recordset itself. |
| Tip | There's a lot of good detailed ADO information that is, for some reason, left out of the "standard" MSDN searches. However, it can be found in the OLE DB SDK's OLEDB.CHM. |
Update Method Options
The Update method is used to post changes either to the database in immediate mode or to the in-memory Recordset in batch mode. As we discussed earlier in this chapter, when looking at the AddNew method, you can take either of two approaches to change the Field.Value properties in the Recordset row. You can address and modify selected Field objects one at a time, or you can pass an array of Field objectnames and new Field object values to change the record. Remember to only change those Field objects whose values have changed if you want to keep the update simple. Changing the Field object's Value property back to its initial setting can slow down the update process.
For example, the following code updates the current Recordset row—changing the Title and Year_Published columns. The Field name array does not have to include any columns that have not changed. The initial Field names array can also be constructed early in the application and reused as necessary later in the application.
rs.Update Array("Title", "Year_Published"), Array("New Title's Title", 1990) This Update statement caused ADO to execute the following query—notice how ADO doubled up the single quote embedded in the new title:
sp_executesql N'UPDATE "biblio".."titles" SET "title"=@P1,"year_published"=@P2 WHERE "title"=@P3 AND "year_published"=@P4 AND "ISBN"=@P5', N'@P1 varchar(255),@P2 smallint,@P3 varchar(255),@P4 smallint,@P5 varchar(20)', 'New Title''s Title', 1990, 'High Integrity Compilation : A Case Study (New)', 1901, '0-1338103-9-9'
You can also reference a single Field object in the Update statement. The following code might go in the Validate event if the input data passes muster.
rs.Update "Price", txtBoxPrice.Text
| Tip | It's not a good idea to update the primary key. If you do, ADO won't be able to locate the record to perform subsequent operations. If you think about it, changing the primary key really constitutes a delete and subsequent insert. |
Dealing with Complex Query Updates
Just as when client-side cursors are created against a single table, when client cursors are created from multiple base tables (for instance, in a join operation), data manipulation performed by the ADO Cursor Service can keep the cursor consistent with each base table referenced while generating the result set. ADO can provide explicit update control to ensure that updates applied to the base tables preserve referential integrity restrictions—that is, primary key/foreign key relationships.
When the Unique Table ADO property is set, ADO Row Fix-up mode is implemented. The primary key of the table identified by Unique Table becomes the primary key of the entire cursor, and the columns holding the primary keys of all the base tables are read-only. Updates and inserts are restricted to columns of the unique table. The Unique Table property must be set for Row Fix-up to occur.
When you execute a complex query, the Recordset object is often the result of a JOIN operation executed on multiple base tables. The rows affected depend on the AffectRecords parameter of the Resync method. The standard Resync method is executed if the Unique Table and Resync Command properties are not set.
The command string of the Resync Command property is a parameterized command or stored procedure that uniquely identifies the row being refreshed, and it returns a single row containing the same number and order of columns as the row to be refreshed. The command string contains a parameter for each primary key column in the Unique Table—otherwise, a runtime error is returned. The parameters are automatically filled in with primary key values from the row to be refreshed.
When you execute a query using the adUseServer CursorLocation (the default), ADO simply asks the backend to create and manage the cursor and all aspects of updatability through the use of the aforementioned system stored procedures.
Update Error Handling
So, what happens when you can't update? For example, when another process has a row or page locked, your application is blocked until the row is free of all locks. If there is a lot of activity on the database, the SQL Server can hold off additional Share locks to prevent your UPDATE, INSERT, or DELETE from waiting forever. However, if these locks can't be freed in CommandTimeout seconds, your Update method will trip a trappable error reporting the timeout—not the fact that another user locked the row. If the Update fails because some other user changed the row since you last read it, then errors are handled through more traditional means.
UpdateBatch Method Options
When you are working with Optimistic Batch cursors, database updates are deferred until you use the UpdateBatch method. When you're ready to post changes back to the database, make sure your connection is reestablished (if you disconnected it).
You have several options when posting your changes. The UpdateBatch method accepts an argument that specifies how the update operation is to be handled. Table 6-7 lists the options.
| UPDATEBATCH ARGUMENT | DESCRIPTION |
|---|---|
| adAffectAll | (Default) Affects all records in the Recordset. |
| adAffectAllChapters | Affects records in all chapters of the Recordset. |
| adAffectCurrent | Affects only the current record. |
| adAffectGroup | Affects only records that satisfy the current setting. You must set the Filter property to one of the valid predefined constants to use this option. |
| Tip | The adAffectAll enumeration is "hidden."Visual Basic statement completion won't show it unless you tell the object browser to show hidden elements. |
If the attempt to change the tables in the database fails for any or all records because of a conflict with the underlying data (for example, if a record has already been deleted by another user), the provider returns warnings to the Errors collection and a runtime error occurs. Use the Filter property (adFilterAffectedRecords) and the Status property to locate records with conflicts.
If the Unique Table and Update Resync dynamic properties are set, and the Recordset is the result of executing a JOIN operation on multiple tables, the execution of the UpdateBatch method is implicitly followed by the Resync method. That is, ADO automatically requeries the database row to determine the current values, including the new TimeStamp. You can use these values to help decide how to respond to the failed update.
Remember, you have three copies of the data setting of each Recordset Field object:
- The value first read from the provider. Original data values are maintained in the Field object's OriginalValue property.
- The value set by the client. Current data values are maintained in the Field object's Value property.
- The value set by another client and saved to the database. Current data values are maintained in the Field object's UnderlyingValue property.
Your application needs to arbitrarily choose one of these values, make a choice based on business rules, or, as a last resort, involve the user in which to choose. This final choice is often the most expensive as far as scalability is concerned—especially if you have pending transactions or pessimistic locks in place. If the user is involved, the database might change again before a decision can be made—especially if the user leaves for the Bahamas on her honeymoon before deciding.
Once someone or your code decides which version of the data to accept, there are several ways to make the changes:
- To accept the data in the database, you don't have to submit another update, but you might want to requery the database to see what changed. This can be done automatically as described earlier or by rerunning the query.
- To force your changes through, you simply resubmit the Update statement. ADO constructs the WHERE clause using the newly refreshed values. However, you face the same risk of someone else changing the data either since you last tried to update or after your changes succeed.
- To force your changes through more forcefully, you might consider changing the Update Criteria property to change the update strategy. That is, instead of having ADO construct a WHERE clause that requires certain known values or a specific time stamp, you can simply update the row based on the primary key.
- To undo the changes made by another user, you can construct and submit an UPDATE statement from the OriginalValue property settings.
These options enable you to closely control modifications to a particular base table in a Recordset that was formed by a JOIN operation on multiple base tables.
Recordset Status
If errors occur in the process of executing the Update method, you can check the Recordset object's Status property to see what went wrong. To see just rows that failed to update, set the Filter property to adFilterAffectedRecords. Visit each row in the Recordset; if there are no rows, your batch update succeeded. If your batch update did not succeed, you can determine why the update failed or what operation was performed by using the constants listed in Table 6-8. Incidentally, these constants are bit settings, so when filtering, ADO may set multiple status values at once.
| CONSTANT | VALUE | DESCRIPTION |
|---|---|---|
| adRecOK | 0 | Record updated successfully. |
| adRecNew | 0×1 | Record is new. |
| adRecModified | 0×2 | Record modified. |
| adRecDeleted | 0×4 | Record deleted successfully. |
| adRecUnmodified | 0×8 | Record not modified. |
| adRecInvalid | 0×10 | Record not saved: bookmark is invalid. |
| adRecMultipleChanges | 0×40 | Record not saved: multiple Records affected. |
| adRecPendingChanges | 0×80 | Record not saved: refers to a pending insert. |
| adRecCanceled | 0×100 | Record not saved: operation canceled. |
| adRecCantRelease | 0×400 | New Record not saved: existing Record locked. |
| adRecConcurrencyViolation | 0×800 | Record not saved: optimistic concurrency in use. |
| adRecIntegrityViolation | 0×1000 | Record not saved: integrity constraints violated. |
| adRecMaxChangesExceeded | 0×2000 | Record not saved: too many pending changes. |
| adRecObjectOpen | 0×4000 | Record not saved: conflict with an open storage object. |
| adRecOutOfMemory | 0×8000 | Record not saved: out of memory. |
| adRecPermissionDenied | 0×10000 | Record not saved: insufficient user permissions. |
| adRecSchemaViolation | 0×20000 | Record not saved: violates database structure. |
| adRecDBDeleted | 0×40000 | Existing record already deleted. |
[4]This example is in the ..\samples\Recordset objects\memory based Recordsets directory on the CD.
[5]Actually, it's faster to assign the ActiveConnection property Nothing, not Set it.
[6]I wrote a program to construct all of the cursor types sing the ODBC, native SQL Server, and native Jet 4.0 providers. The program and a table constructed from the results are provided on the CD and in the Appendix.
[7]The International Standard Book Number (ISBN) is a unique number, assigned in blocks to publishers who assign individual numbers to their books, CDs, and other publications.
[8]Actually, instead of a TextBox control, this application uses a custom control I wrote myself that's a combination of Label and Textbox. Yep, the source for this control is also on the CD.
[9]By "server-side" I really mean the values stored in the database, wherever it is—on a server down the hall or in a database file on your system.
[10]When updating with ADO, you either use Optimistic Batch updating, which defers database updates until you execute UpdateBatch, or immediate mode, which saves changes to the database immediately upon execution.
[11]The sp_cursor functions are Microsoft proprietary, and Microsoft is hesitant to publish how they work because they are not intended for direct access by the developer community. I'll try to work out this issue and publish a separate white paper on this in the months to come—another postretirement project.
Team-Fly |
EAN: 2147483647
Pages: 106