Although the Connection object simplifies the task of connecting to a database, the Connection object has limitations.
Specifically, the Connection object itself cannot be used to retrieve and display database information; rather, you must know exactly what changes you need to make to a database, then implement each change through the use of a query.
For retrieving and manipulating data, ADO provides the Recordset object. As its name implies, the Recordset object is used to contain a set of database records, which can be the entire set of records from a table, or the results of a specified query command.
The behavior of a Recordset object is affected by its environment ( client or server ), and on how the Recordset's CURSOR and LOCK properties are configured. Here we discuss Recordset objects in brief, as used in active server pages.
For most data access and handling chores, we can invoke a "shortcut" method using Recordset.Open, to both open a connection and issue a command over that connection in one operation.
To establish a connection using the Recordset object, first create an instance of the object.
Set recordset=Server.CreateObject( "ADODB.Recordset" )
Then open the recordset with the specified parameters:
recordset.Open Source, ActiveConnection,
CursorType, LockType, CommandType
NOTE: The Open method must be written as a single line command without any line breaks. The code examples shown here are done so only for readability.
Source can be any of the following:
- a table name
- an SQL statement
- a valid Command object variable name
- a stored procedure call
ActiveConnection can either be:
- a valid Connection object variable name
- or a String containing ConnectionString parameters
CursorType, LockType, and CommandType are optional arguments, which are set to the object's defaults when not specified. There are cases, though, when these arguments MUST be specified, as shown below.
The CursorType parameter specifies the type of cursor that the provider should use when opening the Recordset, and can be one of the following constants:
| adOpenDynamic |
Opens a dynamic-type cursor — allows one to view additions, changes, and deletions by other users, and allows all types of movement through the Recordset. |
| adOpenKeyset |
Opens a keyset-type cursor — behaves like a dynamic cursor, except that it prevents one from seeing records that other users add, and prevents access to records that other users delete. Data changes by other users will still be visible. |
| adOpenStatic |
Opens a static type cursor — provides a static copy of a set of records to find data or generate reports. Additions, changes, or deletions by other users will not be visible. |
| adOpenForwardOnly |
( Default ) Opens a forward-only cursor — behaves identically to a static cursor except that it allows to scroll forward only through records. This improves performance in situations where you need to make only a single pass through a Recordset. |
The Recordset object enable authors to precisely control the behavior of cursors, improving the ability to examine and update results.
The LockType parameter specifies what type of locking ( concurrency ) the provider should use when opening the Recordset, and can be one of the following constants:
| adLockReadOnly |
( Default ) Read-only — users cannot alter the data. |
| adLockPessimistic |
Pessimistic locking, record by record — the provider does what is necessary to ensure successful editing of the records, usually by locking records at the data source immediately upon editing. |
| adLockOptimistic |
Optimistic locking, record by record — the provider uses optimistic locking, locking records only when the Update method is called. |
| adLockBatchOptimistic |
Optimistic batch updates — required for batch update mode as opposed to immediate update mode. |
It is obvious from the above that, for record updates in a multi-user environment, the LockType must be specified to other than the default.
Providers ( other than Microsoft's ) may not support all lock types. If a provider cannot support the requested LockType setting, it will substitute another type of locking.
The CommandType parameter specifies how the provider should evaluate the Source argument if it represents something other than a Command object, and can be one of the following constants:
| adCmdText |
Indicates that the provider should evaluate Source as a textual definition of a command. |
| adCmdTable |
Indicates that the provider should evaluate Source as a table name. |
| adCmdStoredProc |
Indicates that the provider should evaluate Source as a stored procedure. |
| adCmdUnknown |
( Default ) Indicates that the type of command in the Source argument is not known. |
If you know what type of command you're using, setting the CommandType property instructs ADO to go directly to the relevant code. For example, when opening an entire table as the Recordset's source, the constant adCmdTable should be specified.
If the CommandType property value is adCmdUnknown ( the default value ), you may experience diminished performance because ADO must make calls to the provider to determine if the CommandText property is an SQL statement, a stored procedure, or a table name.
When used with some providers ( such as Microsoft's ), you can create Recordset objects independently of a previously defined Connection object by passing a connection string with the Open method. ADO still creates a Connection object, but it doesn't assign that object to an object variable.
However, if you are opening MULTIPLE Recordset objects over the same connection, you should explicitly create and open a Connection object to assign the Connection object to an object variable.
You can create as many Recordset objects as needed, but if you do not use a Connection object when opening Recordset objects, ADO creates a new Connection object for each new Recordset, even if you pass the same connection string.
When you have concluded your operations with an open Recordset, use the Close method to free any associated system resources. Using the Close method to close a Connection object also closes any active Recordset objects associated with the connection.
Unless otherwise noted, we shall be using the Recordset.Open method in these tutorials.
The following describes the specific steps to create this type of database connection as used in these tutorials:
strProvider="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=dbtutor.mdb"
- create the Recordset object
set rsProds=Server.CreateObject( "ADODB.Recordset" )
- choose a record source from the database
After initializing the Recordset object, the next step is to decide which of the tables ( or views ) in the database you will use as the source of the records to display on the page.
- open the Recordset object
rsProds.Open "products", strProvider, , , adCmdTable
Successful database applications employ both the Connection object to establish a link, and the Recordset object to manipulate returned data. By "teaming up" the specialized functions of both objects, we can develop database applications to carry out almost any data-handling task.
The sample below demonstrates how to connect to a data source using both the Connection and Recordset Open methods, and returns the collection of ADO Properties for each object if successful.
The sample may not be of use to a site's users, but surely can serve as a tool to examine the supported properties of ANY given data source, as long as you have the "right connection".
Each Property object represents a dynamic characteristic of an ADO object specific to the provider, which can be referenced thru the collection, for example, to use in custom error-handling routines.
Well, that's about it for now. Still a lot to polish and consider, but hopefully you've at least gained something to help you "get connected".
Next in this series:
- ADO Primer: Part II
- Displaying information from a database
- Selecting records from a database
- Retrieving properties of a database
Parameter values for most ADO methods such as Recordset.Open are by default specified ( and returned ) as integers corresponding to the constants mentioned above.
For example, when specifying the CursorType, LockType, and CommandType options, you would normally use:
recSet.Open dbRecordSource, strProvider, 1, 3, 2
IIS ( and PWS ) both ship with ADO Constants files that can be used to specify the counterpart text values for the parameter options, wherein you can instead use:
recSet.Open "dbTableName", strProvider,
adOpenKeyset, adLockOptimistic, adCmdtable
To use these constants in your own pages, you must include the appropriate file in the .asp page by using a server-side include ( SSI ) statement.
If your primary scripting language is VBScript, the file you should include is named adovbs.inc. If you are using JScript, you should include the file named adojavs.inc. These files are originally installed in the /Program Files/Common Files/System/ADO/ directory.
ADO Primer: Displaying information from a database
