OdbcCommand in the stored procedure - "Parameter not attached" error in the "Exit" parameter

Question

I am trying to execute a stored procedure (against SQL Server 2005 using the ODBC driver) and I am getting the following error:

The procedure or function "GetNodeID" expects the parameter "@ID", which was not provided.

@ID is the OUTPUT parameter for my procedure, there is an @machine input file that is specified and 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 

Below is the code that I use 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 also tried using Cmd.ExecuteScalar without success. If I break before executing the command, I see that the @machine value matters.

If I execute the procedure directly from Management Studio, it works correctly.

Any thoughts? Thanks

Answer 1

Try replacing:

 OdbcCommand Cmd = new OdbcCommand("GetNodeID", _Connection); Cmd.CommandType = CommandType.StoredProcedure; 

Through:

 OdbcCommand Cmd = new OdbcCommand("{call GetNodeID(?,?)}", _Connection); 

Additional Information:

http://support.microsoft.com/kb/310130

Answer 2

I'm not quite sure what you mean by

there is an input @machine that is specified and set to null in the stored procedure

In your proc signature, this line:

 @machine nvarchar(32) = null 

does not mean that you set @machine to null inside proc - that means you assign a default value that will be used if there is no parameter (in this case null is the value to use for the missing parameter).

@ID will be no error message about @ID if you call this stored procedure without passing any parameters at all ( @machine will not be flagged as a problem, since it has a default value). Your sample code looks good to me - are you sure that the stored procedure is not called from another place in your program (somewhere where parameters are not added)?

Answer 3

Stored procedure with input parameters and ODBC connection:

create a stored procedure:

create a procedure proc_name @ parm1 varchar (20), @ parm2 varchar (10) how to start pasting table_name (@ parm1, @ parm2) into the values; end

---

This code works in SQL Server.

  private void button1_Click(object sender, EventArgs e) { string name = txtname.Text; string num = txtnum.Text; OdbcConnection con = new OdbcConnection("dsn=naveenk_m5"); OdbcCommand cmd = new OdbcCommand("{call proc1(?,?)}",con); cmd.Parameters.Add("@parm1", OdbcType.VarChar).Value=name; cmd.Parameters.Add("@parm2", OdbcType.VarChar).Value = num; con.Open(); cmd.ExecuteNonQuery(); con.Close(); MessageBox.Show("inserted a row"); }