OdbcCommand on Stored Procedure - "Parameter not supplied" error on Output parameter
11,215
Try replacing :
OdbcCommand Cmd = new OdbcCommand("GetNodeID", _Connection);
Cmd.CommandType = CommandType.StoredProcedure;
With :
OdbcCommand Cmd = new OdbcCommand("{call GetNodeID(?,?)}", _Connection);
More info :
http://support.microsoft.com/kb/310130
Author by
Aaron
Updated on June 05, 2022Comments
-
Aaron almost 2 years
I'm trying to execute a stored procedure (against SQL Server 2005 through the ODBC driver) and I recieve the following error:
Procedure or Function 'GetNodeID' expects parameter '@ID', which was not supplied.
@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;
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.
If I execute the procedure directly from Management Studio, it works correctly.
Any thoughts? Thanks