Databases Reference
In-Depth Information
17.4.4 The Command Object
A
Command
object represents a definition of a command that may be executed by a data
provider. We have seen an example (the
RecordsetExample
subroutine in Example 17-1)
of how a
Command
object can be used to create a recordset. The
RecordsetExample
procedure also demonstrates that a
Command
object is not always required in order to
execute a command. However, a
Command
object is required when we want to execute
the same command more than once. Also, a
Command
object is needed to pass
parameters to a query.
17.4.4.1 Command objects and connections
The ActiveConnection property is used to specify the connection over which the
command will pass. The ActiveConnection property can be set either to a text string that
describes the connection or to a
Connection
object variable that refers to a valid
connection.
It is important to note that if we want to assign a single connection to multiple commands
(at different times), a
Connection
object variable should be used. For if we use a text
string, ADO will create a new
Connection
object for each command, even if the
connection string is the same.
Setting the ActiveConnection property to
Nothing
disassociates the
Command
object
from the current connection and causes the data provider to release any associated
resources on the data source. This may or may not be required, depending on the data
provider, before associating a new
Connection
object to the command.
17.4.4.2 Properties of the Command object
Let us discuss the main properties of the
Command
object.
ActiveConnection
Sets the connection over which the command will be sent. As discussed earlier, it
can be a text string (a connection string) or a
Connection
object variable.
CommandText
Sets (or retrieves) the actual command. This is usually an SQL statement, but it
can be any string that is recognized as a command by the data provider (such as a
stored procedure call). According to the documentation, some data providers may
alter the text of a command string. We can view any changes by examining the
value of the CommandText property.