为什么我得到`过程或函数期望参数`？

I'm inserting a row into a SQL Server 2005 database table from Classic ASP.

我正在从Classic ASP插入一行SQL Server 2005数据库表。

I get the following error:

我收到以下错误:

Procedure or function 'replace_matl_form_insert' expects parameter '@matl_no', which was not supplied.

过程或函数'replace_matl_form_insert'需要参数'@matl_no',这是未提供的。

I am supplying the parameter. The types match, and I'm still getting that error. Here is the classic asp code which is used to insert

我正在提供参数。类型匹配,我仍然得到错误。这是用于插入的经典asp代码

     set CMDInsForm = Server.CreateObject("ADODB.Command")
     CMDInsForm.ActiveConnection = CONN
     CMDInsForm.CommandText = "dbo.replace_matl_form_insert"

     set parm = CMDInsForm.CreateParameter ("matl_no", adChar, adParamInput, 8, Trim(matl_no))
     CMDInsForm.Parameters.Append parm
     For each X in CMDInsForm.Parameters
          Response.Write err_msg & X.Name & ":" & X.Value & "<BR>"
     Next

     CMDInsForm.Execute

As you can see, I cycle through the parameters and print out their name and value. The name matl_no and the correct value are printed.

如您所见,我循环访问参数并打印出其名称和值。将打印名称matl_no和正确的值。

Here is the section of the stored procedure which retrieves it.

以下是检索它的存储过程的一部分。

ALTER PROCEDURE [dbo].[replace_matl_form_insert]
    (@matl_no char(8))
 AS [...]

I'm completely stumped. Does anybody have any suggestions on what it could be, or just take a shot in the dark? I don't care how crazy it sounds.

我完全难过了。有没有人对它可能是什么有任何建议,或者只是在黑暗中拍摄?我不在乎它有多疯狂。

3 个解决方案

#1

I think you need the "@" on the start of the parameter name:

我认为你需要在参数名称的开头加上“@”:

set parm = CMDInsForm.CreateParameter ("@matl_no", adChar, adParamInput, 8, Trim(matl_no))

EDIT
Also, try setting the CommandType to 4 (which is adCmdStoredProc):

编辑另外,尝试将CommandType设置为4(即adCmdStoredProc):

CMDInsForm.CommandType = 4

http://www.w3schools.com/ado/prop_comm_commandtype.asp

#2

I think you are missing the @ in the parameter name. Try:

我想你在参数名称中缺少@。尝试:

set parm = CMDInsForm.CreateParameter ("@matl_no", ...

Instead of :

代替 :

set parm = CMDInsForm.CreateParameter ("matl_no", ...

#3

If you do not want to send parameter to your SP (in some condition ) you can initialize a default value to your SP parameter like

如果您不想将参数发送到SP(在某些情况下),您可以将默认值初始化为SP参数,例如

ALTER PROCEDURE [dbo].[replace_matl_form_insert]
    (@matl_no char(8)=NULL)
 AS [...]

by assigning NULL value to your parameter ,you willn't get this Error

通过为参数指定NULL值,您将不会收到此错误

Procedure or function 'replace_matl_form_insert' expects parameter '@matl_no', which was not supplied.

#1