错误试图调用与prepared声明存储过程 [英] Error trying to call stored procedure with prepared statement

问题描述

我尝试使用prepared语句调用（使用ADODB经典ASP）的存储过程，但是当我设置的CommandType我收到以下错误：

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错误'800a0bb9

ADODB.Command error '800a0bb9'

参数的错误类型，超出可接受的范围，或与另一个冲突。

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

我有以下的code：

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

在prepared语句名是正确的（我能只通过执行字符串来称呼它），如果我离开了.CommandType和尝试调用.Execute，我得到一个错误指定：

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:

过程或函数'PROCNAME'需要参数'@ParamName，这是没有提供。

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

即使我离开了的CommandType，我不知道如何真正添加参数（大意如下的东西才导致了原来的错误有关错误类型的参数）：

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

我看的如何调用使用prepared语句存储过程的几个例子，它看起来像我使用的是正确的语法，但任何事情我尝试似乎产生了某种错误。任何帮助将大大AP preciated。

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.

推荐答案

您想是这样的（未经测试）

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

重要的是要记住的友好名称（如我治我倾向于在我的存储过程，我在ADO友好的名字与我的参数名称），你给你的参数没有任何意义的存储步骤，ADO通过序数型标尺的参数，仅此而已，你会得到错误的事实;

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;

过程或函数'PROCNAME'需要参数'@ParamName，这是没有提供。

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

表明存储过程期待您@ParamName参数（在存储过程中定义）值从ADO在不同的顺序位置，这通常意味着你还没有定义的所有参数或传递所有参数值传递的位置，他们的预期。

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

以2维阵列的工作是容易的，否定直接与ADODB.Recordset工作的开销

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

---

链接

• 使用元数据导入DLL常量 - 如果您使用ADO有麻烦常数（ adCmdStoredProc 等），这将解决它。

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

这篇关于错误试图调用与prepared声明存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！