INFO: Reusing ADO Recordsets Maintains Properties (195512)

SUMMARY

When you create a Recordset object without an active command, the recordset remains empty until it is associated with one. Therefore, it is possible to create a Recordset object at any point in a procedure, but not populate it until necessary.

This makes it possible to create a recordset and then associate it with an active command based on user input. The developer does not have to create a recordset for each possible scenario.

Another benefit is that you can close and reopen the recordset as needed without re-creating it. For example, you can close a recordset and then reopen it, as needed, using a modified command. It is less expensive in terms of performance to modify a recordset without recreating it because the application is not required to reprocess all the information that defines a recordset.

The example in the MORE INFORMATION section demonstrates setting several properties of a recordset, performing a query, reusing the recordset to perform a query on a different table, and displaying the properties, to demonstrate that the Recordset properties are not altered by reuse.

MORE INFORMATION

This example instantiates a Recordset object using the SQL Server driver, sets it to use server-side cursors, a dynamic cursortype, and optimistic locking. It queries the AUTHORS table in the SQL Server sample PUBS database and displays the count of returned records and several recordset properties. It then closes this recordset, reuses the recordset to perform another query on the TITLEAUTHOR table, and displays the same information described in the previous sentence.

To use this example, you must have Microsoft Data Access Components (MDAC) version 2.x or later installed, which is included in the data components of Visual Studio 6.0 or can be downloaded from the following Web address:

http://msdn.microsoft.com/dataaccess

NOTE: Change the SERVER, UID and PWD parameters to match your SQL Server installation.

Sample Code

   * Reusers.prg - Demonstrate that properties are maintained
   * when an existing recordset object is closed and reused.

   #DEFINE adUseServer 2
   #DEFINE adOpenDynamic 2
   #DEFINE adLockOptimistic 3
   #DEFINE CR chr(13)

   oRecordSet = CreateObject("ADODB.Recordset")
   lcSQL = "select * from authors"

   * Recordset is server-side, dynamic with
   * optimistic locking.
   WITH oRecordSet
      .CursorLocation = adUseServer
      .CursorType = adOpenDynamic
      .LockType = adLockOptimistic
      .Open(lcSQL, ;
         "DRIVER={SQL Server};" + ;
         "SERVER=YourServerName;" + ;
         "DATABASE=pubs;" + ;
         "UID=YourUserID;" + ;
         "PWD=YourPassword")
   ENDWITH

   =MessageBox("Query 1 record count: " + ;
      ltrim(str(oRecordSet.RecordCount)) + CR + ;
      "Cursor Location:" + ltrim(str(oRecordSet.CursorLocation)) + CR + ;
      "Cursor Type: " + ltrim(str(oRecordSet.CursorType)) + CR + ;
      "Lock Mode: " + ltrim(str(oRecordSet.LockType)))

   * Must use the close method to close before changing the
   * command.
   oRecordSet.Close

   lcSQL = "select * from titleauthor"

   * Notice no other properties set.
   oRecordSet.Open(lcSQL, ;
      "DRIVER={SQL Server};" + ;
      "SERVER=YourServerName;" + ;
      "DATABASE=pubs;" + ;
      "UID=YourUserID;" + ;
      "PWD=YourPassword")

   * Note that the properties set in the original
   * persist in its new incarnation, even though the original was
   * closed.
   =MessageBox("Query 2 record count: " + ;
      ltrim(str(oRecordSet.RecordCount)) + CR + ;
      "Cursor Location:" + ltrim(str(oRecordSet.CursorLocation)) + CR + ;
      "Cursor Type: " + ltrim(str(oRecordSet.CursorType)) + CR + ;
      "Lock Mode: " + ltrim(str(oRecordSet.LockType)))