ADO Objects In Depth
| Connection | Command | RecordSet |
Introduction
If you've read Introduction to ActiveX Data Objects, you are by now familiar with the "Big 3" objects of ADO: The Command, Connection, and RecordSet objects. I focused on the RecordSet object because you will most often be explicitly using the RecordSet object more than either of the other 2. In the "novice-level" tutorials on this web site, I used the Open method of the RecordSet object to execute a query and return a Recordset, because this is the easiest thing to do. Each object can implicitly create the other 2 whenever necessary, and there are many ways to accomplish the same programming goal with ADO. However, there are reasons why you would want to explicitly create a Connection or Command object.This article discusses the uses of all 3 of these objects, different ways to use them, and reasons why you would want to use one method over the other in specific cases.
As previously mentioned, each of these objects can be created independently, and implicitly create the other 2 when necessary. The following examples perform the same operation (executing a query to open and then close a recordset) 3 different ways, using the Command, Connection, and RecordSet objects:
RecordSet Method
<%
Set rs = Server.CreateObject("ADODB.RecordSet")
q = "SELECT * FROM mytable WHERE myfield LIKE 'my goodness!'"
rs.Open q, "DSN=mydsn;"
rs.Close
Set rs = Nothing
%> Connection Method <%
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open "DSN=mydsn;"
q = "SELECT * FROM mytable WHERE myfield LIKE 'my goodness!'"
Set rs = cn.Execute(q)
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
%> Command Method <%
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open "DSN=mydsn;"
Set cmd = Server.CreateObject("ADODB.Command")
cn.ActiveConnection = cn
cmd.CommandText = "SELECT * FROM mytable WHERE myfield LIKE 'my goodness!'"
Set rs = cmd.Execute(q)
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
Set cmd = Nothing
%>
Now, as you can see, each of these methods achieves the same goal, and the Command method, in this case, looks like the loser of the lot, which it is, in this case. In fact, I could have put 3 or 4 different versions of each of these examples which would have performed the same task. So, let's start by examining each object, according to how and when it's used:
The Connection Object
The Connection object is the "pipeline" of data. It contains all of the information necessary to connect to the database, and a good number of methods as well that enable you to work with the data, and even retrieve information about errors, or the structure of the database itself.When a Connection object is open, it is using resources on the server that should be freed up as quickly as possible. Therefore, while closing and destroying a Connection is handled implicitly, without any interference from you, it is always best to explicitly open, close, and destroy the Connection object. That said, it would seem convenient to use the Connection object to retrieve RecordSets, since the Open method of the Connection returns a RecordSet when a query returns a RecordSet. In fact, this is usually the case.
In fact, creating the Connection object can be used very effectively for queries which do not return RecordSets, such as "INSERT" "UPDATE" or "DELETE" queries. Example:
<%
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open "DSN=mydsn;"
q = "UPDATE mytable SET myfield = 'my goodness!' WHERE myotherfield LIKE 'my other goodness!'"
cn.Execute(q)
cn.Close
Set cn = Nothing
%>
Note that the Open method of the Connection object doesn't have code to store the RecordSet in a RecordSet object, as in the first Connection example. That's because the query doesn't return any records or even a RecordSet. This is very efficient code. You could do the same with the following, using a RecordSet object: <%
Set rs = Server.CreateObject("ADODB.RecordSet")
q = "UPDATE mytable SET myfield = 'my goodness!' WHERE myotherfield LIKE 'my other goodness!'"
rs.Open q, "DSN=mydsn;"
Set rs = Nothing
%>
Notice first that the "rs.Close" code is not executed in this block. That's because while the query is executed, the RecordSet is never populated. It exists because you created it, but it is empty. And, in fact, a Connection object has been implicitly created, but we have no way to grab it and close it. So, we destroy the object we don't need to create, and we can't destroy the Connection object explicitly. Bad waste of resources.
The Connection object has several methods to enable you to perform transactions. Transactions are blocks of SQL that are executed consecutively, such as several SQL statements. You can include code in a transaction to check for the success or failure of any given operation and roll back (cancel) all of the transaction that was executed prior to that moment. Here's an example:
<%
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open "DSN=mydsn;"
cn.BeginTrans
q = "SELECT * FROM mytable WHERE myfield LIKE 'my goodness!'"
Set rs = cn.Execute(q)
if rs.EOF then
cn.RollbackTrans
rs.Close
Set rs = Nothing
Response.Write "Failed."
else
rs.Close
Set rs = Nothing
q = "UPDATE mytable SET myotherfield = 'my other goodness!' WHERE myfield LIKE 'my goodness!'"
cn.Execute(q)
cn.CommitTrans
end if
cn.Close
Set cn = Nothing
%>
The Connection object can also be used to obtain information about the structure, or schema of the database, by using the OpenSchema method. (Note: This is somewhat advanced)
The OpenSchema method has the following syntax:
RecordSet = Connection.OpenSchema(Schema, [Restrictions], [SchemaID])
The "Schema" parameter is a long integer, and can have one of many values. The definitions for these values can be found in adovbs.inc. "Restrictions" (optional) is an array of query constraints for each Schema option. Usually, it will be the column names and values to filter on. "SchemaID" (optional) is a provider-specific schema query not defined by the OLEDB specification. Here's an example of the method in action:
<%
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open "DSN=mydsn;"
Set sch = cn.OpenSchema(adSchemaTables)
Response.Write sch("TABLE_NAME") & "<br>" & sch("TABLE_TYPE")
cn.Close
Set cn = Nothing
%>
Each Schema type returns a RecordSet containing different elements of the schema. If you plan to use this method, I would suggest getting a good book on ADO, such as the ADO 2.1 Programmer's Reference. Finally, certain properties of the Connection object can be used to obtain information from the database. You can use the Errors collection of the object to ascertain if there were any errors in your code. However, this requires turning off the default shutdown of the operation which occurs when there is an error, by using the "on error resume next" VBScript statement. The danger of this is that all errors will not stop the code from being executed. You have to write in error-handling code of your own to account for every possible error. It is not advisable generally.
The Properties collection of the Connection object can be used to gather information about the Properties of the Connection object. Each of the "Big 3" objects of ADO has a Property object which contains a large store of information about that object. The Field object also has a Properties collection. The details about the properties of each object are beyond the scope of this article. Again, a good reference, such as the ADO 2.1 Programmer's Reference would be good to have if you wish to work with the Properties collections.
The Command Object
The Command object has the functionality of executing commands to the database. In most cases, the Command object is created implicitly when executing an operation against the database. You can do this with the Connection object's Execute() method, or with the RecordSet's Open method. Each of these methods takes as an argument a "Command Text." In the novice examples on this web site, the command text is a SQL statement, as it is likely to be most of the time. When executing a prepared SQL statement, it isn't necessary to use the Command object explicitly.However, there are times when you absolutely need to use it. Probably the most common case is when executing a Stored Procedure or a Stored Query (Access), especially when the procedure or query takes and/or returns parameter values. The Command object contains the Parameters collection.
Parameters
The Parameters collection contains all of the parameter objects for the Command object. The parameters collection can be accessed in the following way:
variablename = Command_Object_Name.Parameters
A Parameter object has many properties which can be accessed and/or set programatically. This section will only touch on the most frequently used ones. For more information, a good reference, such as the ADO 2.1 Programmer's Reference would be useful. These properties include:
The Direction Property The parameter can have one of 5 directions, all defined in adovbs.inc.
The Name Property
adParamUnknown The direction is not known. This is the default value. adParamInput The parameter is passing informtion into the stored procedure or query. adParamOutput The parameter is retrieving information from the stored procedure or query. adParamInputOutput The parameter can be used to pass information to, or receive information from a stored procedure or query. adParamReturnValue The parameter will contain the return value of the stored procedure or query.
The name of the parameter is defined with this property.
The Size Property
This indicates the size in bytes to set aside for the parameter value.
The Type Property
This indicates the data type of the parameter object. Again, you will need a good ADO reference to find out the data types for this property.
The Value Property
This is the actual value assigned to the parameter.
The Item Property
This is used to index into the Parameters collection:
variablename = Parameters.Item(Index)
Command Object Properties
There are several properties of the Command object that are useful for working with parameters in stored procedures. The first is the CommandText property. This contains the SQL Statement, Stored Procedure name, or provider-specific command that is being sent to the database. Example:
<%
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open "DSN=mydsn;"
Set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = cn
cmd.CommandText = "SELECT * FROM mytable"
Set rs = cmd.Execute
rs.Close
cn.Close
Set rs = Nothing
Set cmd = Nothing
Set cn = Nothing
%>
Note the use of the ActiveConnection property here, as well as the Command object's "Execute" method. the ActiveConnection property can be a Connection object or a Connection String. I will cover the "Execute" method a little later.
The CommandType Property
The CommandType property indicates what type of Command is being executed. You can use this to optimize your code when using the Command object. If, for example, your Command returns no records, set the CommandType property to adExecuteNoRecords in conjunction with adCmdStoredProc: cmd.CommandType = adCmdStoredProc OR adExecuteNoRecords.
You will find the CommandType enumerations in adovbs.inc.
Command Object Methods
The CreateParameter Method
The CreateParameter object creates a new Parameter object in the Parameters collection of the Command object:
Set Parameter_name = Command_Object_Name.CreateParameter([Name], [Type], [Direction], [Size], [Value])
The parameters passed to this method correspond to the types described above in the section on Parameters. Example:
Set myparam = cmd.CreateParameter("LastName", adVarChar, adParamInput, 25, "Spencer")
The Execute Method
The Execute method executes a query, SQL statement, or Stored Procedure. It uses the following syntax:
Set RecordSet_Name = Command_Object_Name.Execute([Records Affected], [Parameters], [Options])
Of course, you only want to use a Recordset if the command returns a Recordset. The "Records Affected" (optional) parameter is a long integer, and will hold the number of records that the operation affected after the operation is complete. The "Parameters" (optional) parameter is an array of parameter values passed with a SQL statement. The "Options" (optional) parameter is a long integer defined in adovbs.inc., that indicates how the provider should interpret the CommandText property of the Command object. It can be one of the following:
adCmdText The Command is to be interpreted as a text command, such as a SQL statement. adCmdTable The Command is to be interpreted as the name of a table adCmdTableDirect The Command is to be interpreted directly as a table name.
It can enhance performance over adCmdTableadCmdStoredProc The Command is the name of a Stored Procedure or Query. adCmdUnknown The nature of the Command is unknown (default)
The RecordSet Object
You probably work the most with RecordSets, but as you can see, it is not often necessary or wise to explicitly create your RecordSet object. You can usually use the RecordSet returned by the Connection's or the Command's Execute method. But there are times when it is best to explicitly create your RecordSet.The Execute method returns a RecordSet, but it returns a RecordSet with all default properties, and no ability to edit them, as you must change the properties of a RecordSet before opening it. The default RecordSet has a Read Only/Forward only cursor. In many cases, this is no problem. But there are a number of cases where you will want a fully navigable cursor, or one which allows updates, inserts, and deletes. There are times when you will want to set other properties and execute other methods as well that require certain kinds of Cursor types or Lock types. Let's review these cases, first by examining the Recordset's properties. Note, this is a partial list.:
RecordSet Properties
The AbsolutePage Property
This property is used in conjunction with a number of other properties to "page" through RecordSets. Some applications use paging to do a "next x records" type of thing. When paging is used, the RecordSet is split into logical "pages" which consist of equal numbers of records. The number of records is determined by the "PageSize" property. When you set the AbsolutePage property, you point the cursor at the first record of the page you set with the property. Example:
rs.AbsolutePage = 2 ' ** Sets the cursor to the first record in the second page.
The PageCount Property
The PageCount property is the number of the last page of a RecordSet which has used paging (by setting the "PageSize" property).
The PageSize Property
This is the number of Records to return per page in a RecordSet which has been split via paging.
The ActiveConnection Property
This is used to identify or set or get the Active Connection which a RecordSet uses to connect to the database. It is useful when you are explicitly creating a RecordSet and setting its' properties. Since we discussed the fact that it's always best to explicitly create your Connection, you can open the Connection and set the ActiveConnection property of the RecordSet before opening it. You can also set the ActiveConnection property when using the RecordSet's Open method. The following 2 examples set the ActiveConnection of the RecordSet to a Connection object which we will create first. The first uses the ActiveConnection property of the RecordSet; the second uses the Connection's Execute method, and the third uses the RecordSet's Open method:
ActiveConnection Property
<%
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open "DSN=mydsn;"
Set rs = Server.CreateObject("ADODB.RecordSet")
rs.ActiveConnection = cn
q = "SELECT * FROM mytable"
rs.Open q
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
%> ActiveConnection Property <%
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open "DSN=mydsn;"
q = "SELECT * FROM mytable"
Set rs = cn.Execute(q)
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
%> Open Method <%
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open "DSN=mydsn;"
Set rs = Server.CreateObject("ADODB.RecordSet")
rs.Open "SELECT * FROM mytable", cn
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
%>
Again, there are situations in which you will want to do this, such as when re-using a RecordSet and/or a Connection, for performance purposes.
The Bookmark Property
This is a property which is used after a RecordSet has been opened. It is used to set a "BookMark" at the cursor location, so that you can move the cursor back to that location instantly at any time. In order to use Bookmarks, you must create a Cursor type that supports Bookmarks. That would be "adOpenKeyset" or "adOpenStatic." In order to set the CursorType property, you must explicitly create your RecordSet first.
The CursorType Property
The CursorType property is very important in terms of creating fully navigable cursors (backwards and forwards), and Bookmarks.The CursorType property is one of 4 enumerated constants found in adovbs.inc. These are:
Not all CursorTypes are supported by all providers. You may need to use the Supports() method to find out if the provider supports the CursorType you choose. More about that later.
adOpenForwardOnly This RecordSet only allows forward movement through the RecordSet, one record at a time. Bookmarks are not supported by this CursorType. It is the most efficient in terms of performance, and it is the default. adOpenKeyset This cursor gives you a set of records that is up to date with the underlying data, but additions and deletions may not be. Movement is supported both forwards and backwards. adOpenDynamic The cursor is kept up to date with the underlying data, including changes to any records. New records will be visible, but deleted records will not. Movement through the RecordSet is supported both forwards and backwards. adopenStatic The data is fixed at the time the cursor is created. Movement both forwards and backwards is supported.
The Index Property
The Index property indicates the index which is currently being used. Example:
rs.Index = "LastName"
The index must be a previously-defined index in the table being queried from. Not all providers support the Index property. Use the Supports() method to find out if the provider you're using supports it.
The LockType Property
This property sets or indicates the type of record locking to use. It is particularly useful when creating RecordSets that you want to add new records to or update programatically. There are 4 enumerated constants in adovbs.inc. for LockTypes:
| adLockReadOnly | This RecordSet can not be updated, deleted from, or added to. This is the default. |
| adLockPessimistic | Pessimistic locking, where the provider attempts to lock edited records at the data source, is used. |
| adLockOptimistic | Optimistic locking is used. The provider attempts to lock records on a row-by-row or a page-by-page basis, when Update is called. |
| adLockBatchOptimistic | Batch update mode. Locking occurs when UpdateBatch is called |
The MaxRecords property
This property is useful in situations where you want to limit the number of records returned. My Next 10 Records tutorial uses this property to limit the number of records returned per page.Example:
rs.MaxRecords = 10
The RecordCount Property
This property is the number of records in the current RecordSet. Note: You must create a fully-navigable cursor in order to use this property. If you use the wrong kind of cursor, or that CursorType is not supported by the provider, this value will be -1.
The Source Property
A Command object or SQL command for the data in the RecordSet object. this can be used when using the RecordSet object with a Command object, such as when executing a parameterized Stored Procedure or query, that returns a RecordSet.
RecordSet Object Methods
Note: This is only a partial list of methods which are relevant to ASP and to the explicit creation of a Recordset object in web applications.
The AddNew Method
This method must be used with an updateable RecordSet LockType. It creates a new record. The syntax for this is:
RecordSet_Name.AddNew([Fields List], [Values])
As you can see, it almost looks like an INSERT statement.
It can optionally have a fields list and a values list, to populate the record. However, it can be called without the fields list or values list, to create an empty record. The fields and values lists can be single items or arrays.
The Delete Method
You can use this to delete the current record or a group of records. Syntax:
RecordSet_Name.Delete([AffectRecords])
Note: This must be executed only when an updateable RecordSet is being used. The optional AffectRecords parameter is one of 4 enumerated constants found in adovbs.inc.:
The Find Method
adAffectAllChapters Deletes all child recordsets associated with the record or group of records affected. These are referred to as "chapters" adAffectCurrent Deletes only the current record in the RecordSet adAffectGroup Deletes all records that match the current "Filter" property adAffectAll Deletes all records in the RecordSet
This method searches the current RecordSet for a record matching certain criteria. Syntax:
RecordSet_Name.Find(Criteria, [Skip Records], [Search Direction], [Start]) Note: This method should only be used with a fully-navigable RecordSet. See CursorType for details.
The Criteria parameter is a string that looks like a WHERE clause. It can take only one criterion. The Skip Records (optional) parameter is an offset from the current row, or from a bookmark specified in the Start parameter, from which the search should begin.
The Search Direction (optional) parameter is an enumerated constant found in adovbs.inc, that indicated the direction of the search. It can be adSearchForward (forwards) or adSearchBackward (backwards).
The Start (optional) parameter is a bookmark from which the search should start.
The GetRows Method
The GetRows method places the contents of a RecordSet into a multi-dimensional array. Syntax:
Array_Name = RecordSet_Name.GetRows([Rows], [Start], [Fields])
This can be useful when storing RecordSets in Session or Application variables. when no parameters are used, it returns an array containing the same number of rows as the RecordSet, and the same number of columns as the number of fields in the table.
The Rows (optional) parameter specifies how many rows to retrieve into the RecordSet. It defaults to all. The Start (optional) parameter is a bookmark from which to start getting the rows.
The Fields parameter can be a single field name, an array of field names, a single ordinal number of the field to be fetched, or an array of ordinal numbers indicating which fields should be fetched.
The Move Method
This method can be used with a dynamic (fully navigable forwards and backwards) cursor, to move the record pointer (cursor) forwards or backwards by the number indicated by the NumRecords parameter. Syntax:
RecordSet_Name.Move(NumRecords, [Start])
The NumRecords parameter can be a positive (forward) or a negative (backward) number. The optional Start parameter is a bookmark from which to start.
The MoveFirst Method
Again, this method can only be used with fully navigable cursors. It takes no parameters, and moves the cursor to the first record in the RecordSet.
The MoveLast Method
This method must be used with a fully navigable RecordSet. It takes no parameters, and moves the curosr to the last record in the RecordSet.
The MoveNext Method
This method can be used with any RecordSet. It moves the cursor forward to the next record in the RecordSet.
The MovePrevious Method
The cursor must be fully navigable. It moves the cursor to the previous record in the RecordSet.
The NextRecordSet Method
This is used particularly when using Stored Procedures that contain multiple SQL SELECT statements. When the results are returned, executing the NextRecordSet method moves the cursor from the current one to the beginning of the next one.
The Requery Method
This method re-executes the query that created the RecordSet initially. Syntax:
RecordSet_Name.Requery([options])
Options (optional) is not something you should worry about. It is merely for optimization. The default is generally fine.
The Resync Method
The Resync method refreshes the RecordSet from the underlying database. It can be useful when you think certain records might have been changed by another user in the interim. Syntax:
RecordSet_Name.Resync([Affect records], [Resync Values])
AffectRecords is an enumerated constant found in adovbs.inc., and it can be one of 5 values:
The Seek Method
adAffectCurrent Refreshes just the current row adAffectGroup Refreshes all records matching the current "Filter" property. adAffectAll Refreshes all records in the RecordSet adResyncAllValues Refreshes all value properties, overwriting pending updates. adResyncUnderlyingValues Refreshes only the UnderLyingValue property
This method uses an Index to position the cursor at a specific record. Note: You must use a fully navigable RecordSet to use this. Syntax:
RecordSet_Name.Seek KeyValues, SeekOption
The KeyValues parameter is an array of column values to search for.
SeekOption is an enumerated constant value from adovbs.inc. It can be one of the following values
If no record is found, the cursor is positioned at EOF.
adSeekAfter Find fhe first key after the match adSeekAfterEQ Find the key equal to or just after the match adSeekBefore Find the key just before the match adSeekBeforeEQ Find the key equal to or just before the match adSeekFirstEQ Find the first key equal to the match adSeekLastEQ Find the last key equal to the match
The seek property is used in conjunction with the Index property. Example:
<%
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open "DSN=mydsn;"
q = "SELECT * FROM mytable"
Set rs = cn.Execute(q)
rs.Index = "LastName"
rs.Seek Array("Spencer"), adSeekFirstEQ
if rs.EOF then
Response.Write "No Record Found"
else
Response.Write "Record Found"
end if
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
%>
The Supports Method
Use this method to find out whether a RecordSet object supports a particular method or property. Syntax:
Boolean_Variable_Name = RecordSet_Name.Supports(CursorOptions)
The CursorOptions parameter is an enumerated constant from adovbs.inc., which can be one or more of the following (joined by the OR operator):
| adAddNew | Supports the AddNew method |
| adApproxPosition | Supports absolute positioning, via AbsolutePosition and AbsolutePage properties |
| adBookmark | Supports Bookmarks |
| adDelete | Supports deletions via the Delete method |
| adFind | Supports the Find method |
| adHoldRecords | Changes to the RecordSet will be kept if you fetch more records |
| adIndex | Supports the Index property |
| adMovePrevious | Supports the MovePrevious method |
| adNotify | Supports notifications and will return events |
| adResync | Supports the Resync method |
| adSeek | Supports the Seek method |
| adUpdate | Supports the Update method |
| adUpdateBatch | Supports the UpdateBatch method |
<%
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open "DSN=mydsn;"
q = "SELECT * FROM mytable"
Set rs = cn.Execute(q)
if rs.Supports(adIndex OR adSeek) ' ** supports both the Index method and the Seek method
rs.Index = "LastName"
rs.Seek Array("Spencer"), adSeekFirstEQ
if rs.EOF then
Response.Write "No Record Found"
else
Response.Write "Record Found"
end if
else
Response.Write "Not Supported"
end if
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
%>
The Update Method
Updates the current RecordSet with changes made. Note: this must be used with an updateable LockType. Syntax:
RecordSet_Name.Update([Fields], [Values])
The Fields and Values parameters can be single values or arrays of values. It follows the same lines of logic as the Fields and Values list of an UPDATE SQL statement. However, you can also assign the values to the fields themselves and then call the Update method without parameters as well.
The UpdateBatch Method
Writes all pending batch updates to the disk. Syntax:
RecordSet_Name.UpdateBatch([AffectRecords])
The AffectRecords (optional) parameter is one of 3 enumerated constants found in adovbs.inc.:
adAffectCurrent Refreshes just the current row adAffectGroup Refreshes all records matching the current "Filter" property. adAffectAll Refreshes all records in the RecordSet
When using the UpdateBatch method, records are cached until the method is called. You must use the adLockBatchOptimistic LockType to enable caching.