System.Data.SqlClient.SqlException: 'Must declare the scalar variable "@SSN"

Question

I am developing a Windows Forms application in C#. I have always encryption columns in SQL Server.

My goal is to pull data from the datagridview in the form and display data.

I want to pull the data with the where operator and display it in the datagridview, but I am getting the following error. Is there any way to do this?

I would be glad if you help!!

Error

System.Data.SqlClient.SqlException: 'Must declare the scalar variable "@SSN".

Code:

private void btnSearch_Click(object sender, EventArgs e)
{
    con = new SqlConnection("Data Source = " + IP + "; Initial Catalog = " + db + ";  Persist Security Info = False; User ID = " + username + "; Password = " + password + ";Column Encryption Setting = Enabled;");

    using (SqlCommand cmd = con.CreateCommand())
    {
        cmd.CommandText = @"SELECT* FROM AE WHERE TEST_TYPE = @SSN";

        SqlParameter paramSSN = cmd.CreateParameter();
        paramSSN.ParameterName = @"@SSN";
        //paramSSN.ParameterName = "@SSN";
        paramSSN.DbType = DbType.AnsiStringFixedLength;
        paramSSN.Direction = ParameterDirection.Input;
        paramSSN.Value = "'INITIAL_TEST'";
        paramSSN.Size = 18;

        DataSet data_set = new DataSet(cmd.CommandText);
        dataAdapter = new SqlDataAdapter(cmd.CommandText,con);

        SqlCommandBuilder commandbuild = new SqlCommandBuilder(dataAdapter);
        dataAdapter.Fill(data_set);

        dataGridView1.DataSource = data_set.Tables[0].DefaultView;
        int rowCount = data_set.Tables[0].Rows.Count;
        label6.Text = rowCount.ToString();//Total record 
        con.Close();
    }
}

You need to add the parameter to the command, so it's easier to just do `cmd.Parameters.Add("@SSN", SqlDbType.VarChar, 18).Value = "INITIAL_TEST"`. You probably want `"INITIAL_TEST"` with no single quotes. Side note: you need a `using` on the `con` and `dataAdapter` objects — Charlieface, Oct 08 '21 at 12:22
You did not defined `@SSN` variable in sqlCommand and just used in in your C# command. I think you wanted to use it as input parameter but here sqlCommand treat it like a variable not input. You have to add some value to it. It is not defined (`Declare @SSN int`) and set some value. — Reza Akraminejad, Oct 08 '21 at 12:28
@Charlieface I tried what you said. Problem solved. Thank you. — Rba Egn, Oct 12 '21 at 13:42

score 0 · Answer 1 · answered Oct 12 '21 at 13:45

When I changed my code like this, the error went away.I found the solution.

        ConnectionString();
        using (SqlCommand cmd = con.CreateCommand())
        {
            cmd.CommandText = @"SELECT* FROM AE WHERE "+comboBox1.Text+" = 
            @SSN";

            SqlParameter paramSSN = cmd.CreateParameter();
            DataSet data_set = new DataSet(cmd.CommandText);
            dataAdapter = new SqlDataAdapter(cmd.CommandText,con);
            dataAdapter.SelectCommand.Parameters.Add("@SSN", 
            SqlDbType.VarChar,18).Value = textBox2.Text;

            SqlCommandBuilder commandbuild = new SqlCommandBuilder(dataAdapter);
            dataAdapter.Fill(data_set);
            dataGridView1.DataSource = data_set.Tables[0].DefaultView;
            int rowCount = data_set.Tables[0].Rows.Count;
            label6.Text = rowCount.ToString();//Total record 
            con.Close();
        }

Please consider adding explanation to your answer so everyone can learn from it — qki, Oct 12 '21 at 14:01

System.Data.SqlClient.SqlException: 'Must declare the scalar variable "@SSN"

1 Answers1