Why would changing the parameter name in a parameterized query cause an SqlException to be thrown?

Question

My Original query used @WF_ID as the parameter name and would execute without any problems. I am lazy though and WF_ was to much typing so I renamed the parameter to @ID but now when executed an SQLException is thrown. The exact message is "Invalid column name '_Page'"

Here are the two versions of the query and corresponding code. As you can see the only thing that has changed is the parameter name.

SQL_Connection.Open();
SQL_Command.Connection = SQL_Connection;
SqlParameter param = new SqlParameter("@WF_ID", SqlDbType.Int);
param.Value = WF_ID;
SQL_Command.Parameters.Add(param);
SQL_Command.CommandText = "SELECT COUNT(*) AS MyCount from members WHERE ID = @WF_ID";
int numRows = Convert.ToInt32(SQL_Command.ExecuteScalar().ToString());
SQL_Command.Parameters.Clear();

Version 2.0 the broken version

 SQL_Connection.Open();
 SQL_Command.Connection = SQL_Connection;
 SqlParameter param = new SqlParameter("@ID", SqlDbType.Int);
 param.Value = WF_ID;
 SQL_Command.Parameters.Add(param);
 SQL_Command.CommandText = "SELECT COUNT(*) AS MyCount from members WHERE ID = @ID";
 int numRows = Convert.ToInt32(SQL_Command.ExecuteScalar().ToString());
 SQL_Command.Parameters.Clear();

My Initial thought was a parameter name cannot be the same as one of the column names...but I can't find anything that explicitly says that.

Looking at this StackOverflow topic it would seem that you couldn't pass a column name via parameters. Can I pass column name as input parameter in SQL stored Procedure

To text your theory rename it to something other than the two names already used and see if it works or not. — Dmitriy Khaykin, Feb 20 '14 at 17:46

score 7 · Accepted Answer · edited Jun 24 '21 at 08:45

7

"SELECT COUNT(*) AS MyCount from members WHERE ID = " + @WF_ID;

This is not a parameterized query at all. You just concatenated the value to your SQL string.

"SELECT COUNT(*) AS MyCount from members WHERE ID = @WF_ID";

This would make it use your parameter.

edited Jun 24 '21 at 08:45

Wai Ha Lee

8,173
68
59
86

answered Feb 20 '14 at 17:48

nvoigt

68,786
25
88
134

My bad. I had been playing around with the code trying different things to figure this out and forgot to change it back when making the post. – TheDude Feb 20 '14 at 18:22

Why would changing the parameter name in a parameterized query cause an SqlException to be thrown?

1 Answers1