How To Refresh ADO Parameters Collection for a Stored Procedure

This article was previously published under Q174223
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
This article demonstrates two techniques for filling the ADO Parameterscollection for a command object using a Stored Procedure.
Consider this stored procedure, which uses input, output, and returnparameters:
create proc sp_AdoTest( @InParam int, @OutParam int OUTPUT )asSELECT @OutParam = @InParam + 10SELECT * FROM AuthorsWHERE 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 theorder 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.CommandCmd1.ActiveConnection = Conn1Cmd1.CommandText= "sp_AdoTest"Set Param1 = Cmd1.CreateParameter(, adInteger, adParamReturnValue)Cmd1.Parameters.Append Param1Set Param2 = Cmd1.CreateParameter(, adInteger, adParamInput)Param2.Value = 10Cmd1.Parameters.Append Param2Set Param3 = Cmd1.CreateParameter(, adInteger, adParamOutput)Cmd1.Parameters.Append Param3Set Rs1 = Cmd1.Execute()				
NOTE: This technique is hazardous in that if the stored procedure changes theorder or direction of parameters, this code has to be changed. There is amuch smaller alternative, which gets the same results. By just listingthe name of the stored procedure for CommandText and refreshing theParameters Collection, ADO automatically fills the Parameters Collection,one for each parameter required by the stored procedure. However, one potential drawback to thismethod is the overhead involved. It does require a round trip to the server toget the parameter information, and thus there is a greater performance hitthan 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 isdemonstrated in the following code:
Cmd1.ActiveConnection = Conn1Cmd1.CommandText = "sp_AdoTest"Cmd1.CommandType = adCmdStoredProcCmd1.Parameters.RefreshCmd1.Parameters(1).Value = 10Set Rs1 = Cmd1.Execute()				
This generates exactly the same rowset, but lets ADO fill the parameterscollection automatically and correctly (although this code correctly determines that thesecond 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.
For additional information%1, click the article number%2 below to view the article%2 in the Microsoft Knowledge Base:
225897 ACC2000: How to Use Parameters with ActiveX Data Objects (ADO) and Jet

رقم الموضوع: 174223 - آخر مراجعة: 07/01/2004 20:48:35 - المراجعة: 1.1

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

  • kbdatabase kbhowto KB174223