ADO Command Object Properties and Collections

[Previous] [Next]

Let's take a closer look at the properties and collections of the Command object.

Command Object Properties and Collections
Property or Collection Name	Data Type	Description
ActiveConnection	String or Connection	Specifies the Connection object used to communicate with your database
CommandText	String	Contains the query string or the name of the table, view, or stored procedure you want to execute
CommandTimeout	Long	Controls the number of seconds the query will run before timing out
CommandType	CommandTypeEnum	Specifies the type of Command to execute
Name	String	Contains the name of the Command object
Parameters	Collection of Parameter objects	Contains parameter information for the query
Prepared	Boolean	Specifies whether the Command will be prepared and stored in the database
Properties	Collection of Property objects	Contains dynamic properties for the query
State	Long	Indicates the current state of the Command object

ActiveConnection Property

This property is similar to the ActiveConnection property on the Recordset object (described in Chapter 4). You can set this property either to a Connection object or to a connection string. If you set the ActiveConnection property to a string, ADO will create a new Connection object and attempt to connect to the database based on this string.

CommandText Property

The CommandText property contains the query string you want to execute. You can use simple query strings such as

 SELECT CustomerID, CompanyName, BalanceDue FROM Customers WHERE CustomerID = 7 

as well as parameterized queries such as

 SELECT CustomerID, CompanyName, BalanceDue FROM Customers WHERE CustomerID = ? 

This property is possibly the heart of the Command object, yet it's one of the simplest and most straightforward properties.

CommandTimeout Property

Queries can hang for a number of reasons, including high network traffic, data-locking issues, and extreme complexity. In many cases, you'll want to cancel your query after a certain length of time. The CommandTimeout property allows you to do so.

The CommandTimeout property takes a long integer that specifies the number of seconds that ADO will wait for your query to complete before timing out and canceling the query; it defaults to 30 seconds. If you want your query to run indefinitely without timing out, set CommandTimeout to 0.

Not all queries time out the same way. A query that generates a client-side Recordset might run longer than the CommandTimeout setting. The reason is that the CommandTimeout property controls how long ADO will wait for the query to begin to return results. Once the OLE DB provider starts to return data, ADO retrieves the rest of the results of your query regardless of the CommandTimeout setting. With a server-side Recordset that uses a static cursor, you might be more likely to time out because the database's query processor might generate the results for the entire query before returning the records that ADO requested.

If the database takes more time than you've specified in the CommandTimeout property, ADO will cancel the query and generate an error. If you have enough expertise with your particular database, you should be able to test this scenario by generating a query whose results take longer to return than you've specified. For example, I'll lock data in my Microsoft SQL Server database by modifying it within a transaction, and then I'll submit a query on another connection that should retrieve that same row of data to see whether the query will time out.

CommandType Property

The CommandType property represents an attempt to simplify data access by letting you simplify your query. You can specify what type of query you're submitting to ADO by using the CommandType property. For example, you can simply supply a table name as your query string and specify a CommandType of adCmdTable. The following table shows the CommandTypeEnum values you can use with this property.

CommandTypeEnum Values
Constant	Value	Description
adCmdText	1	The query will not be modified by ADO.
adCmdTable	2	ADO will append "select * from " to the query.
adCmdStoredProc	4	ADO will format the query as a call to a stored procedure; for example: {? = CALL MyProc (?)}.
adCmdUnknown	8	Default value; ADO will try different methods of executing the query until the query succeeds.
adCmdFile	256	Indicates that the CommandText property refers to a filename. Not applicable to the Command object. Applicable to the Open method on the Recordset object.
adCmdTableDirect	512	ADO will use an optional but advanced set of OLE DB API calls to retrieve all rows and columns from the table name specified. Not applicable to the Command object. Applicable to the Open method on the Recordset object.

Wouldn't it be nice to simply supply an object name—a table name, or the name of a stored procedure or view—instead of having to type out the entire query, and to simply let ADO determine what to do with that information? Maybe, maybe not.

I talk a little bit about why I avoid using the adCmdTable constant in the discussion on the Recordset's Open method in Chapter 4.

The default value for CommandType is adCmdUnknown. What happens if you use this value with your query? As the name of the constant implies, ADO does not know what type of query you're submitting. So ADO guesses based on some internal algorithms that we won't discuss here. Let's look at an example and see how ADO handles a Command object whose CommandType is adCmdUnknown:

 Set cmdCustomers = New ADODB.Command Set cmdCustomers.ActiveConnection = cnNorthwind cmdCustomers.CommandText = "Customers" Set rsCustomers = New ADODB.Recordset rsCustomers.Open cmdCustomers 

Yes, this code will work. It will successfully retrieve the contents of the Customers table into the Recordset object. I ran similar code against SQL Server and used the SQL Server Profiler to watch the Transact-SQL commands that ADO submits. This is what I saw in the log:

 exec Customers Customers select * from Customers 

ADO had to go through three iterations of what the query could be before it found the right one. This is an example of why I avoid using the default value, adCmdUnknown, as the CommandType value.

Quite a few programmers use the adCmdStoredProc constant. It's similar to the adCmdTable constant except that it lets you specify only the stored procedure name in the CommandText property rather than the table name. However, this constant doesn't allow much control over the way ADO handles the parameters associated with the stored procedure. For example, you might want to call a stored procedure and provide some values as part of the call rather than as ADO Parameter objects. (We'll talk about the Parameter object in the second half of this chapter.)

Let's see what happens to the CommandText property for the Command object when you use the adCmdStoredProc value. In the following code, we'll examine the contents of the CommandText property after each call:

 With cmdStoredProc 'Specify that the Command object will call a stored procedure. .CommandType = adCmdStoredProc 'Specify the stored procedure name. .CommandText = "MySP" 'CommandText property now contains "{ call MySP }". 'Populate the Parameters collection. .Parameters.Append .CreateParameter("@RetVal", adInteger, _ adParamReturnValue) .Parameters.Append .CreateParameter("@Param1", adInteger, _ adParamInput) .Parameters.Append .CreateParameter("@Param2", adInteger, _ adParamInput) 'CommandText property now contains "{ ? = call MySP (?, ?) }". End With 

ADO will take the value specified in the CommandText property and format it to the ODBC standard for stored procedure calls, { call MySP }. If you populate the Parameters collection, ADO will build that information into the CommandText property. Although this is an efficient, appropriate, and impressive algorithm, I'd rather simply specify the query string the way it should be formatted. In the preceding case, I would use the following code instead:

 With cmdStoredProc 'Specify that the Command object will use a text string. .CommandType = adCmdText .CommandText = "{? = CALL MySP(?,?)}" 'Populate the Parameters collection. .Parameters.Append .CreateParameter("@RetVal", adInteger, _ adParamReturnValue) .Parameters.Append .CreateParameter("@Param1", adInteger, _ adParamInput) .Parameters.Append .CreateParameter("@Param2", adInteger, _ adParamOutput) End With 

This code specifies parameters, but the values must be provided elsewhere. However, by using adCmdText and maintaining full control over the query string that ADO will submit as we've done here, you can modify this code to supply some parameter values in line. Suppose your sample stored procedure took an input parameter followed by an output parameter. If you know the value you want to submit for the input parameter, you can build it into your code this way:

 With cmdStoredProc .CommandType = adCmdText .CommandText = "{? = CALL MySP(" & intParam1 & ",?)}" .Parameters.Append .CreateParameter("@RetVal", adInteger, _ adParamReturnValue) .Parameters.Append .CreateParameter("@Param2", adInteger, _ adParamOutput) End With 

You don't need to use a Parameter object for the input parameter if you plan to call this stored procedure only once. With the adCmdStoredProc constant, you don't have this level of control.

In short, you should avoid using adCmdUnknown (the default), adCmdStoredProc, and adCmdTable for the CommandType property.

Name Property

The Command object exposes a Name property, which stores a string. This property is set to an empty string by default. The Name property is read/write prior to setting ActiveConnection, at which point it becomes read-only.

The Name property serves two purposes. First, it helps you better trap for asynchronous events. The Command object doesn't expose events, so if you execute commands asynchronously, you might need to use events exposed by the Connection and Recordset objects to check the status of the operation. Since you'll probably execute more than one query on a particular Connection object, you need to be able to determine which query fired the event you're currently trapping.

Here's an example: The Connection object's ExecuteComplete event uses a pCommand parameter to specify which query generated the event. Rather than having to use the Microsoft Visual Basic Is operator or examine a lengthy string in the CommandText property on the pCommand parameter, you can choose to examine the Name property instead, assuming you set this property earlier in your code.

There's a second use for the Name property, which I file under "Just because you can, that doesn't mean you should." Once you set the Name property on the Command object and set the ActiveConnection property to a Connection object, you can execute the command as if it were a method on the Connection object, as shown in the following code:

 strSQL = "SELECT OrderID FROM Orders WHERE OrderDate >= ? AND" & _ " OrderDate <= ?" 'Create the Command object. Set qryOrderRange = New ADODB.Command With qryOrderRange 'Set the Name of the qryOrderRange command to OrderRange. .Name = "OrderRange" .CommandText = strSQL 'Create the parameters to insert into the query string. .Parameters.Append .CreateParameter("@Start", adDBTimeStamp, _ adParamInput) .Parameters.Append .CreateParameter("@EndDate", adDBTimeStamp, _ adParamInput) 'Set the Command object's ActiveConnection property. .ActiveConnection = cnNorthwind End With Set rsOrders = New ADODB.Recordset rsOrders.CursorLocation = adUseClient 'Use the Name of the qryOrderRange object to access the query ' through the Connection object. cnNorthwind.OrderRange #8/1/1996#, #8/31/1996#, rsOrders 

While it's a neat trick, code like this uses late binding, so it runs slower than code that uses the Execute method on the Command object or the Open method on the Recordset object.

Parameters Collection

The Command object exposes a Parameters collection, which we'll discuss a little later in this chapter.

Prepared Property

Some database systems allow you to compile a query once and execute it multiple times with different parameters. Essentially, this feature is similar to a temporary stored procedure. The Command property exposes this functionality through the Prepared property. The Prepared property stores a Boolean value and defaults to False.

To determine whether setting this property to True will improve the performance of your application, your best bet is to run your own tests. I've yet to see any definitive statistics that explain under what circumstances Prepared should be set to True. Through my own testing, I've found that using this property with SQL Server 7 actually decreases performance even when executing the same query repeatedly. Your mileage can vary.

Properties Collection

Like most ADO objects, the Command object exposes a Properties collection whose contents depend on your choice of OLE DB provider. Unlike with the Connection, Recordset, and Field objects, however, there's generally no need to use these properties with the Command object.

State Property

Just like the Connection and Recordset objects, the Command object exposes a State property. The State property takes an ObjectStateEnum type, but only two values from ObjectStateEnum apply to the Command object: adStateClosed and adStateExecuting. If you execute the command asynchronously (with either the Command.Execute or the Recordset.Open method), the State property of the Command object will return adStateExecuting while ADO awaits the results of the query. Once the Connection object's ExecuteComplete event fires, this property is reset to adStateClosed.

If you're also using the ADO Cursor Engine's asynchronous fetching feature, the Command object's State property is set to adStateClosed once the ADO Cursor Engine has retrieved the initial set of rows. I don't recommend using the Command object again until ADO has fetched all the results for the query. Wait for the Recordset object's FetchComplete event to fire, or make sure the Recordset's State property is set to 1 (adStateOpen) and not 9 (adStateOpen + adStateFetching).