The Recordset object is the primary object you will work with when you are accessing data using ASP and ADO. A Recordset is essentially a database cursor, which you can page through to view each row of a query or table. To begin with, we'll declare our recordset:
'Create Recordset Object
Dim objRst 'Create Recordset Variable
Set objRst = Server.CreateObject("ADODB.Recordset")
Set objRst.ActiveConnection = objConnect
Set objRst.Source = objCmd
Note that our Recordset references our connection objConnect and our Command object, objCmd. This is the minimal requirements for a recordset that uses the command object. You can get away with fewer parameters when declaring your recordset, if you opt not to use the Command object(in fact there are a few different ways to set up your recordset), but I won't cover them here. You should understand two additional parameters for your recordset which can affect how your query performs. They are the CursorType and LockType properties. Each of these has 4 possible values. The default values give you a Read-Only, Forward-Only recordset. For the operations we will be performing in this tutorial, the default options are ideal. If you choose to use the Recordset object instead of the Command object for your updates and inserts, you will need to become more familiar with the Cursor and Lock types. Here is an excerpt from the adovbs.inc file, listing the cursor and locktypes:
'---- CursorTypeEnum Values ----
Const adOpenForwardOnly = 0
Const adOpenKeyset = 1
Const adOpenDynamic = 2
Const adOpenStatic = 3
'---- LockTypeEnum Values ----
Const adLockReadOnly = 1
Const adLockPessimistic = 2
Const adLockOptimistic = 3
Const adLockBatchOptimistic = 4
Note that Cursor is 0 to 3 and Lock is 1 to 4. Don't ask me why Microsoft did it this way. To set these properties, you would use code like:
'Set Locktype and Cursortype
Set objRst.Locktype = adLockReadOnly
Set objRst.Cursortype = adOpenForwardOnly
Now that we've managed to declare all of our necessary objects, we're finally ready to access some data. The first thing you need to do is test out an SQL query to make sure it returns some results from your database. A simple "SELECT * FROM TableName" will suffice. Use your query for the CommandText in the following piece of code, with a valid column name in place of "field":
objCmd.CommandText = "SELECT * " & _
" FROM mytable "
objRst.Open
If Not objRst.EOF Then
Response.Write(objRst("field"))
End If
objRst.Close
When you execute this SQL query, it will print the first record's "field" value. If no values exist, it will do nothing. If you do not test for EOF before attempting to access recordset values, you will generate a run-time error (when you reach EOF). Always test for EOF before attempting to access your recordset. Note that I immediately close the recordset. Get in the habit of closing your recordsets as soon as you are finished using them, especially within loops. If you attempt to open a recordset that is already open, you will generate a run-time error.
Update: If you loop through a recordset, make sure you call objRst.MoveNext within your While Not objRst.EOF loop. Otherwise you'll enter an infinite loop. This is an extremely common mistake. Also, avoid looping through recordsets. A much faster technique is to use GetRows, which will return the results in an array. Here's some more info on GetRows.
So far we haven't done anything that required the use of the command object, although it is a nice debug tool to be able to write out the CommandText property to check your SQL. The next example demonstrates why the Command object works better than the ADO recordset object for manipulating the data in your database.
objCmd.CommandText = "UPDATE mytable SET " & _
" field = 'Somevalue'" & _
" WHERE field = 'Some Other Value' "
objRst.Open
Note that for this Command, you do not need to close your recordset. This command uses your database's own UPDATE routine, which will almost always be far faster than using ASP's ADO Recordset object to perform your update. Ideally, you should move all critical database calls to stored procedures on your database server, but the above method is almost as efficient. Similarly, you can perform the following statements using your database:
objCmd.CommandText = "DELETE FROM mytable " & _
" WHERE field = 'Some Other Value' "
objRst.Open
objCmd.CommandText = "INSERT INTO mytable " & _
" (field1,field2) " & _
" VALUES ("this value","this other value") "
objRst.Open
Note once again that I didn't have to close the recordsets between operations, because they do not result in any data being sent to the recordset. You only need to worry about closing your recordset/command object combination when you are performing SELECT statements.
Update: You really really really should use stored procedures for your database access, for security reasons as well as performance. Using SQL in your page comprised of strings concatenated together opens up your code to SQL Injection Attacks.