INFO: Reusing ADO Recordsets Maintains Properties

This article was previously published under Q195512
This article has been archived. It is offered "as is" and will no longer be updated.
SUMMARY
When you create a Recordset object without an active command, the recordsetremains empty until it is associated with one. Therefore, it is possible tocreate a Recordset object at any point in a procedure, but not populate ituntil necessary.

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

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

The example in the MORE INFORMATION section demonstrates setting severalproperties of a recordset, performing a query, reusing the recordset toperform a query on a different table, and displaying the properties, todemonstrate 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 optimisticlocking. It queries the AUTHORS table in the SQL Server sample PUBSdatabase and displays the count of returned records and several recordsetproperties. It then closes this recordset, reuses the recordset to performanother query on the TITLEAUTHOR table, and displays the same informationdescribed 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 ofVisual Studio 6.0 or can be downloaded from the following Web address:

http://msdn.microsoft.com/en-us/data/aa937729.aspx

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)))				
ADO
Properties

Article ID: 195512 - Last Review: 02/21/2014 00:30:47 - Revision: 4.2

  • Microsoft Visual FoxPro 6.0 Professional Edition
  • Microsoft Data Access Components 2.1 Service Pack 2
  • Microsoft Data Access Components 2.5
  • Microsoft Data Access Components 2.6
  • kbnosurvey kbarchive kbdatabase kbinfo kbsqlprog KB195512
Feedback