The Parameters collection of a Command object represents the parameters passed
between the requester and a stored procedure. If the stored procedure returns a value, the
Parameters collection also contains an additional member (element 0) that holds the return value.
Before you can call a stored procedure that uses parameters, you must add
a Parameter object to the Parameters collection for each parameter that the stored procedure
uses. There are two methods for populating the Parameters collection with a stored procedure's
parameters:
- Explicitly create a Parameter object in code and append it to the Command object's
Parameters collection for each parameter that the stored procedure uses. Listing 9.6 gives
an example of this technique.
LISTING 9.6
POPULATING THE Parameters COLLECTION EXPLICITLY
Dim cmdPubs As ADODB.Command
Set cmdPubs = New ADODB.Command
Set cmdPubs.ActiveConnection = connPubs
cmdPubs.CommandText = "Publishers_All"
cmdPubs.CommandType = adCmdStoredProc
Dim param As ADODB.Parameter
Set param = cmdPubs.CreateParameter("Return",
_
adInteger, _
adParamReturnValue, , 0)
cmdPubs.Parameters.Append param
Set rsPubs = cmdPubs.Execute
-
Automatically populate the Parameters collection with the appropriate members by calling
the Refresh method of the Command object's Parameters collection. You must, of course,
do this only after you set the Command object's CommandType and CommandText properties for
the stored procedure that you want to call. Listing 9.7 gives an example of this technique.
LISTING 9.7
POPULATING THE Parameters COLLECTION AUTOMATICALLY WITH THE Command OBJECT'S
Refresh METHOD
Dim cmdPubs As ADODB.Command
Set cmdPubs = New ADODB.Command
Set cmdPubs.ActiveConnection = connPubs
With cmdPubs
.CommandType = adCmdStoredProc
.CommandText = "Update_Titles_Title"
'Refresh Parameters collection
.Parameters.Refresh
'and then set properties of each parameter:
'@id parameter
.Parameters("@id").Value = txtUpdateTitleID
.Parameters("@id").Direction
= adParamInput
'@title parameter
.Parameters("@title").Value
= txtUpdateTitleName
.Parameters("@title").Direction
= adParamInput
.Execute
End With
After you have populated the Parameters collection and called the stored procedure with
the Command object's Execute method, you can then check the values of any Output parameters
by checking the Value property of the appropriate Parameter objects. If the stored procedure furnishes
a return value, the return value will appear in element 0 of the Parameters collection, as
illustrated in Listing 9.8
LISTING 9.8
CALLING A STORED PROCEDURE THAT FURNISHES AN OUTPUT PARAMETER AND A RETURN VALUE
Dim cmdPubs As ADODB.Command
Set cmdPubs = New ADODB.Command
Set cmdPubs.ActiveConnection = connPubs
cmdPubs.CommandText = "Count_Titles_For_PubID"
With cmdPubs.Parameters
'append directly to parameters collection, using
'the return value of the CreateParameter method
.Append cmdPubs.CreateParameter("@PubID", _
adVarChar, _
adParamInput, _
4, _
RTrim(txtCountPubID.Text))
.Append cmdPubs.CreateParameter("@NumTitles", _
adInteger, _
adParamOutput, _
, _
0)
End With
cmdPubs.Execute
lblCountTitlesPublisher = _
cmdPubs.Parameters("@NumTitles").Value
blnSuccess = _
cmdPubs.Parameters(0).Value