Must declare the scalar variable '@connection&

问题:

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");
}

回答1:

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?