The Command Object - The State Property
(Page 4 of 8 )
This property describes whether the Command object is open, closed, or currently processing a command.
Command.State = ObjectStateEnum
State can be one of the following ObjectStateEnum constants:
- adStateClosed indicates that the command is closed.
- adStateOpen indicates that the command is open.
- adStateExecuting indicates that the command is executing.
- adStateFetching indicates that the command is fetching records.
- adStateConnecting indicates that the command is connecting to the provider.
Using the State property allows you to detect the current state of the command. For example, to detect if the Command is still executing an asynchronous query, you could use this line:
If objCmd.State = adStateExecuting Then
Print "Command is still executing"
End If
Collections of the Command Object
The Parameters Collection
This collection contains all the Parameter objects for a Command object.
Parameters = Command.Parameters
The Parameters collection is most often used when passing arguments to stored procedures or queries. Parameter objects are appended to this collection by use of the Append method:
Set objParam = objCmd.CreateParameter("ID", adInteger, _
adParamInput, 8, 123)
objCmd.Append objParam
You can iterate through the objects in this collection in Visual Basic or VBScript by using the For Each...Next command. For example:
For Each objParam In objCmd.Parameters
Print objParam.Name
Next
If your provider is capable of describing the parameters of a stored procedure or query, then you can use the Refresh method to have the provider fill in the Parameters collection for you:
objCmd.Parameters.Refresh
The Parameters collection is discussed more fully in "The Parameters Collection" section later in this chapter.
Other collections, and their methods and properties, are covered in more detail in Chapter 8.
The Properties Collection This collection contains all of the provider-specific, dynamic Property objects for a Command object:
Properties = Command.Properties
The Parameter Object The Parameter object comprises all of the information for a single parameter to be used in a stored procedure or query, and is really used only in conjunction with the Command object and its associated Parameters collection.
Methods of the Parameter Object This section details the methods of the Parameter object.
The AppendChunk Method This method appends data to a large or binary Parameter object.
Parameter.AppendChunk Data
| Parameter | Type | Description |
| Data | Variant | Data to be appended to the parameter |
The first AppendChunk call after editing the parameter details writes data to the parameter, overwriting any existing data. Subsequent calls add to existing data. Although the documentation states that a parameter must support long data for this method to work, this doesn't appear to be compulsory for SQL Server. You can check the Attributes property (see the following section) to see if it contains adParamLong, which will indicate if it supports long data.
This method is most often used when storing images in tables. A full discussion of using images with AppendChunk appears at the end of Chapter 8.
The Parameter objects AppendChunk method is functionally equivalent to the AppendChunk method of the Field object, and it works the same way. The only difference is that you are dealing with a different base object.
Properties of the Parameter Object This section details the properties of the Parameter object.
The Attributes Property This property indicates one or more characteristics of a Parameter object.
ParameterAttributesEnum = Parameter.Attributes
Parameter.Attributes = ParameterAttributesEnum
This can be one or more of these ParameterAttributesEnum values:
- adParamSigned indicates that the parameter will accept signed values. This is the default.
- adParamNullable indicates that the parameter will accept null values.
- adParamLong indicates that the parameter accepts long data, such as binary data.
You can combine these values by using a logical OR statement:
objParam.Attributes = adParamNullable OR adParamLong
Setting adParamLong doesn't appear to be compulsory for binary data. A SQL Server stored procedure with a parameter of type image will accept long data without this set.
The Direction Property This property indicates whether the Parameter object represents an input parameter, an output parameter, or an input/output parameter, or if the parameter is a return value from a stored procedure.
ParameterDirectionEnum = Parameter.Direction
Parameter.Direction = ParameterDirectionEnum
ParameterDirectionEnum can be one of the following values:
- adParamUnknown indicates that the direction of the parameter is not known.
- adParamInput indicates that the parameter is to pass information to the stored procedure or query.
- adParamOutput indicates that the parameter is to receive information from the stored procedure or query.
- adParamInputOutput indicates that the parameter can be used to pass information to, and return information from, a stored procedure or query.
- adParamReturnValue indicates that the parameter will contain the return value of the stored procedure or query.
In a SQL Server stored procedure, you would declare an output parameter by appending OUTPUT to the parameter declaration. For example, the following SQL code creates a stored procedure with input and output parameters and return values:
CREATE PROCEDURE usp_GetValues
@PubID char(4), –– indicates an input parameter
@Value integer OUTPUT –– indicates an output parameter
AS
BEGIN
. . . .
RETURN 123 –– indicates the return value
The return value is always the first parameter (index position zero) in the Parameters collection and is named RETURN_VALUE. If you're creating parameters, you must create the return value first:
objCmd.CreateParameter "RETURN_VALUE", adVarInteger, _
adParamReturnValue, 8, lngRetVal
The return value should be declared as a long integer. If you use Refresh, then a return value parameter is always created, irrespective of whether the stored procedure returns a value with the RETURN statement.
You can also set this property by using the Direction argument of the Command object's CreateParameter method.
Microsoft Access does not support output parameters or return values. |
The Name Property This property indicates the Parameter object's name.
String = Parameter.Name
Parameter.Name = String
This just identifies the parameter name within the Parameters collection, and doesn't have to be the same as the parameters name as defined in the stored procedure or query--but it makes sense to keep these the same. This makes the code more readable and easier to maintain.
You can also set this property by using the Name argument of the Command object's CreateParameter method.
Once the Parameter object has been appended to the Parameters collection, this property becomes read-only, because its name becomes its key in the Parameters collection.
Next: The NumericScale Property >>
More Database Articles
More By Apress Publishing
|
 This article is excerpted from ADO Programmer's Reference by David Sussman (Apress, 2004; ISBN: 1590593421). Check it out at your favorite bookstore. Buy this book now.
|
|
