存储过程中的OdbcCommand-“未提供参数"输出参数错误 [英] OdbcCommand on Stored Procedure - "Parameter not supplied" error on Output parameter
问题描述
我正在尝试执行存储过程(通过ODBC驱动程序针对SQL Server 2005),并且收到以下错误:
I'm trying to execute a stored procedure (against SQL Server 2005 through the ODBC driver) and I recieve the following error:
过程或函数'GetNodeID'需要未提供的参数'@ID'.
Procedure or Function 'GetNodeID' expects parameter '@ID', which was not supplied.
@ID是我的过程的OUTPUT参数,在存储过程中指定了一个输入@machine并将其设置为null:
@ID is the OUTPUT parameter for my procedure, there is an input @machine which is specified and is set to null in the stored procedure:
ALTER PROCEDURE [dbo].[GetNodeID]
@machine nvarchar(32) = null,
@ID int OUTPUT
AS
BEGIN
SET NOCOUNT ON;
IF EXISTS(SELECT * FROM Nodes WHERE NodeName=@machine)
BEGIN
SELECT @ID = (SELECT NodeID FROM Nodes WHERE NodeName=@machine)
END
ELSE
BEGIN
INSERT INTO Nodes (NodeName) VALUES (@machine)
SELECT @ID = (SELECT NodeID FROM Nodes WHERE NodeName=@machine)
END
END
以下是我用来设置参数和调用过程的代码:
The following is the code I'm using to set the parameters and call the procedure:
OdbcCommand Cmd = new OdbcCommand("GetNodeID", _Connection);
Cmd.CommandType = CommandType.StoredProcedure;
Cmd.Parameters.Add("@machine", OdbcType.NVarChar);
Cmd.Parameters["@machine"].Value = Environment.MachineName.ToLower();
Cmd.Parameters.Add("@ID", OdbcType.Int);
Cmd.Parameters["@ID"].Direction = ParameterDirection.Output;
Cmd.ExecuteNonQuery();
_NodeID = (int)Cmd.Parameters["@Count"].Value;
我也尝试使用Cmd.ExecuteScalar,但没有成功.如果我在执行命令之前中断了,我可以看到@machine有一个值.
I've also tried using Cmd.ExecuteScalar with no success. If I break before I execute the command, I can see that @machine has a value.
如果我直接从Management Studio执行该过程,则它可以正常工作.
If I execute the procedure directly from Management Studio, it works correctly.
有什么想法吗?谢谢
推荐答案
尝试替换:
OdbcCommand Cmd = new OdbcCommand("GetNodeID", _Connection);
Cmd.CommandType = CommandType.StoredProcedure;
使用:
OdbcCommand Cmd = new OdbcCommand("{call GetNodeID(?,?)}", _Connection);
更多信息:
http://support.microsoft.com/kb/310130
这篇关于存储过程中的OdbcCommand-“未提供参数"输出参数错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!