SqlCommand.Parameters.AddWithValue 未返回正确结果
我承认，在 C# 中使用参数化查询时，我有点新手（尽管学得很快！），所以我可能只是忽略了这里的一些东西，但我似乎不知道如何获取一个适合我的参数化查询。
这是一个非常简化的示例。如果需要更多信息，我当然愿意提供。
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();
}
使用 SQL Server Profiler，我可以看到正在发出以下查询：
exec sp_executesql N'SELECT COUNT (*) FROM Sites WHERE ((STATE LIKE ''@STATE''))',N'@STATE nvarchar(2)',@STATE=N'MA'
如果我直接在 SQL Server Management Studio 中运行该查询，它将返回 0。但是，如果我像这样修改查询：
exec sp_executesql N'SELECT COUNT (*) FROM Sites WHERE ((STATE LIKE ''MA''))',N'@STATE nvarchar(2)',@STATE=N'MA'
并运行它，我会得到数了 51 回来，这是正确的。
我在这里缺少什么？
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您只需在 SQL 语句中取消对参数的引用（引用文本使 SQL Server 将其视为文字）。将此：更改
为：
You just need to unquote your parameter in the SQL statement (quoting text makes SQL Server treat it as a literal). Change this:
to this:
你不需要@STATE 周围的引号
you don't need quotes around @STATE