When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
Message Board
Related Web Technologies
User Tips!
Coding Tips

Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Stump the SQL Guru!
XML Info
Author an Article
ASP ASP.NET ASP FAQs Message Board Feedback
Print this page.
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!

    - continued -

    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.addnew "ryg_status","Green" 
    RsStatus.addnew "ryg_status","Yellow" 
    RsStatus.addnew "ryg_status","Red" 

    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.

    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


  • 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

  • ASP.NET [1.x] [2.0] | ASPFAQs.com | Advertise | Feedback | Author an Article