How To Demonstration of ADO Bookmarks and Move Methods (194610)

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 it supports 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://msdn.microsoft.com/dataaccess

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