Data Access and Transactions

Recordsets and Cursors

A Recordset, in its simplest form, is a collection of rows returned from an ADO database query. In addition to the data, each Recordset object includes the database schema (or metadata) and a connection to the database. You can create and access a Recordset with just a few lines of script:

<%
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open "SELECT * FROM Authors", "DSN=Pubs;UID=sa;PWD="
'Loop through the recordset with MoveNext.
Do Until rs.EOF
'Access record fields here.
rs.MoveNext
Loop
rs.Close
%>

All Recordset objects include a cursor, which marks the current record. When a Recordset is first opened, the cursor is positioned to the first record that matched the query. As you move the cursor forward with MoveNext, it will eventually run out of records, causing the Recordset’s EOF (End Of File) property to change to True.

All of the cursor types will let you insert and update records in a Recordset, unless you have specified adLockReadOnly as the lock type. Different cursor types give you varying degrees of visibility for database actions performed by other users. Table 7.3 lists cursor types based on their level of functionality.

Table 7.3 Recordset Functionality by Cursor Type

Cursor Type	Insert, Update Records	View External Updates/Deletions	View External Inserts
Forward-only, read-only (default)	False	False	False
Static	True	False	False
Keyset	True	True	False
Dynamic	True	True	True

Table 7.4 names and describes the four types of cursors available for ADO Recordset objects. There is no single cursor type that you should always use. Your choice of cursor type should depend on the functionality you require. The following sections further describe the differences in these cursor types.

Table 7.4 ADO Cursor Types

Cursor Type	Description
adOpenForwardOnly	Forward-only cursor. You can only scroll forward through records. This is the default cursor type.
adOpenStatic	Static cursor. A static copy of a set of records that you can use to find data or generate reports. Additions, changes, or deletions by other users are not visible. The Recordset is fully navigable, forward and backward.
adOpenDynamic	Dynamic cursor. Additions, changes, and deletions by other users are visible, and all types of movement through the Recordset are allowed (except for bookmarks if the provider doesn’t support them).
adOpenKeyset	Keyset cursor (keysets are values used to access specific records or rows). Like the dynamic cursor type, except that you can’t see records that other users add. Deletions and other modifications made by other users are still visible.

Since most applications typically access the Recordset sequentially from the first record, the default cursor type is optimized for forward-only traversal. (In the case of SQL Server, it is read-only as well. If you use the Execute method of the Connection (and Command) object, you will always get a forward-only, read-only cursor. This is also the default behavior of Recordset.Open if no extra parameters are specified.

When you use the Recordset.Open method, however, you have the opportunity to configure your Recordset exactly as you’d like it (cursor type, record locking method, number of records to cache, and so on). The following example demonstrates how to create Recordset objects with varying degrees of cursor functionality:

Function SimpleCursor()
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open "pubs", "sa", ""
'Creates a forward-only, read-only cursor.
Set SimpleCursor = cn.Execute("select * from authors")
End Function
Function MoreFunctionalCursor()
Set cn = Server.CreateObject("ADODB.connection")
cn.Open "pubs", "sa", ""
'Requests dynamic keyset cursor and optimistic 
'Record locking (concurrency).
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open "select * from authors", cn, adOpenKeyset, adLockOptimistic
Set MoreFunctionalCursor = rs
End Function

See the following: