尝试使用准备好的语句调用存储过程时出错 [英] Error trying to call stored procedure with prepared statement

问题描述

我正在尝试使用准备好的语句来调用存储过程(在经典 ASP 中使用 ADODB)，但是当我设置 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.命令错误'800a0bb9'

ADODB.Command error '800a0bb9'

参数类型错误、超出可接受范围或相互冲突.

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

我有以下代码:

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

准备好的语句名称是正确的(我可以通过执行字符串来调用它)，如果我省略 .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

我查看了几个关于如何使用准备好的语句调用存储过程的示例，看起来我使用了正确的语法，但我尝试的任何操作似乎都会导致某种错误.任何帮助将不胜感激.

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

处理二维数组很容易，而且不需要直接处理 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

<小时>

链接

• 使用 METADATA 导入 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.

这篇关于尝试使用准备好的语句调用存储过程时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！