vb.net 将 varchar 值“[]”转换为数据类型 int 时转换失败
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20402169/
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 varchar value '[]' to data type int
提问by jimmyahughes
I'm trying to create a search function that uses a TextBox and two DropDownLists to return entries from my MSSQL DB. The way I have it set up is by passing the search queries via QueryStrings to a GridView on a separate page. Here's what I have for the search function:
我正在尝试创建一个搜索函数，该函数使用一个 TextBox 和两个 DropDownLists 从我的 MSSQL DB 返回条目。我设置它的方法是通过 QueryStrings 将搜索查询传递到单独页面上的 GridView。这是我的搜索功能：
Partial Class Includes_LeftCol
Inherits System.Web.UI.UserControl
Public Sub btnSearch_Click(sender As Object, e As System.EventArgs) Handles btnSearch.Click
Dim SelectURLRangePrice As String
If tbxSearch.Text.Length = 0 And ddlRangeName.SelectedValue = "" And ddlPriceRange.SelectedValue = "" Then
SelectURLRangePrice = "SearchResults.aspx"
Response.Redirect(SelectURLRangePrice)
ElseIf tbxSearch.Text.Length = 0 And ddlRangeName.SelectedValue = "" And ddlPriceRange.SelectedValue.Length > 0 Then
SelectURLRangePrice = "SearchResults.aspx?price=" & ddlPriceRange.Text
Response.Redirect(SelectURLRangePrice)
ElseIf tbxSearch.Text.Length = 0 And ddlRangeName.SelectedValue.Length > 0 And ddlPriceRange.SelectedValue.Length > 0 Then
SelectURLRangePrice = "SearchResults.aspx?range=" & ddlRangeName.Text & "&price=" & ddlPriceRange.Text
Response.Redirect(SelectURLRangePrice)
End If
Dim SelectURLRange As String
If tbxSearch.Text.Length = 0 And ddlRangeName.SelectedValue.Length = 0 Then
SelectURLRange = "SearchResults.aspx"
Response.Redirect(SelectURLRange)
ElseIf tbxSearch.Text.Length = 0 And ddlRangeName.SelectedValue.Length > 0 Then
SelectURLRange = "SearchResults.aspx?range=" & ddlRangeName.Text
Response.Redirect(SelectURLRange)
End If
Dim SelectURL As String
If tbxSearch.Text.Length = 0 Then
SelectURL = "SearchResults.aspx"
Response.Redirect(SelectURL)
ElseIf tbxSearch.Text.Length > 0 Then
SelectURL = "SearchResults.aspx?search=" & tbxSearch.Text
Response.Redirect(SelectURL)
End If
End Sub
End Class
And here's my SQL statement:
这是我的 SQL 语句：
SELECT Product_Rental, Product_ID, Range_Name, Model_Name, Product_Name, Product_Year, Product_Code, Product_Active, Product_DateAdded
FROM Products
WHERE (Range_Name LIKE '%' + @Range_Name + '%') OR
(Model_Name LIKE '%' + @Model_Name + '%') OR
(Product_Name LIKE '%' + @Product_Name + '%') OR
(Product_Code LIKE '%' + @Product_Code + '%') OR
(Product_Year LIKE '%' + @Product_Year + '%') OR
(Product_Rental BETWEEN @Product_Rental AND @Product_Rental + 50)
Once submitted, I get the following error:
提交后，我收到以下错误：
Conversion failed when converting the varchar value '[e.g. 209.35]' to data type int.
将 varchar 值“[例如 209.35]”转换为数据类型 int 时转换失败。
I'm a beginner at this so apologies if it's something stupid, and I've tried to go as far as I can before posting, but I just can't get past this.
我是这方面的初学者，所以如果它是愚蠢的，我很抱歉，并且在发布之前我已经尽力而为，但我无法克服这一点。
Thanks in advance.
提前致谢。
回答by GarethD
The problem is here:
问题在这里：
Product_Rental BETWEEN @Product_Rental AND @Product_Rental + 50
Because you are using + 50data type precedencekicks in. The docs state:
因为您正在使用+ 50数据类型优先级。文档状态：
When an operator combines two expressions of different data types, the rules for data type precedence specify that the data type with the lower precedence is converted to the data type with the higher precedence. If the conversion is not a supported implicit conversion, an error is returned. When both operand expressions have the same data type, the result of the operation has that data type.
当一个运算符组合两个不同数据类型的表达式时，数据类型优先级规则指定优先级较低的数据类型转换为优先级较高的数据类型。如果转换不是受支持的隐式转换，则返回错误。当两个操作数表达式具有相同的数据类型时，运算的结果将具有该数据类型。
So internally SQL Server attempts to convert @Product_Rentalto an int (because 50 is an int, and an int has a higher precedence than varchar). So whenever This can reproduced fairly simply:
所以在内部 SQL Server 尝试转换@Product_Rental为 int（因为 50 是一个 int，并且 int 的优先级高于 varchar）。因此，每当 This 可以相当简单地重现时：
DECLARE @Var VARCHAR(6) = '50';
SELECT *
FROM (VALUES (1)) T (A)
WHERE A < @Var + 50;
Then checking the execution plan XML we can see the comversion behind the scenes:
然后检查执行计划 XML 我们可以看到幕后的转换：
<ScalarOperator ScalarString="(1)<(CONVERT_IMPLICIT(int,[@Var],0)+(50))">
This basically shows that SQL Server has essentially turned
这基本上表明 SQL Server 基本上已经转向
A < @Var + 50
Into
进入
A < CONVERT(INT, @Var) + 50;
This is fine if @Varconverts to an int, but if it has a decimal point it will not, as can be shown by:
如果@Var转换为 int，这很好，但如果它有小数点，则不会，如以下所示：
SELECT CONVERT(INT, '50.0');
I would suggest if your column Product_Rentalcontains decimal data, then it should be of the decimal type, as should any parameters used to filter the column.
我建议如果您的列Product_Rental包含十进制数据，那么它应该是十进制类型，用于过滤列的任何参数也应该如此。
回答by N0Alias
The SQL Type INT is for whole numbers (1,2,3). FLOAT could support decimals. And text characters like [ and ] definitely won't work with an INT column.
SQL 类型 INT 用于整数 (1,2,3)。FLOAT 可以支持小数。像 [ 和 ] 这样的文本字符绝对不能用于 INT 列。
Edit from Comments:
从评论编辑：
Since the Product_Rental field is VarChar try changing to this (if you don't want to change the type on the table, which I recommend):
由于 Product_Rental 字段是 VarChar 尝试更改为此（如果您不想更改表上的类型，我建议这样做）：
--Make @Product_Rental a numeric type first.
(CAST(Product_Rental AS FLOAT) BETWEEN @Product_Rental AND @Product_Rental + 50)
回答by 15ee8f99-57ff-4f92-890c-b56153
The problem is here:
问题在这里：
Product_Rental BETWEEN @Product_Rental AND @Product_Rental + 50
You've got operands of multiple types there. One of them is an integer. SQL Server is trying to convert everything to an integer, so they can all be compared meaningfully, apples to apples (why does it pick integer? Precedence rules). It can't do that in this case, because in SQL you can't implicitly convert a varchar with a decimal point to an integer. Information will be lost there, so it won't just do that automatically for you. Ifyou really wanted all integers, you would have to convert to float, then implicitly truncate the float to an integer. Better would be to convert everything here to a float up front, because you do actually want to be comparing floats here. If you do that, SQL Server won't be left floundering around guessing what data type you want.
那里有多种类型的操作数。其中之一是整数。SQL Server 正在尝试将所有内容都转换为整数，因此它们都可以进行有意义的比较，苹果对苹果（为什么选择整数？优先规则）。在这种情况下它不能这样做，因为在 SQL 中你不能将带小数点的 varchar 隐式转换为整数。信息将在那里丢失，因此它不会自动为您丢失。如果您真的想要所有整数，则必须转换为浮点数，然后将浮点数隐式截断为整数。更好的是将这里的所有内容预先转换为浮点数，因为您确实想在这里比较浮点数。如果你这样做了，SQL Server 就不会因为猜测你想要什么数据类型而苦苦挣扎。
So, if the varchar values have decimal points, you will get an error that resembles the one you gave us, but it won't have square brackets in it. (PLEASE don't insert random characters into error messages and tell people that's what SQL Server gave you, if it isn't).
所以，如果 varchar 值有小数点，你会得到一个类似于你给我们的错误，但它不会有方括号。（请不要在错误消息中插入随机字符并告诉人们这是 SQL Server 给你的，如果不是的话）。
Try this:
尝试这个：
convert(float,Product_Rental)
BETWEEN convert(float,@Product_Rental)
AND convert(float,@Product_Rental) + 50
This is why you should use the correct data types in your table and in your variables. Both should have been decimal or float all along.
这就是为什么您应该在表和变量中使用正确的数据类型。两者都应该一直是十进制或浮点数。
If the actual value in your variable, or in either of those fields, is '[e.g. 209.35]', that can't be converted to float anyhow.
如果您的变量或其中任何一个字段中的实际值是'[e.g. 209.35]'，则无论如何都无法将其转换为浮点数。
