sql - Error trying to call stored procedure with prepared statement

Question

I'm trying to use a prepared statement to call a stored procedure (using ADODB with classic ASP), but when I set CommandType I get the following error:

ADODB.Command error '800a0bb9'

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

I have the following code:

With Server.CreateObject("ADODB.Command")
    .ActiveConnection = db 'this is initialized prior
    .CommandType = adCmdStoredProc
    .CommandText = "procName"
End With

The prepared statement name is correct (I'm able to call it just by executing the string), and if I leave out the .CommandType and try calling .Execute, I get an error specifying:

Procedure or function 'procName' expects parameter '@ParamName', which was not supplied.

Even if I leave out the CommandType, I have no idea how to actually add the parameter (something along the following lines just results in the original error about arguments of the wrong type):

.Parameters.Append .CreateParameter("@ParamName",adVarChar,adParamInput,50,param)

I've also tried the following and got an error "Item cannot be found in the collection corresponding to the requested name or ordinal."

.Parameters.Refresh
.Parameters(0) = param

I've looked at several examples of how to call stored procedures using prepared statements, and it looks like I'm using the right syntax, but anything I try seems to result in some kind of error. Any help would be greatly appreciated.

Answer 1

You want something like this (untested)

Dim cmd, rs, ars, conn

Set cmd = Server.CreateObject("ADODB.Command")

With cmd
  'Assuming passing connection string if passing ADODB.Connection object
  'make sure you use Set .ActiveConnection = conn also conn.Open should
  'have been already called.
  .ActiveConnection = conn
  'adCmdStoredProc is Constant value for 4 (include adovbs or 
  'set typelib in global.asa)
  .CommandType = adCmdStoredProc
  .CommandText = "dbo.procName"
  'Define parameters in ordinal order to avoid errors
  Call .Parameters.Append(.CreateParameter("@ParamName", adVarChar, adParamInput, 50))

  'Set values using parameter friendly name
  .Parameters("@ParamName").Value = param

  'Are you returning a recordset?
  Set rs = .Execute()
  'Populate array with data from recordset
  If Not rs.EOF Then ars = rs.GetRows()
  Call rs.Close()
  Set rs = Nothing
End With
Set cmd = Nothing

It is important to remember that the friendly name (as I rule I tend to match my parameter names in my stored procedure to my friendly names in ADO) you give your parameter means nothing to the stored procedure as ADO passes the parameters ordinally and nothing more, the fact you get the error;

Procedure or function 'procName' expects parameter '@ParamName', which was not supplied.

Suggests that the stored procedure is expecting your @ParamName parameter (defined in your stored procedure) value to be passed from ADO in a different ordinal position, which usually means you have not defined all your parameters or passed all the parameter values in the position they are expected.

You can also do a shortened version if your confident of your ordinal positioning and parameter requirements

With cmd
  .ActiveConnection = conn
  .CommandType = adCmdStoredProc
  .CommandText = "dbo.procName"

  'Pass parameters as array following ordinal position.
  Set rs = .Execute(, Array(param))
  'Populate array with data from recordset
  If Not rs.EOF Then ars = rs.GetRows()
  Call rs.Close()
  Set rs = Nothing
End With
Set cmd = Nothing

Working with a 2-dimensional array is easy and negates the overhead of working directly with a ADODB.Recordset.

Dim row, rows

If IsArray(ars) Then
  rows = UBound(ars, 2)
  For row = 0 To rows
    Response.Write "First column from row " & row & " = " & ars(0, row) & "<br />"
  Next
Else
  Response.Write "No data to return"
End If

---

Links

• Using METADATA to Import DLL Constants - If your having trouble with the ADO constants (adCmdStoredProc etc.) this will fix it for you.