Error trying to call stored procedure with prepared statement

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.