The Command Object

This property contains the text of a command to be issued against a data provider.

  String = Command.CommandText
  Command.CommandText = String

This can be a SQL statement, a table name, a stored procedure name, or a provider-specific command, and the default is an empty string. For example, to set the command text to a SQL string, you can do the following:

  objCmd.CommandText = "SELECT * FROM authors " & _
                         "WHERE state = 'CA'"

You can pass parameters into a stored procedure in SQL Server a number of ways. One way is to use the parameters contained in the Parameters collection. Another way is to pass the stored procedure and its arguments as a text command:

  objCmd.CommandText = "usp_MyProcedure ('abc', 123)"
  objCmd.CommandType = adCmdStoredProc
  Set objRs = objCmd.Execute()

This method is sometimes quicker to code than using the Parameters collection, although you obviously have no way to use output parameters with this method. Alternatively, you could use the Parameters argument of the Execute method:

  objCmd.CommandText = "usp_MyProcedure"
  objCmd.CommandType = adCmdStoredProc
  Set objRs = objCmd.Execute (, Array("abc", 123))

If you set the Command object's Prepared property to True, then the command will be compiled and stored by the provider (if it supports prepared statements) before executing. This prepared statement is retained by the provider for the duration of the connection. If you are using the SQL Server provider, this may create a temporary stored procedure for you (if you have the Use Procedure for Prepare dynamic property in the Connection objects Properties collection, as discussed in Appendix C). This is particularly useful when the same command must be executed several times but with different parameters.

The Parameters collection is explained in more detail later in this chapter.

The CommandStream and CommandText properties are mutually exclusive; setting one clears the other.

This property indicates how long, in seconds, to wait while executing a command before terminating the command and generating an error. The default is 30 seconds.

  Long = Command.CommandTimeout
  Command.CommandTimeout = Long

An error will be generated if the timeout value is reached before the command completes execution, and the command will be cancelled. If you are using Visual Basic and ADO events, the ExecuteComplete event be fires when the error is generated, and you can check the pError argument to detect the error. You can also trap the error and examine the error details.

This property bears no relation to the Connection object's CommandTimeout property; it is not inherited from the connection.

This property indicates the type of the Command object.

  CommandTypeEnum = Command.CommandType
  Command.CommandType = CommandTypeEnum

The CommandTypeEnum constants are defined under the Execute method and in Appendix B.

You should use this property to optimize the processing of the command, because it informs the provider what sort of command you will execute before that command is actually performed. This allows the provider to decide what to do in advance; this often increases performance because ADO doesnt have to figure out the type of command.

The distinction between adCmdTable and adCmdTableDirect is quite subtle. Consider the following:

  objCmd.CommandText = "authors"
  objCmd.CommandType = adCmdTable

This actually sends SELECT*FROM authors to the provider. In contrast, consider this code:

  objCmd.CommandText = "authors"
  objCmd.CommandType = adCmdTableDirect

This only sends the statement authors to the provider. Some providers may not support direct table names and may require explicit SQL statements.

The performance implications of these options are discussed in more detail in Chapter 14.

This property identifies the dialect to be used for the CommandStream or CommandText properties.

  String = Command.Dialect
  Command.Dialect = String

The Dialect property is a Globally Unique Identifier (GUID) and is provider-dependent, allowing the provider to support multiple dialects. The default dialect is standard SQL, identified by the following GUID:

  {C8B521FB–5CF3–11CE–ADE5–00AA0044773D}

For the SQL XML format supported by SQL Server 2000, you would use the following GUID:

  {5D531CB2–E6Ed–11D2–B252–00C04F681B71}

For XPATH support, you should use:

  {EC2A4293–E898–11D2–B1B7–00C04F680C56}

Consult your provider documentation to find out any provider-specific dialects.

More details about XML interaction can be found in the ADO help files.

This property indicates the name of the Command object.

  String = Command.Name
  Command.Name = String

In many cases you probably won't use this property, but it could be used to uniquely identify a Command object in a collection of commands. For example, imagine an application that allows users to build up a number of commands. You could store these in a collection and use the commands'  Name property to identify them. Think about the query window in SQL Server; you could build quite a good emulation of this by using ADO to connect to multiple data sources and using a collection to store various command objects.

There is a rarely noted feature that goes like this: if you name a Command object, then associate the command with a Connection object, the Connection object inherits a method corresponding to the name of the Command. This is a dynamic method, which doesn't show up as part of the object's methods or properties within code editors such as Visual Basic.

Let's consider an example. Suppose you have two types of user, a Clerk and a Manager, and both types need to look at employee details. Managers are allowed to see the salary details, but the humble clerks are not. You have decided to use stored procedures to encapsulate all your SQL logic, so you have two possibilities. The first is to build a single SQL statement to return the employee details, and have it check whether the user is a manager or a clerk. Your user then calls a business object that fetches the data, as shown in this diagram of three-tier architecture with the logic in the server: 

Note that you now have a business rule built into the SQL. In many cases this is not a problem (and indeed could be deemed good practice), but this doesn't always fit everyones needs. You may find that you'd rather not put business rules into your SQL code. For instance, in this example, you also must work out details such as how to establish which group the user belongs to. That's not a particularly complex problem, but here's an alternative situation that keeps all the business logic in the same place-the business object-as this diagram shows: 

In this situation, the application first calls a business object, perhaps when it starts, and the business object establishes the role: clerk or manager. This sets the appropriate stored procedure and attaches the command as a method of the connection. Later, the application can simply call the business object that calls this new method. Because the method is one of two stored procedures, the correct stored procedure is run. Although this seems like more code, it actually simplifies some programming. It puts the business logic in a component where it is easily reused, and it also makes the stored procedures simpler; these will now run faster because there is no run-time decision to make.

When using this method, you must set the Name property before setting the ActiveConnection; otherwise, an error occurs .

One thing that's not documented very clearly-or very often-is that when using this method, the newly named procedure accepts the parameters of the stored procedure, and an extra argument: a recordset. For example, imagine the following stored procedure:

 CREATE PROCEDURE usp_TestProcOne
      @sAuLName varchar(40)
  AS
      SELECT * FROM authors
      WHERE au_lname = @sAuLName

You could call this using the following code:

  strConn = "Provider=SQLOLEDB; Data Source=SQL7Server; " & _
             "Initial Catalog=TestData; User Id=sa; Password="
  objConn.Open strConn
  objCmd.CommandText = "usp_TestProcOne"
  objCmd.CommandType = adCmdStoredProc
  objCmd.Name = "TestProc"
  Set objCmd.ActiveConnection = objConn
  objConn.TestProc "Ringer", objRec

This passes Ringer into the stored procedure as the parameter, and the results are returned in the recordset objRec.

Another shortcut is simply to call the stored procedure name directly against the connection:

  objConn.Open " . . ."
  objConn.usp_TestProcOne "Ringer", objRec

If ADO doesn't recognize the command, it passes the command and parameters to the provider to see if it can handle it. If the provider recognizes the command, the command is executed; otherwise, an error is generated. Note that this, of course, will result in poorer performance.

One particularly interesting feature of named commands is that a variation of it allows you to retrieve output parameters of a stored procedure, even when the provider doesnt support it. This is covered in more detail in the "Retrieving Output Parameters" section at the end of the chapter.

The NamedParameters Property

This property indicates whether parameter names should be passed to the provider.

  Boolean = Command.NamedParameters
  Command.NamedParameters = Boolean

Setting this property to True means that ADO will pass the name of each parameter to the provider, where the name will be used to match up the parameters. If the value is False (the default), parameters are interpreted in the order created. It is more efficient to add parameters by position because, when adding by name, ADO has to match the parameters to their underlying counterparts.

This property indicates whether or not to save a compiled version of a command before execution.

  Boolean = Command.Prepared
  Command.Prepared = Boolean

The default value of the property is False. You should set it to True when the command is to be repeated several times. Although the compilation process will be slower during the first command execution, subsequent executions will be quicker because the command text does not have to be parsed and the execution plan can be reused.

If youre using the SQL Server provider, and you set the Use Procedure for Prepare dynamic property of the Connection object to True, a temporary stored procedure will be created for prepared commands. If you find that you need to prepare a lot of statements, consider moving the SQL into a stored procedure, because this will be more efficient than preparing statements.

Use Procedure for Prepare is one of the provider-specific dynamic properties of the ADO Connection object. For more on ADO's dynamic properties and how to use them, see Appendix C.

If you are creating prepared commands against SQL Server and not disconnecting, you should ensure that the temporary database (tempdb) has enough space to accommodate the stored procedures.

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.

This collection contains all of the provider-specific, dynamic Property objects for a Command object:

  Properties = Command.Properties

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.

This section details the methods of the Parameter object.

This method appends data to a large or binary Parameter object.

  Parameter.AppendChunk Data

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.

This section details the properties of the Parameter object.

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.

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.

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.

This property indicates the scale of numeric values for the Parameter object.

  Byte = Parameter.NumericScale
  Parameter.NumericScale = Byte

The numeric scale indicates how many digits are to the right of the decimal point.

Both the NumericScale and Precision properties are required to process SQL data of numeric type correctly. Both Oracle and SQL Server 7.0 support numeric types, using both native OLE DB Providers and the OLE DB Provider for ODBC.

Furthermore, note that the NumericScale and Precision properties cannot be set via the Command object's CreateParameter method. If you need to set these properties (for example, in the scenario described in the previous paragraph), you must create an explicit Parameter object and set its properties before appending it to the Parameters collection.

This property indicates the degree of precision for numeric values in the Parameter object.

  Byte = Parameter.Precision
  Parameter.Precision = Byte

The precision indicates the maximum number of digits used to represent a numeric value.

The Precision property, like the NumericScale property, is required for the correct processing of SQL data of numeric type-as supported by both Oracle and SQL Server 7.0, using both native OLE DB Providers and the OLE DB Provider for ODBC. There's more about this in the previous section on the NumericScale property.

This property indicates the maximum size, in bytes or characters, of a Parameter object.

  Long = Parameter.Size
  Parameter.Size = Long

Watch out for a few things when using the Size property:

• For variable-length parameters (such as character strings or binary data), you must always set the Size so that the provider knows how much space to allocate for the parameter. If you don't specify the size, an error is generated.

• If you use the Parameters collection's Refresh method to force the provider to fill in the parameter details, the Size for variable-length parameters may be at their maximum potential size, and memory may be allocated for these parameters accordingly.

• For binary data you have to be quite specific about the size of the parameter. For example, if a SQL Server stored procedure has a parameter of type image, and you Refresh the parameters, the size of this parameter is returned as 2147483647. If you create the parameters yourself and use this size, and then use AppendChunk to add the data to the parameter, there are no problems, but creating the parameter with the actual size of the binary data doesn't work. That's because Size is the maximum size of the parameter, not its actual size.

This property indicates the data type of the Parameter object.

  DataTypeEnum = Parameter.Type
  Parameter.Type = DataTypeEnum

A full list of the DataTypeEnum values is shown in Appendix B, but the following table lists the data types used by the SQL Server and Jet providers and shows how the underlying data store's data types map to those of ADO. The empty table cells indicate that there is no direct mapping between the two database types.

There are a few interesting (and often confusing) things to note. For example, date parameters in SQL Server don't map to the obvious adDate data type, but rather to the adDBTimeStamp data type. Also, the timestamp maps to adVarBinary for SQL Server 6.5 and adBinary for SQL Server 7.0. You should also note that adInteger maps to Visual Basic's Long data type; it doesn't fit VB's Integer type. If the parameters are of the wrong type, they can cause your commands to fail.

If you wish to create your parameters by using the CreateParameter method, but you're having trouble matching data types or sizes, then the simplest fix is to temporarily call the Refresh method and examine the Parameters collection. You can do this in VBScript by looping through the collection or by using the Locals window in Visual Basic. You can then copy the values that ADO has used and amend your code accordingly. Don't forget to remove the Refresh once you've sorted out your parameters, because leaving it in will cause a performance penalty.

You can find more on data types in Appendix E.

The Value Property

This property indicates the value assigned to the Parameter object.

  Variant = Parameter.Value
  Parameter.Value = Variant

This is the default property and can be omitted if desired.

The value of a parameter can be read only once, and the recordset should be closed before you read the value (depending upon the Output Parameter Availability dynamic property of the Connection). Reading a value more than once returns an empty value. For more details on this, see the "Retrieving Output Parameters" section on parameter values at the end of this chapter.

For a parameter holding binary data, you can use the Value property to set its value instead of using the AppendChunk method. For example:

  objRec.Parameters("@Logo").Value = varChunk

A full description of using binary data appears at the end of Chapter 8.

When dealing with stored procedures or queries that accept arguments, you have three options:

• Use the Command object's Execute method, passing in the parameters as an array of values into the second argument of this method. Not all providers support this.

• Use the Command object's Execute method, passing the parameters in the Command's Parameters collection.

Use the Command object's Execute method, passing the parameter values as part of the command text. In many cases the first is acceptable (and is in fact easier to code), but the one restriction of this is that you can't return any values from your stored procedure. For example, suppose you have a stored procedure like this:

  CREATE PROCEDURE usp_AuthorsByState 
     @RequiredState   char(2),
     @ID              integer    OUTPUT
  AS
  BEGIN
      SELECT *
      FROM   authors
      WHERE  state = @RequiredState
      SELECT @ID=123
      RETURN 456
  END

This procedure does three things:

1. It returns a recordset using one of the parameters to restrict the rows that are returned.

2. It sets the output parameter to an arbitrary value (123 in this case).

3. It returns another arbitrary value (456 in this case).

Although the output value and return value are falsely constructed, it illustrates two common techniques for returning information from a stored procedure.

If you wished to call this stored procedure without using the Parameters collection, you could do so using code like this:

  objCmd.CommandText = "usp_AuthorsByState"
  objCmd.CommandType = adCmdStoredProc
  Set objRec = objCmd.Execute (, Array("CA", lngID))

This simply calls the procedure, passing in two arguments: CA to filter the recordset and lngID (a long integer) to be used as the output parameter. This works fine, because ADO builds a Parameters collection from the values you supply. However, note that the output parameter (lngID) is never populated with the output value, and there's also no way to access the return value. So although this method is extremely easy to use, it doesnt give you the functionality that the Parameters collection does.

There's a caveat to this approach: If you reference the Parameters collection before calling the Execute method, the Parameters collection is then Refreshed automatically for you. For example:

  objCmd.CommandText = "usp_AuthorsByState"
  objCmd.CommandType = adCmdStoredProc
  Print "Parameter Count = " & objCmd.Parameters.Count

During the Print statement, ADO automatically calls a Refresh and populates the Parameters collection. The key point is that there are three parameters, not two: the return value, the input parameter (the required state), and the output parameter (the ID value). This means that the next line might fail, because it doesn't use enough parameters:

  Set objRec = objCmd.Execute (, Array("CA", lngID))

However, this line works:

  Set objRec = objCmd.Execute (, Array(lngRV, "CA", lngID))

This shows that if you manually populate the Parameters collection or use the Array method, you don't need to worry about the return parameter. However, if you Refresh the Parameters collection (or if it is refreshed implicitly for you), you'll find that the return value becomes the first parameter in the collection.

Note that not all providers or versions of SQL Server may support this automatic refresh (or indeed the Refresh method itself). If using SQL Server 6.5, ensure that you have installed the latest Catalog Procedures. See the SQL Books online for more details on this.

The automatic refreshing of parameters in ADO 2.1 onward is different from what occurred in ADO 2.0. Using an explicit Refresh works for both the OLE DB Provider for ODBC and the OLE DB Provider for SQL in both versions of ADO. However, using the implicit refresh gives different results.

Ive used the following code to test the automatic refresh facility in ADO 2.0 and ADO 2.1 onward:

  objCmd.CommandText = "usp_AuthorsByState"
  objCmd.CommandType = adCmdStoredProc
  Print "Parameter Count = " & objCmd.Parameters.Count

If you are using SQL Server 6.5 (and you haven't updated your Catalogs), only the OLE DB Provider for ODBC will return the correct parameter information to the Parameters collection. If you have updated your catalogs, or if you're using SQL Server 7, then there are differences between the ways that the two versions of ADO handle the parameters. The following list shows which combinations of ADO, providers, and data stores support automatic refreshing of parameters:

Don't think that this removal of functionality is a detrimental step. In this case it is a deliberate (and documented) move to improve performance and reduce network traffic.

When using the array method in ASP scripting languages, you must make sure that any variables used for output parameters have the correct data type. When you declare variables in scripting languages, they are variants, and this may not match the correct parameter type. The output parameter must have the correct type, even though you can't actually use it. For example, in VBScript:

  Dim lngID
  Set objRec = objCmd.Execute (, Array(lngRV, "CA", lngID))

This produces an error, which indicates that a parameter object has not been supplied. That's because lngID is a variant and doesn't have a default type or value. However, this section of code works:

  Dim lngID
  lngID =
  Set objRec = objCmd.Execute (, Array(lngRV, "CA", lngID))

This forces the variant to hold the correct data type; it gives lngID a sub-type of Integer, which is compatible with the integer type declared in the stored procedure (SQL Server integers map to Longs in Visual Basic and in scripting languages).

The Parameters collection contains a Parameter object for each parameter in a stored procedure, including the return value if the procedure has one. It has only two properties and three methods.

Methods of the Parameters Collection

The Append Method

This method appends a Parameter object to the Parameters collection.

  Parameters.Append(Object)

Parameters are created by using the CreateParameter method, and you must set the Type property before calling this method. For example:

  Set objParam = objCmd.CreateParameter
  objParam.Name = "Name"
  objParam.Type = adVarChar
  objParam.Direction = adParamInput
  objParam.Size = 25
  objParam.Value = "Janine"
  objCmd.Parameters.Append objParam

Although more cumbersome to use than the Refresh method to build a parameter list, this method is much more efficient, because it minimizes the total number of calls made to the provider.

You can create a parameter with just one line by using the following:

  Set objParam = objCmd.CreateParameter("State",adChar,adParamInput,2,"CA")

You can combine the Append and CreateParameter methods into a single statement:

 objCmd.Parameters.Append _  
         objCmd.CreateParameter("State",adChar,adParamInput,2,"CA")

This method deletes a Parameter object from the Parameters collection.

  Parameters.Delete(Index)

You must use the Parameter object's index or its Name when deleting a parameter. You cannot use an object variable. For example, we can delete the parameter called ID by using this line:

  objCmd.Parameters.Delete ("ID")

Here's another example. This line deletes the first parameter in the collection:

  objCmd.Parameters.Delete (0)

This method updates the Parameter objects in the Parameters collection.

  Parameters.Refresh

This has the impact of querying the data provider for details of the parameters, such as the name, size, direction, and so on. You can then access the parameters by name or ordinal number by indexing into the collection. For example:

  objCmd.Parameters.Refresh
  objCmd.Parameters(0).Value = 1
  objCmd.Parameters("@FirstName") = "Janine"

You should use this method only when willing to accept the performance hit of the provider requerying the data source for the parameter details. This is especially important if the same set of parameters is used frequently.

You can use this method quite effectively by querying the provider for the parameters once-for example, at the start of the program. You could then copy the parameter details into a global variable (perhaps an array or a collection, or even a local, fabricated recordset), where it can be used many times during the program. The advantage with this method is that you don't have to keep repeating CreateParameter calls, and you can create a generic piece of code to run a command. In addition, you can change the stored procedure without having to change the code that runs the command. The disadvantage is, of course, the delay as the program starts while the parameters are read in. However, you may consider this delay      worthwhile, because it gives you a good chance to display that fancy splash screen you've always wanted!

If you access the Parameters collection (for example, setting the value of a parameter) without having created your own Parameters for a command, ADO will call Refresh automatically to fill the collection. Not all providers support the Refresh method.

The performance issues regarding Refresh are discussed in Chapter 14.

Properties of the Parameters Collection

The Count Property

This property indicates the number of Parameter objects in the Parameters collection.

  Long = Parameters.Count

You can use the Visual Basic or VBScript For Each...Next command to iterate through the parameters collection without using the Count property. For languages that do not support enumeration of collections, you can use this property in a loop.

This property allows indexing into the Parameters collection to reference a specific Parameter object.

  Parameter = Parameters.Item(Index)

This is the default property and can be omitted. For example, the following lines are identical:

  objCmd.Parameters.Item(1)
  objCmd.Parameters(1)
  objCmd.Parameters("@FirstName")

Retrieving Output Parameters

The ability to return information from stored procedures in parameters is extremely useful, but you should be aware of some points. The first is whether output parameters are supported at all by the provider-Access doesn't support them. The second is at what stage the output parameters are available, and for this there are two choices:

1. The parameter is available as soon as the command has been executed.

2. The parameter is available only after the recordset has been closed.

This latter option is the one you must watch for, because in that case, ADO will read the parameter values from the provider only once. This means that if you read the parameter before closing the recordset, then close it, and then try to read the parameter value again, the value may not be available.

You can check to see which of the modes your provider supports by examining the Output Parameter Availability dynamic property for the connection. This will return one of the three DBPROPVAL_OA constant values:

• DBPROPVAL_OA_ATEXECUTE indicates that the parameters are available after the command has been executed. This has a value of 2.

• DBPROPVAL_OA_ATROWRELEASE indicates that the parameters are available after the recordset has been closed. This has a value of 4, and is the option supported by SQL Server.

• DBPROPVAL_OA_NOTSUPPORTED indicates that output parameters are not supported. This has a value of 1.

You could check these values with code like this:

 Set objCmd.ActiveConnection = objConn
  objCmd.CommandText = "usp_ProcedureWithOutputParam"
  objCmd.CommandType = adCmdStoredProc
  objCmd.Parameters.Append objCmd.CreateParameter("RETURN_VALUE", _
            adInteger, adParamReturnValue)
  Set objRec = objCmd.Execute
  intParamAvail = objConn.Properties("Output Parameter Availability")
  If intParamAvail = DBPROPVAL_OA_ATROWRELEASE Then
      ' parameter not available until recordset is closed
      objRec.Close
  End If
  intRV = objCmd.Parameters("ReturnValue")

This isnt a problem with commands that do not return recordsets, because there is no recordset to close.

There are two solutions to the problem of parameters not being available until the recordset is closed. The first is simply a matter of using client-side cursors, which allows the parameters to be immediately available.

The second method involves the naming of stored procedures by using the Command object's Name property. You've already seen that code similar to this is allowed:

  strConn = "Provider=SQLOLEDB; Data Source=SQL7Server; " & _
            "Initial Catalog=TestData; User Id=sa; Password="
  objConn.Open strConn
  objCmd.CommandText = "usp_AStoredProcedure"
  objCmd.CommandType = adCmdStoredProc
  Set objParam = objCmd.CreateParameter("ID", adInteger,
                         adParamInput, 8, 123)
  objCmd.Parameters.Append objParam
  Set objParam = _
         objCmd.CreateParameter("Name", adVarChar, _
                   adParamInput, 25, "Janine")
  objCmd.Parameters.Append objParam
  objCmd.Name = "StoredProcedureName"
  Set objCmd.ActiveConnection = objConn
  objConn.StoredProcedureName

This was never well documented, but what's even less well documented is that you can pass the arguments to the stored procedure and a recordset object like so:

  objConn.StoredProcedureName Argument1, Argument2, objRec

The arguments are accepted by the stored procedure in the normal way, and the recordset object is filled accordingly. As soon as this command is executed, you can access the return and output parameters even though the recordset is still open.

As a word of warning, you shouldn't really rely on this procedure; because it's not documented, there's no way of telling whether this will continue to work in future versions of ADO.