ODBC Command Procedure Function Expects Parameters-CodePudding

The stored procedure in SQL works perfectly with the desired result.

ALTER PROCEDURE [dbo].[My_StoredProc]
    (@autoidx int,
     @r varchar(max) OUTPUT)
--with encryption
AS
BEGIN
    -- ... some code here ...
END

Trying to call the stored procedure in VS 2019 using ODBC command but I get getting an error :

Procedure or function My_StoredProc expects parameter @autoidx, which was not supplied.

My code:

string connectionString = String.Format("DSN={0};uid=my_user;pwd=my_pwd", toolStripComboBoxDSN.Text);

OdbcCommand DbCommand = new OdbcCommand("My_StoredProc", DbConnection);
DbCommand.CommandType = CommandType.StoredProcedure;

DbCommand.Parameters.AddWithValue("@autoidx",  this.AutoIndex); //this.AutoIndex has value

DbCommand.Parameters.Add("@r", OdbcType.VarChar,500);
DbCommand.Parameters["@r"].Direction = ParameterDirection.Output;

DbConnection.Open();
DbCommand.ExecuteNonQuery();

string s = (string)DbCommand.Parameters["@r"].Value;

CodePudding user response：

I had one opinion:

1.- Try to change this

DbCommand.Parameters.AddWithValue("@autoidx",  this.AutoIndex);

for this:

DbCommand.Parameters.Add("@autoidx",  SqlDbType.Int);
DbCommand.Parameters["@autoidx"].Value = this.AutoIndex;

2.- Check you DbConnection, sometimes this contain other database for example db_developer instead of db_production.

CodePudding user response：

Try to use the System.Data.SqlClient native SQL client to SQL Server, and use proper using blocks and proper parameter definition - something like this:

// standard SQL Client connection string
string connectionString = "......";

// define connection and command in proper "usinh" blocks
using (SqlConnection conn = new SqlConnection(connectionString))
using (SqlCommand cmd = new SqlCommand("dbo.My_StoredProc", conn))
{
    cmd.CommandType = CommandType.StoredProcedure;

    // define parameters properly
    cmd.Parameters.Add("@autoidx", SqlDbType.Int);
    cmd.Parameters.Add("@r", SqlDbType.VarChar, -1).Direction = ParameterDirection.Output;
    
    // set values to parametesr
    cmd.Parameters["@autoidx"] = this.AutoIndex;
    
    // open connection, execute procedure, close connection
    conn.Open();
    
    int rowsAffected = cmd.ExecuteNonQuery();

    string s = cmd.Parameters["@r"].Value?.ToString();
    
    conn.Close();
}