vb.net 将 nvarchar 值“%”转换为数据类型 int 时转换失败
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/39081796/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Conversion failed when converting the nvarchar value '%' to data type int
提问by Web Develop Wolf
I have an Object Data Source which pulls information from the database where if we don'd have a value for this particular parameter then we want to get all records. So I have a LIKE statement and I'm using a wildcard to return all entries, but this is giving me the error message
我有一个对象数据源，它从数据库中提取信息，如果我们没有这个特定参数的值，那么我们想要获取所有记录。所以我有一个 LIKE 语句，我使用通配符返回所有条目，但这给了我错误消息
System.Data.SqlClient.SqlException (0x80131904): Conversion failed when converting the nvarchar value '%' to data type int.
System.Data.SqlClient.SqlException (0x80131904)：将 nvarchar 值“%”转换为数据类型 int 时转换失败。
The table structure for the relevant column is:
相关列的表结构是：
[columnName] VARCHAR(50)
The SQL is something similar to:
SQL类似于：
SELECT [columns] from [table] where [column] LIKE @param
Then in VB I add the parameter:
然后在VB中我添加参数：
Dim sessionParam As New Parameter
sessionParam.Name = "param"
sessionParam.DefaultValue = "%"
sessionParam.Type = TypeCode.String
SqlDataSource1.SelectParameters.Add(sessionParam)
So far I've tried casting the parameter value, casting the column, using dbType for the parameter instead of type, but nothing seems to work and I just get the same error. I suspect the column is reading as an int as this column is a mix of values so some are numbers, some are text, therefore SQL is making the 'educated guess' that that value needs to be an int
到目前为止，我已经尝试转换参数值，转换列，使用 dbType 作为参数而不是类型，但似乎没有任何效果，我只是得到相同的错误。我怀疑该列正在读取为 int，因为该列是值的混合，因此有些是数字，有些是文本，因此 SQL 正在做出“有根据的猜测”该值需要是 int
采纳答案by scsimon
Instead of using LIKEif the parameter is NULL, try this instead.
而不是使用LIKEif 参数是NULL，试试这个。
SELECT [columns] from [table] where @param is null or [column] = @param
If you pass in a NULLparameter everything is returned. If it isn't null, then only where the column matches the parameter will be returned.
如果您传入NULL参数，则返回所有内容。如果它不为空，则仅返回与参数匹配的列。
回答by Matt
Conversion failed when converting the nvarchar value '%' to data type int
将 nvarchar 值“%”转换为数据类型 int 时转换失败
Is pretty clear that you have a datatype issue. And the wildcard of '%'is certainly not a integer. You are probably having the issue because the columnin where [column] LIKE @paramis probably an integer. So even though you identify the parameter as stringit is still trying to do an integerto stringcomparison.
很明显你有一个数据类型问题。而通配符 of'%'肯定不是integer. 您可能遇到了这个问题，因为columninwhere [column] LIKE @param可能是一个integer. 因此，即使您确定参数，因为string它仍在尝试integer进行string比较。
So you are comparing like WHERE Integer LIKE Stringand that throws a datatype conversion error because SQL will automatically try to convertyour stringto an integer.
因此，您正在比较 likeWHERE Integer LIKE String并且会引发数据类型转换错误，因为 SQL 会自动尝试将convert您string的integer.
To solve if you want to search for a number as a string which doesn't seem like a good idea you would do something like:
要解决是否要将数字作为字符串搜索这似乎不是一个好主意，您可以执行以下操作：
WHERE CAST([column] AS VARCHAR(10)) LIKE @param.
