转换为nvarchar值的数据类型为int时，转换失败 [英] Conversion failed when converting the nvarchar value to data type int
问题描述
我在做什么错在这里？
string favorites = "210,213";
sqlCommand.CommandText = "select * from clients (nolock)
where Deleted = 0 and ClientID in (@favorites)";
sqlCommand.Parameters.AddWithValue("@favorites", favorites);
另外一个说明：在210213仅仅是一个例子。它可以是数字的任何长度。这就是为什么我使用的是SQL IN操作符。
One other note: the "210,213" is just an example. It could be any length of numbers. That is why I am using the SQL IN operator.
感谢。
推荐答案
这不是参数如何工作。你实际上是试图让客户那里客户端ID是等于210213。嗯，这不是一个数字，所以这是一个失败。
That's not how parameters work. You are actually trying to get clients where ClientID is equal to '210,213'. Well, that's not a number, so there's a failure.
参数化查询和在条款实际上是不平凡实现在一起，如果你的在列表大小是动态的，从改变时有发生。
Parametrized queries and IN clause are actually not trivially implemented together if your IN list size is dynamic, changing from time to time.
阅读这太问题和答案：一个参数化SQL IN子句
Read this SO question and answers: Parameterizing a SQL IN clause?
通常情况下，你必须动态地组装你的SQL查询，时刻牢记的问题，如 SQL注入。
Generally, you'd have to dynamically assemble your SQL query, always keeping in mind issues like SQL Injection.
延伸阅读：
- <一个href="http://stackoverflow.com/questions/7031693/best-practice-between-these-two-queries/7031810#7031810">Best这两个查询 的实践
- <一个href="http://stackoverflow.com/questions/6442205/deleting-from-sqlite-database-using-in/6442392#6442392">deleting使用IN SQLite数据库
- <一个href="http://stackoverflow.com/questions/6155146/problem-using-oracle-parameters-in-select-in/6155215#6155215">problem使用选择的Oracle参数
- <一个href="http://stackoverflow.com/questions/589931/is-it-possible-to-use-query-parameters-to-fill-the-in-keyword">Is有可能使用查询参数来填充IN关键字的
- Best practice between these two queries
- deleting from sqlite database using IN
- problem using Oracle parameters in SELECT IN
- Is it possible to use query parameters to fill the IN keyword
这篇关于转换为nvarchar值的数据类型为int时，转换失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
