HOWTO: Refresh ADO Parameters for a Stored Procedure

Last reviewed: September 24, 1997
Article ID: Q174223

The information in this article applies to:

ActiveX Data Objects (ADO), versions 1.0, 1.5

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 code fragment below.

   ...
   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 will get 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 below:

   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.

Keywords          : adoall
Version           : WINDOWS:1.0,1.5
Platform          : WINDOWS
Issue type        : kbhowto

================================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: September 24, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.