Submit Tutorials

Creating a Recordset Object

The general format for creating (instantiating) a Recordset Object is similar to the method used to create a Connection Object,

where, RecordsetObject is a name assigned to the object and by which it can be referenced in our script. The Server Object uses its CreateObject method to create an ADODB (Active Data Object DataBase) Recordset Object and assigns it to the name provided. For this example, we'll use RSObj as the name of our object and add the necessary Set statement to our script:

We now have available a Recordset Object named RSObj that our script can use to gain access to the Accounts table of our database.

Opening a Table

Once a Recordset Object is created, its built-in Open method is available to extract information from a database table and make it available to our script for processing. The general format for extracting ALL the information from a table is to open the entire table.

Here, TableName is the name of a database table and Connection Object is the name of an existing connection to the database containing the table. In the present application the name of our table is Accounts and the existing connection to the database containing this table is CNObj. So, we add the RSObj.Open statement to our script:

With this statement in place, our script has extracted the entire Accounts table from the Database.mdb database and made it available for processing by the script.

The Recordset.Fields Collection

When a recordset is opened, ASP generates a Recordset.Fields Collection. This Collection is a set of data extracted from the associated table. When "TableName" is used in the Opencopy of the table--a recordset extracted from the table. statement, the Collection contains all of the information from the table, organized into rows and columns that exactly match the structure of the table. Keep in mind, though, that your script does not have access to the actual table. It has access to an in-memory

The name of the Collection is Recordset.Fields, meaning that the name you assigned to your Recordset Object is used as the Recordset prefix. Thus, the name of the Collection extracted from the Accounts table in our example is the RSObj.Fields Collection, since we created and opened a Recordset Object named RSObj. You can visualize the Collection as having the following structure and contents:

When the Accounts table is opened, the entire collection of records is loaded into the RSObj.Fields Collection. As in the Request.Form or Request.QueryString Collection, the data values contained in the recordset can be referenced through the notation RSObj.Fields("FieldName"), where the FieldName is taken from the field names assigned when the table was created in Access. However, unlike a Request.Form or Request.QueryString Collection, where a single name is associated with a single data value, there is more than one data value that can be referenced through a recordset field name. There are, for instance, three accounts and three passwords in the table. So, there are up to three values each that could be referenced through the specifications RSObj.Fields("Account") and RSObj.Fields("Password").

Database Cursors

The particular value referenced through RSObj.Fields("Account") or RSObj.Fields("Password")cursor. A cursor is a pointer used to keep track of a record location in a recordset. When a recordset is first opened, its cursor is pointing at the firstRSObj.Fields("Account") would be a reference to the value "aaaaa" and a reference to RSObj.Fields("Password") would be a reference to the value "11111." depends on the location of the database record (the first set of fields) in the recordset. Therefore, a script reference to

Through scripting, you can move the cursor from one record to another in the recordset, permitting a reference to different values for the named fields. If you moved the cursor forward one record in the recordset, the value of RSObj.Fields("Account") would be "bbbbb" and the value of RSObj.Fields("Password") would be "22222."

At this point in our example script, we have opened a connection to our database and extracted a recordset containing all of the records in the Accounts table. The recordset cursor is pointing to the first record in the recordset. Our next order of business is to iterate through this recordset, looking for an account and password that matches those entered by the visitor.