Must declare the scalar variable '@connection' error

Question

Below is the code for which am getting

must declare scalar variable @connection

error. I don't know where I am going wrong. Please guide

protected void LinkButton1_Click(object sender, EventArgs e)
{
    string connection = Drpconn.SelectedItem.Text;
    using (OdbcConnection con = new OdbcConnection("DSN=Sqltesting;UID=user1;PWD=test@123;Integrated Security=no;"))
    {

        using (OdbcCommand cmd = new OdbcCommand("INSERT INTO TblConfigure(Connection,Server,DbName,UserID,Password,Connection_Name,Port,Service_ID) VALUES (@Connection, @Server , @DbName,@UserID,@Password,@ConnectionName,@Port,@ServiceID)", con))
        {
            con.Open();

            cmd.Parameters.AddWithValue("@Connection", connection);
            cmd.Parameters.AddWithValue("@Server", TxtServer.Text);
            cmd.Parameters.AddWithValue("@DbName", DrpDbName.SelectedItem.Text);
            cmd.Parameters.AddWithValue("@UserID", TxtUsr.Text);
            cmd.Parameters.AddWithValue("@Password", TxtPass.Text);
            cmd.Parameters.AddWithValue("@ConnectionName", Txtconnname.Text);
            cmd.Parameters.AddWithValue("@Port", TxtPort.Text);
            cmd.Parameters.AddWithValue("@ServiceID", TxtService.Text);

            cmd.ExecuteNonQuery();
        }
    } // closes the connection 
    Response.Redirect("LoginPL.aspx");
}

Rewrite your commandtext to _INSERT INTO (.....) VALUES(?,?,?,?,?,?,?,?)_ Odbc doesn't support named parameters — Steve, Apr 27 '17 at 07:22
Read docs here: https://msdn.microsoft.com/en-us/library/system.data.odbc.odbcparameter.aspx, it should use placeholder "?" instead of using name parameters. — Tetsuya Yamamoto, Apr 27 '17 at 07:30
Not the cause of your problem, but you should [stop using AddWithValue](http://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already) — Bill Tür stands with Ukraine, Apr 27 '17 at 07:38

Steve · Accepted Answer · 2017-04-27T07:39:50.127

You need to rewrite your command text to follow the guidelines for ODBC parameters. With this provider you cannot supply the command text with embedded NAMED placeholders for your parameters.
You provide this text with just a question mark for the parameter.

When CommandType is set to Text, the .NET Framework Data Provider for ODBC does not support passing named parameters to an SQL statement or to a stored procedure called by an OdbcCommand. In either of these cases, use the question mark (?) placeholder

Also when you add the parameters to the command Parameters collection you should provide them in the exact order expected by the INSERT fields. (But this is already correct in your current code)

  string cmdText = @"INTO TblConfigure
            (Connection,Server,DbName,UserID,
             Password,Connection_Name,Port,Service_ID) 
             VALUES (?,?,?,?,?,?,?,?)";
  using (OdbcCommand cmd = new OdbcCommand(cmdText, con))
  {
        con.Open();
        cmd.Parameters.AddWithValue("@Connection", connection);
        .....

A final note. Beware of AddWithValue. It is an handy shortcut, but in certain circumstances it bites you. See Can we stop using AddWithValueAlready?

Must declare the scalar variable '@connection' error

1 Answers1

Linked