Home

ASP ADVANCED

You can open a Recordset with one of four types of cursors.  A cursor determines the types of operations you can perform with a Recordset.  The cursor also determines what types of changes other users can make in an open Recordset.  The following list describes the cursor types and their restrictions:

  • adOpenForwardOnly.  Using a forward-only cursor, you can only move forward through the records in a Recordset.
  • adOpenKeyset.  Using a keyset cursor, you can move both forward and backward in a Recordset.  If a record is deleted or changed by another user, this is reflected in the Recordset.  However, if a new record is added by another user, the new record won't appear in the Recordset.
  • adOpenDynamic.  Using a dynamic cursor, you can move both forward and backward in a Recordset.  Any changes made to records by other users are reflected in the Recordset.
  • adOpenStatic.  Using a static cursor, you can move both forward and backward in a Recordset.  However, a static cursor doesn't reflect changes in the records made by other users.

By default, when you open a Recordset, it's opened with a forward-only cursor.  This means that you can only move forward through the records in the Recordset, using the MoveNext method.  Other types of operations with the Recordset are not fully supported.

The advantage of a forward-only cursor is that it's fast.  Whenever you can get away with it, you should use a forward-only cursor.  However, if you need to open a Recordset with a richer type of cursor, you can use a script like the following:

<!-- #INCLUDE VIRTUAL="ADOVBS.inc" -->
Set MyConn=Server.CreateObject("ADODB.Connection")
Set RS=Server.CreateObject("ADODB.RecordSet")
MyConn.Open "FILEDSN=d:\Program Files\Common Files\ODBC\Data Sources\MyData.dsn"
RS.Open "SELECT * FROM MyTable", MyConn, adOpenDynamic
RS.Close
MyConn.Close
%>

To open a Recordset with a specific type of cursor, you must explicitly create the Recordset and open it with the cursor type.  To do this, you first create an instance of the Recordset object. Next, you use the Open method to open the Recordset with a particular connection and cursor type.  In this script, the RS Recordset is opened using the MyConn Connection object and a dynamic cursor.

When opening a Recordset, you can also specify the type of locking to use.  The locking type determines how the database will handle situations in which more than one user attempts to change a record at the same time.  You can specify any one of the following four locking types:

  • adLockReadOnly.  Indicates that you can't modify the records in a Recordset.
  • adLockpessimistic.  Indicates that a record should be locked immediately upon editing.
  • adLockOptimistic.  Indicates that a record should be locked only when the Recordset's Update method is called.
  • adLockBatchOptimistic.  Indicates that the records will be batch-updated.

By default, a Recordset uses read-only locking.  To specify a different locking type, you include one of these locking constants when you open the Recordset.  Here's an example of how you can do this:

<!-- #INCLUDE VIRTUAL="ADOVBS.inc" -->
<%
Set MyConn=Server.CreateObject("ADODB.Connection")
Set RS=Server.CreateObject("ADODB.RecordSet")
MyConn.Open "FILEDSN=d:\Program Files\Common Files\ODBC\Data Sources\MyData.dsn"
RS.Open "SELECT * FROM MyTable", MyConn, adOpenDynamic, adLockPessimistic
RS.Close
MyConn.Close
%>

This script is the same as the preceding script except for the addition of the locking type. When the RS Recordset is opened, it's opened using a pessimistic lock.  This means that the records in the Recordset can be modified. (You learn how to do this in the next section.)

Finally, when you open a Recordset, you can specify an options parameter.  The options parameter indicates the type of command string being used to open the Recordset.  Warning the ADO about the contents of the string being executed helps ADO to execute the command more efficiently.

You can use the following constants for the Options parameter:

  • adCMDTable.  The string being executed contains the name of a table.

  • adCMDText.  The string being executed contains a textual command.

  • adCMDStoredProc.  The string being executed contains the name of a stored procedure. 

  • adCMDUnknown.  The contents of the string are unspecified. (This is the default value.)

In the following script, the options parameter is used to warn the ADO that the content of the command string is a textual command:

<!-- #INCLUDE VIRTUAL="ADOVBS.inc" -->
<%
Set MyConn=Server.CreateObject("ADODB.Connection")
Set RS=Server.CreateObject("ADODB.RecordSet")
MyConn.Open "FILEDSN=d:\Program Files\Common Files\ODBC\Data Sources\MyData.dsn"
RS.Open "SELECT * FROM MyTable", MyConn,
adOpenDynamic, adLockPessimistic, adCMDText
RS.Close
MyConn.Close
%>