Using a ReturnValue ADO Parameter Object
If you want to specify an ADO Parameter Object to hold a return value from an SQL Server Stored Procedure this parameter must be appended to the Parameters collection before any other parameters are. Eg:

<%
With objCommand
   .CommandText = "usp_ReturnValueTest"
   .Parameters.Append .CreateParameter("@Return", adInteger, adParamReturnValue, 4)
   .Parameters.Append .CreateParameter("@Name", adVarChar, adParamInput, 50, strName)
End With
%>

If append the above parameters in the reverse order (ie with the ReturnValue after the input parameter), then you will receive an error similar to:

Microsoft OLE DB Provider for SQL Server (0x80040E14)
Procedure or function usp_ReturnValueTest has too many arguments specified.

Returning output parameters and recordsets
If you execute a sproc that returns a recordset and output parameters you need to call the .Close() method of the Recordset object before you can access the values of the output parameters. The use of .GetRows() or .GetString() is thus recommended in this situation so as to avoid messy data retrieval code being interspersed with your presentation code.

<%
With objCommand
   .CommandText = "usp_RecordsetAndOutputParams"
   .Parameters.Append .CreateParameter("@FirstName", adVarChar, adParamOutput, 50)
   .Parameters.Append .CreateParameter("@LastName", adVarChar, adParamOutput, 50)
End With

Set objRS = objCommand.Execute

' Move recordset data (if any) to VBScript array
If not objRS.EOF then
   arrResults = objRS.GetRows
End If

' We now close the recordset
' see www.adopenstatic.com/resources/code/objDispose.asp
Call objDispose(objRS, True, True)

' We can now get our output parameters
strFirstName = objCommand.Parameters("@FirstName").Value
strLastName = objCommand.Parameters("@LastName").Value

' We now dispose of objCommand
Call objDispose(objCommand, False, True)
%>

Back to FAQ Listing