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类型的
2 个解决方案
#1
1
Instead of using LIKE if the parameter is NULL, try this instead.
如果参数为空，不要使用LIKE，而是使用这个。
SELECT [columns] from [table] where @param is null or [column] = @param
If you pass in a NULL parameter everything is returned. If it isn't null, then only where the column matches the parameter will be returned.
如果传入一个空参数，则返回所有内容。如果它不是null，那么只有列与参数匹配的地方才会返回。
#2
2
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 column in where [column] LIKE @param is probably an integer. So even though you identify the parameter as string it is still trying to do an integer to string comparison.
很明显，您有一个数据类型问题。而且'%'的通配符肯定不是整数。您可能遇到了这个问题，因为@param等[列]中的列可能是整数。所以即使你把这个参数定义为字符串，它仍然在尝试对字符串进行整数比较。
So you are comparing like WHERE Integer LIKE String and that throws a datatype conversion error because SQL will automatically try to convert your string to an integer.
所以你在比较像字符串这样的整数它会抛出一个数据类型转换错误因为SQL会自动地把你的字符串转换成一个整数。
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.
#1
1
Instead of using LIKE if the parameter is NULL, try this instead.
如果参数为空，不要使用LIKE，而是使用这个。
SELECT [columns] from [table] where @param is null or [column] = @param
If you pass in a NULL parameter everything is returned. If it isn't null, then only where the column matches the parameter will be returned.
如果传入一个空参数，则返回所有内容。如果它不是null，那么只有列与参数匹配的地方才会返回。
#2
2
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 column in where [column] LIKE @param is probably an integer. So even though you identify the parameter as string it is still trying to do an integer to string comparison.
很明显，您有一个数据类型问题。而且'%'的通配符肯定不是整数。您可能遇到了这个问题，因为@param等[列]中的列可能是整数。所以即使你把这个参数定义为字符串，它仍然在尝试对字符串进行整数比较。
So you are comparing like WHERE Integer LIKE String and that throws a datatype conversion error because SQL will automatically try to convert your string to an integer.
所以你在比较像字符串这样的整数它会抛出一个数据类型转换错误因为SQL会自动地把你的字符串转换成一个整数。
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.