ASP ScriptingJava ScriptingCGI ScriptingHTMLJavaLanguage CERP Education Links

The RecordSet Object

  Using the Recordset Object

     The syntax for creating an instance of a Recordset Object is similar to that for other objects. Again, we have to use the CreateObject method. However, before you do this, you need to have an open Connection Object, otherwise the script will raise an error.

Set rsfMyCursor = Server.CreateObject("ADODB.Recordset")

Use the following syntax to open the Recordset Object once it is instantiated:

rstMyCursor.Open Source, ActiveConnection, CursorType, LockType, Options

     Source is a variant that evaluates to a valid Command Object variable, an SQL statement, a table name, a stored procedure call, or the file name of a persisted Recordset. ActiveConnection is either a variant that evaluates to a valid Connection Object variable or a string containing Connection String parameters. CursorType is a CursorTypeEnum value that determines the type of cursor that the provider should use when opening the Recordset. LockType is a LockTypeEnum value that determines what type of locking the provider should use when opening the Recordset. Options is a long value that indicates how the provider should evaluate Source if it represents something other than a Command object.

     The optional arguments can also be set as properties of the Recordset Object before opening it. Take a look at the following code: 

<%
Set objMyConn = Server.CreateObj.ect('NADODB.Connection") 
objMyConn.Open "MyDSN"
Set objMyCommand = Server.Create0bject("ADODB.Command")
Set objMyCommand.ActiveConnection= objMyConn 
objMyCommand..CommandText = "MyTable" 
objMyCommand..CommandType = adCmdTable

Set rstMyCursor= Server.CreateObjeet("ADODB.Recordset") 
Set rstMyCursor.Source = objMyCammand 
rstMyCursor.CursorType = adOpenKeyset
rstMyCursor LockType = adLockOptimistie 
rstMyCursor.Open
%>

     This opens a Recordset Object that holds data from the table MyTable in the database that is connected via the DSN MyDSN. The recordset is a dynamic cursor with optimistic locking.

  Types of Cursors

     ADO supports four types of cursors (or recordsets); each having its own limitations and scalability measure. The type of cursor can be changed by setting the CursorType property of the Recordset Object. The capabilities of each kind of cursor depends on the capabilities of the provider.

  Dynamic Cursors

     These cursors are opened by setting the CursorType property to adOpenDynamic. They automatically refresh themselves at regular intervals, hence, they reflect all changes made by any user to the base tables. They also allow movement in any direction across the recordset.

  Keyset Cursors

     Keyset cursors are opened by setting the CursorType property to adOpenKeyset. These cursors allow as much flexibility as dynamic cursors. The only difference is that changes made to the data by other users is not reflected on the cursor. However, records deleted by other users will not be accessible.

  Static Cursors

     Static cursors are opened by setting the CursorType property to adOpenstatic. These are similar to snapshots in MS Access. Static cursors do not detect changes made to the membership, order, or values of the result set after the cursor is opened. Static cursors may detect their own updates, deletes, and inserts, depending on the provider. They allow forward and backward navigation and bookmarks if supported by the provider.

  Forward Only Cursors

     These cursors are opened by setting the CursorType property to adOpenForwardOnly. This is the default cursor created if no other type is specified. The cursor does not keep a track of any changes made to the data and allows movement only in the forward direction.

  Navigation 

     Moving through records is pretty straightforward. MoveFirst moves the record pointer to the first record and MoveLast moves the it to the last record in the recordset. The MovePrevious method moves to the previous record and the MoveNext method moves to the next record. The Move method moves the pointer by a specified number of records. The number can be . positive for forward movement and negative for backward movement.

rstMyCursor.Move NumberofRecords

     Note that if you try to jump beyond the first or last records, the script will generate an error. To avoid this, you can check the BOF (Beginning Of File) and EOF (End Of File) properties of the Recordset Object before moving.

  Recordset Pages

     ADO manages recordsets in pages. That is, every recordset can be grouped into sets of records called pages. The number of records held in one such set is controlled by the PageSize property. The AbsolutePage property of the Recordset Object holds the current position within the recordset. To move through the recordset, change the value of this property and the output will display records beginning from this one. The PageCount property of the Recordset Object stores the number of pages in the recordset.

     To place this in a more understandable perspective, let's assume we have a query which retrieves 1000 records. Set PageSize to 50. The PageCount propertywould be 20. Now You cannot directly read the AbsolutePage when you display the records, the first fifty records would be displayed on the browser. property. When setting this property, store it to When the user moves to the next page (by clicking on the relevant link or button), you a Session variable so that it can be retrieved would set the Absolutepage property to 2 and display the records. This time, the page would when the user moves to the next page. display records number 51 through 100.