CODEWITHSUNDEEP
c#sqlasp.net
Kluong
Kluong

Reputation: 302

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.

Upvotes: 1

Views: 1155

Answers (1)

Steve
Steve

Reputation: 216303

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?

Upvotes: 4

Related Questions