I'll admit that I'm a bit of a 开发者_如何转开发newbie (though learning fast!) when it comes to using parameterized queries in C#, so I'm probably just overlooking something here, but I can't seem to figure out how to get a parameterized query to work for me.
Here is a much simplified example. If more information is needed, I am certainly willing to supply it.
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = "SELECT COUNT (*) FROM Sites WHERE ((STATE LIKE '@STATE'));
command.Parameters.AddWithValue("@State", "MA");
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
count = Convert.ToInt32(reader[0]);
}
reader.Close();
connection.Close();
}
Using SQL Server Profiler, I can see that the following query is being issued:
exec sp_executesql N'SELECT COUNT (*) FROM Sites WHERE ((STATE LIKE ''@STATE''))',N'@STATE nvarchar(2)',@STATE=N'MA'
If I run that query directly in SQL Server Management Studio, it returns 0. If, however, I modify the query like this:
exec sp_executesql N'SELECT COUNT (*) FROM Sites WHERE ((STATE LIKE ''MA''))',N'@STATE nvarchar(2)',@STATE=N'MA'
And run it, I get a count of 51 back, which is correct.
What am I missing here?
You just need to unquote your parameter in the SQL statement (quoting text makes SQL Server treat it as a literal). Change this:
command.CommandText = "SELECT COUNT (*) FROM Sites WHERE ((STATE LIKE '@STATE'));
to this:
command.CommandText = "SELECT COUNT (*) FROM Sites WHERE ((STATE LIKE @STATE));
you don't need quotes around @STATE
精彩评论