Using the Parameters Collection to Manipulate and Evaluate Parameters for Stored Procedures

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