Kluong Kluong - 10 months ago 109
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;
SqlDataAdapter da = new SqlDataAdapter(cmd.CommandText,cmd.Connection);

When I just add in the variable such as:

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

It works perfectly fine.

Answer Source

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

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?