SQL Error Must declare the scalar variable

Question

        private void FillInvoiceList()
    {
        DataTable distinctInvoice = new DataTable();
        using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["APOS_CONNECTION_STRING"].ConnectionString))
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand("Select distinct svc_tag from data where rep_name = @value");
            cmd.Parameters.AddWithValue("@value", this.DropDownList1.SelectedItem.Text);
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(cmd.CommandText, conn.ConnectionString);
            sqlDataAdapter.Fill(distinctInvoice);
        }
        foreach (DataRow row in distinctInvoice.Rows)
        {
            this.ListBox1.Items.Add(row["svc_tag_dim_invoice_num"].ToString());
        }
    }

I have this code and I get this error when I call the Fill(DistinctInvoice)

Must declare the scalar variable "@value"

My FillInvoiceList() Method is being called from a SelectedIndexChanged event from the DropDownList1. The value of DropDownList1.SelectedItem.Text seems to be correct.

Thanks for any help.

Answer 1

The error is here :

SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(cmd.CommandText, conn.ConnectionString);

You're setting the SQLDataAdapter to use the original CommandText, not the SQLCommand itself. Change it to :

SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(cmd, conn.ConnectionString);

Answer 2

SqlCommand cmd = new SqlCommand("Select distinct svc_tag from data where rep_name = @value");

must be:

SqlCommand cmd = new SqlCommand("Select distinct svc_tag from data where rep_name = @value", conn);

and then you can create the sql data adapter:

SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(cmd);

That should do the trick