HOWTO: Refresh ADO Parameters Collection for a Stored Procedure

ID: Q174223

The information in this article applies to:

ActiveX Data Objects (ADO), versions 1.0, 1.5, 2.0

SUMMARY

This article demonstrates two techniques for filling the ADO Parameters collection for a command object using a Stored Procedure.

MORE INFORMATION

Consider this stored procedure, which uses input, output, and return parameters:


create proc sp_AdoTest( @InParam int, @OutParam int OUTPUT )
as
SELECT @OutParam = @InParam + 10
SELECT * FROM Authors
WHERE State <> 'CA'
RETURN @OutParam +10

The ADO Parameters collection will need one object for each parameter, and more importantly, the direction property of each parameter must match the order of the parameters in the calling syntax. Typically, the syntax for invoking this procedure through ODBC would be as follows:


{call ?=sp_AdoTest(?, ?)}

Using this syntax in the Command object's CommandText property forces you to create each parameter individually, as shown in the following code fragment:


...
Set Cmd1 = New ADODB.Command
Cmd1.ActiveConnection = Conn1
Cmd1.CommandText      = "{call ?=sp_AdoTest(?, ?)}"

Set Param1 = Cmd1.CreateParameter(, adInteger, adParamReturnValue)
Cmd1.Parameters.Append Param1

Set Param2 = Cmd1.CreateParameter(, adInteger, adParamInput)
Cmd1.Parameters(1).Value = 10
Param2.Value = 10
Cmd1.Parameters.Append Param2

Set Param3 = Cmd1.CreateParameter(, adInteger, adParamOutput)
Cmd1.Parameters.Append Param3

Set Rs1 = Cmd1.Execute()

This technique is hazardous in that if the stored procedure changes the order or direction of parameters, this code has to be changed. There is a much smaller alternative, which gets the same results. By just listing the name of the stored procedure for CommandText and refreshing the Parameters Collection, ADO automatically fills the Parameters Collection, one for each parameter required by the stored procedure. This is demonstrated in this code:


Cmd1.ActiveConnection = Conn1
Cmd1.CommandText = "sp_AdoTest"
Cmd1.CommandType = adCmdStoredProc
Cmd1.Parameters.Refresh
Cmd1.Parameters(1).Value = 10

Set Rs1 = Cmd1.Execute()

Note that the Parameters Refresh queries the server and actually replaces the value for Cmd1.CommandText to the correct value -- that is, "{call ?=sp_AdoTest(?, ?)}" -- in addition to correctly filling the parameters collection.

This generates exactly the same rowset, but lets ADO fill the parameters collection automatically and correctly (although this code does assume the second parameter is an input parameter). However, one potential drawback to this method is the overhead involved. It does require a round trip to the server to get the parameter information, and thus there will be a greater performance hit than if you build the parameters yourself.

Additional query words: kbdse


Keywords          : kbADO150 kbADO200 kbDatabase kbProvider kbVBp500 kbVBp600 
Version           : WINDOWS:1.0,1.5,2.0
Platform          : WINDOWS 
Issue type        : kbhowto

Last Reviewed: February 23, 1999