The Command Object - Methods of the Parameters Collection
(Page 7 of 8 )
The Append Method
This method appends a Parameter object to the Parameters collection.
Parameters.Append(Object)
Description Object
Parameter Type Parameter Parameter object to be appended to the collection
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")
The Delete Method This method deletes a Parameter object from the Parameters collection.
Parameters.Delete(Index)
Description Index Variant Parameter Type Number or name of the parameter object to be removed from the collection
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)
The Refresh Method 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.
The Item Property This property allows indexing into the Parameters collection to reference a specific Parameter object.
Parameter = Parameters.Item(Index)
Name Description Index Variant Type Number or name of the parameter within the collection
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:
- The parameter is available as soon as the command has been executed.
- 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.
Next: Output Parameters Without Closing Recordsets >>
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.
|
|
