Microsoft KB Archive/174223

= How To Refresh ADO Parameters Collection for a Stored Procedure =

Article ID: 174223

Article Last Modified on 7/1/2004

-

APPLIES TO


 * Microsoft ActiveX Data Objects 1.0
 * Microsoft ActiveX Data Objects 1.5
 * Microsoft ActiveX Data Objects 2.0
 * Microsoft ActiveX Data Objects 2.1 Service Pack 2
 * Microsoft ActiveX Data Objects 2.5
 * Microsoft ActiveX Data Objects 2.6
 * Microsoft ActiveX Data Objects 2.7

-



This article was previously published under Q174223



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 needs 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.

The code below demonstrates setting up the call to the stored procedure and explicitly creating the parameter objects properties: ... Set Cmd1 = New ADODB.Command Cmd1.ActiveConnection = Conn1 Cmd1.CommandText= "sp_AdoTest"

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

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

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

Set Rs1 = Cmd1.Execute NOTE: 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. 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 is a greater performance hit than if you build the parameters yourself. This would be a good way to determine the correct parameters properties for a procedure. Use the information returned to set up the parameters in code as demonstrated above. This is demonstrated in the following code: Cmd1.ActiveConnection = Conn1 Cmd1.CommandText = "sp_AdoTest" Cmd1.CommandType = adCmdStoredProc Cmd1.Parameters.Refresh Cmd1.Parameters(1).Value = 10

Set Rs1 = Cmd1.Execute This generates exactly the same rowset, but lets ADO fill the parameters collection automatically and correctly (although this code correctly determines that the second parameter is an input parameter).

NOTE: Parameters.refresh will fail in some situations or return information that is not entirely correct. Parameters.refresh is particularly vulnerable when used on ASP pages. Please query on "parameters.refresh" to find other articles that talk about some of the problems associated with Parameter.refresh.

