HOWTO: Demonstration of ADO Bookmarks and Move Methods

ID: Q194610

The information in this article applies to:

SUMMARY

The ActiveX Data Objects (ADO) Recordset object has methods and properties that allow you to use it to perform FoxPro-style record navigation.

When you open a Recordset object, each record is assigned a unique bookmark. You can use the Bookmark property to save the location of the current record so you can return to it at a later time.

Not all Recordset objects support bookmarks. For example, forward-only and dynamic cursors do not support the use of bookmarks. You can use the Supports property of the recordset to determine if does support bookmarks.

The EOF property is set to true when the user tries to move past the last record of a Recordset.

The BOF property is set to true when the user tries to move to a location before the first row in the Recordset. The next attempt to MovePrevious results in a run-time error. Both BOF and EOF are set to true if the recordset is empty.

The various move methods (MoveFirst, MoveLast, MoveNext and MovePrevious) are similar in effect to the FoxPro SKIP and GOTO commands.


MORE INFORMATION

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 it can be downloaded from the following Web address:

http://www.microsoft.com/data/
The following example establishes a DSN-less connection and creates a Recordset based on a query of the sample SQL Server AUTHORS table. It uses the various Move methods of the Recordset and tests the BOF and EOF properties.

Sample Code


  #DEFINE adUseClient 3

   oRecordSet = CREATEOBJECT("ADODB.Recordset")

   lcSQL = "Select * from authors"

   * Modify the SERVER=, DATABASE=, UID= and PWD= parameters
   * as needed.
   WITH oRecordSet
      .CursorLocation = adUseClient
      .OPEN(lcSQL, ;
         "DRIVER={SQL Server};" + ;
         "SERVER=YourServerName;" + ;
         "DATABASE=pubs;" + ;
         "UID=YourUserID;PWD=YourPassword")
   ENDWITH

   CLEAR
   oRecordSet.MoveLast
   ? "Last record last name: ", oRecordSet.FIELDS("au_lname").VALUE
   oRecordSet.MoveFirst
   ? "First record last name: ", oRecordSet.FIELDS("au_lname").VALUE
   oRecordSet.MovePrevious
   IF oRecordSet.BOF
      ? "Can't move prior to BOF - going to top"
      oRecordSet.MoveFirst
   ENDIF
   oRecordSet.MoveNext
   ? "Second record last name: ", oRecordSet.FIELDS("au_lname").VALUE
   oRecordSet.MoveLast
   oRecordSet.MoveNext
   IF oRecordSet.EOF
      ? "Can't move past EOF - going to bottom"
      oRecordSet.MoveLast
   ENDIF

   oRecordSet.MovePrevious
   ? "Next-to-last record last name: ", oRecordSet.FIELDS("au_lname").VALUE
   oBookmark = oRecordSet.Bookmark
   ? "Bookmark: ", oRecordSet.FIELDS("au_lname").VALUE
   oRecordSet.MoveFirst
   ? "Move to the top: ", oRecordSet.FIELDS("au_lname").VALUE
   oRecordSet.Bookmark = oBookmark
   ? "Return to Bookmark: ", oRecordSet.FIELDS("au_lname").VALUE

   oRecordSet.CLOSE

Additional query words: kbVFp600 kbADO200 kbActiveX kbMDAC kbDatabase kbSQL 


Keywords          : kbVFp600 
Version           : WINDOWS:6.0
Platform          : WINDOWS 
Issue type        : kbhowto

Last Reviewed: July 28, 1999