Kluong Kluong - 20 days ago 16
SQL Question

Must declare the scalar variable Error C# ASP.NET Sql Command with Select Statement

I am running into a problem where it prompts me an error that says "Must declare the scalar variable "@ID". This only happens when when I add the parameter in, but when I add the variable in, it works fine.

Down below is the code that gives me the problem:

cmd.CommandText = "Select * From Attendee WHERE ID=@ID";
cmd.Connection = conn;
cmd.Parameters.AddWithValue("@ID",ID);
conn.Open();
cmd.ExecuteNonQuery();
SqlDataAdapter da = new SqlDataAdapter(cmd.CommandText,cmd.Connection);
da.Fill(dt);
conn.Close();


When I just add in the variable such as:

cmd.CommandText = "Select * From Attendee Where ID=" + ID;


It works perfectly fine.

Answer

In the SqlDataAdapter constructor you pass the SqlCommand.CommandText.
This text (just a string) is not enough to define the parameter @ID that is defined instead in the SqlCommand.Parameters collection

You have two options

 SqlDataAdapter da = new SqlDataAdapter(cmd.CommandText,cmd.Connection);
 da.SelectCommand.Parameters.AddWithValue("@ID",ID);
 da.Fill(dt);

or just build your adapter using the command

 SqlDataAdapter da = new SqlDataAdapter(cmd);

As an added advice, I really suggest you to not use AddWithValue, but instead use the overload of Add that takes also the type for the parameter

cmd.Parameters.Add("@ID",SqlDbType.Int).Value = ID;

See Can we stop using AddWithValue already?