Published: Wednesday, August 01, 2001
Using Disconnected Recordsets, Part 2
By Sean Grimaldi
Read Part 1
In Part 1 we looked at how to create a disconnected Recordset. In this
final part, we'll look at the process of creating a disconnected Recordset in detail and how to create
custom, disconnected Recrodsets!
Exploring Disconnected Recordsets
Now we have a concrete example of a Recordset returning function, there are two points that should be immediately
addressed. The first point is the use of constants to set Recordset properties. In order to create a disconnected Recordset
two Recordset properties must be set appropriately. It is a requirement that the CursorLocation
property is set to adUseClient and the LockType property is set to
adLockBatchOptimistic. Note that the CursorType will default to
adUseStatic if we don't explicitly state that it should be set to adUseClient.)
If you cannot memorize these two values or you expect to use many other ADO constants you can include the file:
adovbs.inc. Since this file is approximately 500 lines of ASP, as a general rule, do not use it
unless you have to. You may also want to rename it to an .asp file extension for security reasons.
It is more efficient to set the values you will use as constants. (For more information about using adovbs.inc
be sure to check out this FAQ; for information
on why to give server-side includes a .asp extension, be sure to read
this article.)
As a side note, you could also use the cursor number directly, although this is somewhat sloppy programming if
you leave the comment off, as it is harder to comprehend what the code is doing.
The second, and less obvious point, is that although disconnected implies the Recordset was at one time
connected; but this is not necessarily true. A disconnected Recordset can be used like an array, a collection,
or a dictionary to hold a dataset. This is a valuable option for at least a couple of reasons.
One reason you may want to use a Recordset to hold data rather than an array, collection, or dictionary is that
all the Recordset methods that made ADO so convenient for client-server application, such as sort can be a real
time saver. Performance is also a factor. Chances are that they will also be dramatically more efficient then
coding your own sort algorithm , for example. Plus think what a headache recursion, required in most sort
algorithms, can be. The code snippet below illustrates how to create a custom Recordset, one crafted without
a database connection. (For more information on custom Recordsets, be sure to read:
Using Custom Recordsets.)
Const adVarChar = 200 'the SQL datatype is varchar
'create a client side RS
Set RSStatus = server.CreateObject("ADODB.RECORDSET")
RSStatus.fields.append "ryg_status",adVarChar,25
'Creates a column called status that is a VarChar and 25 long
RSStatus.CursorLocation = adUseClient
RSStatus.CursorType = adOpenStatic
RsStatus.open
RsStatus.addnew "ryg_status","Green"
RsStatus.addnew "ryg_status","Yellow"
RsStatus.addnew "ryg_status","Red"
RSStatus.Update
|
Although it is usually best to write a SQL update statement to update the data, ADO can be used to update the
database with the client side Recordset's data. Here is the classic example. You have written an application
that gets inventory for salespeople. They store this inventory on their laptop as a persisted disconnected Recordset
in an XML file (for more information on saving a Recordset's data to XML, be sure to read:
Creating a Function to Stream a Recordset to XML). Every night they
connect to your application by modem. If a customer wants to return an item,
the salesperson updates the data on their laptop and then when they connect at night the database is updated. At
this point, their local data (in a disconnected recordset) is not the same as the database server's data. When
they do an update at night the two will be resynchronized.
Unfortunately, in the real world, it is not as simple as using objRS.Update. If the database
has been updated by another salesperson, there will be a conflict between the disconnected Recordset and other
user's data. Erring on the side of data consistency, updates must check that they will not conflict with updates
other users have performed. To check if there are database conflicts use
objRS.Filter = adFilterConflictingRecords. In an active database, there will usually be
conflicting records; you can raise these so the user can make a decision as to the best course of action. This
is not a very good solution in many cases however as a typical business user, in this case, a salesperson should
not be making decisions that affect overall data consistency.
The best example I have seen of updating
disconnected Recordsets can be found in Francesco Balena's Programming Microsoft Visual Basic 6.0 (Microsoft Press).
This traveling salesperson example is a simplification of his in depth example. Just about anyone who owns Visual
Basic should own this book.
So now, you have seen that a Recordset that does not have a database connection can be very useful as a tool in
your programming. It can save you time and effort and make your code more scalable.
Conclusion
This article reviewed disconnected Recordsets and hopefully pointed out some of the underused features that are
available. In addition to greater scalability than connected Recordsets, disconnected Recordsets are handy to use.
They have valuable methods that make writing code faster and less error prone, since the developer is reusing
proven methods.
Happy Programming!
By Sean Grimaldi
Links
Programming Microsoft Visual Basic 6.0 (Microsoft Press)
Using VB and ADO to return and update Record Set based XML
SeriousConsulting.com - Sean Grimaldi's Web site
