如何处理从字符串到类型整数的转换无效 [英] How to handle conversion from string to type integer is not valid

问题描述

我只是想知道是否有人可以帮我解决以下问题。我正在使用VB。 NET和SQL Server，并希望在我的代码中包含一些错误处理。 
 
我理解这个错误的发生是因为数据类型是字符串。

我尝试过：

 Public Sub addProducts（）
尝试
 dbConnection（）
 search_query = SELECT * FROM tblproducts WHERE product_name = @product_name; 
 command = New SqlCommand 
使用命令
 .Connection = connection 
 .CommandText = search_query 
 .Parameters.Clear（）
 .Parameters.Add（New SqlParameter使用{.ParameterName =@ product_name，。SqlDbType = SqlDbType.VarChar，.Value = productsView.txtProductName.Text}）
 Dim check As String = .ExecuteScalar（）。ToString（）
 If检查> 0然后
 formNotify.Show（Product already exists！，formNotify.AlertType.warning）
 Else 
 insert_query =INSERT INTO tblproducts（product_barcode，product_name，product_sprice，product_type，product_supplier，product_unit ）& _ 
VALUES（@ barcode，@ name，@ price，@ type，@ supplier，@ unit）; 
 command = New SqlCommand 
使用命令
 .Connection = connection 
 .CommandText = insert_query 
 .Parameters.Clear（）
 .Parameters.Add（New SqlParameter使用{.ParameterName =@ barcode，。SqlDbType = SqlDbType.VarChar，.Value = productsView.txtProductBarcode.Text}）
 .Parameters.Add（New SqlParameter with {.ParameterName =@ name,. SqlDbType = SqlDbType.VarChar，.Value = productsView.txtProductName.Text}）
 .Parameters.Add（带有{.ParameterName =@ price的新SqlParameter，.SqlDbType = SqlDbType.Int，.Value = productsView.txtSellingPrice .Text}）
 .Parameters.Add（New SqlParameter With {.ParameterName =@ type，。SqlDbType = SqlDbType.VarChar，.Value = productsView.cbProductType.SelectedItem}）
 .Parameters.Add （新的SqlParamete r {.ParameterName =@ Supplier，。SqlDbType = SqlDbType.VarChar，.Value = productsView.cbSupplierName.SelectedItem}）
 .Parameters.Add（New SqlParameter With {.ParameterName =@ unit,. SqlDbType = SqlDbType.VarChar，.Value = productsView.cbUnit.SelectedItem}）
 result = .ExecuteNonQuery（）
如果result = 0那么
 formNotify.Show（添加产品时出错！ ，formNotify.AlertType.err0r）
 Else 
 formNotify.Show（Product added！，formNotify.AlertType.success）
 End if 
 End with 
 End If 
以
结束Catch ex As SqlException 
 MsgBox（Error：+ ex.Message）
最后
 connection.Close（）
 command.Dispose （）
结束尝试
结束子

解决方案

简单：验证您的输入。

当您轻松地将用户输入直接传递到您的数据库时，您将无法控制他们正在做什么。当它是你传递的数值时，你真的必须检查它是你发送的数字。

最好的方法是使用Int32.TryParse [ ^ ]将Price（和任何其他数字值）转换为数字，报告问题用户这样他就可以纠正它们，然后将数字直接传递给SQL：

  Dim 价格< span class =code-keyword>作为 整数 
 如果 不 整数 .TryParse（productsView.txtSellingPrice.Text，price）然后 
 ' 报告问题用户。 
 返回 
 结束 如果 
 ... 
 .Parameters.AddWithValue（  @sprice，价格）

首先 - 带下划线的代码永远不会成真：

  
 search_query =   SELECT * FROM tblproducts WHERE product_name = @ product_name;  
 command = New SqlCommand 
 with command 
 .Connection = connection 
 .CommandText = search_query 
 .Parameters.Clear（ ）
 .Parameters.Add（新的SqlParameter，{.ParameterName =   @ product_name， .SqlDbType = SqlDbType.VarChar，.Value = productsView.txtProductName.Text}）
  Dim check As  Str ing  = .ExecuteScalar（）。ToString（）
如果检查>   0 然后 
 

为什么？由于一系列原因：

1. SELECT * 将返回表格中所有字段的数据。

2 。 ExecuteScalar [ ^ ]方法从数据库中检索单个值。

3. check 被声明为字符串变量，但你是试图将它用作整数变量。



还有一些错误......我不会在这里列举它...请检查 OriginalGriff [ ^ ]的答案（解决方案＃1）。



如何解决？

广告。 1.

使用：

  SELECT  COUNT（*）作为  FROM  tblproducts  WHERE  product_name =  @ product_name ; 

Ad。 2.和3

  Dim 检查 As  整数 = .ExecuteScalar（）
 如果检查> 0 然后 

I am just wondering if someone could help me with the following. I am using VB. NET and SQL Server and want to include some error handling in my code.

I understand this error occurs because the datatype is string.

What I have tried:

Public Sub addProducts()
        Try
            dbConnection()
            search_query = "SELECT * FROM tblproducts WHERE product_name = @product_name;"
            command = New SqlCommand
            With command
                .Connection = connection
                .CommandText = search_query
                .Parameters.Clear()
                .Parameters.Add(New SqlParameter With {.ParameterName = "@product_name", .SqlDbType = SqlDbType.VarChar, .Value = productsView.txtProductName.Text})
                Dim check As String = .ExecuteScalar().ToString()
                If check > 0 Then
                    formNotify.Show("Product already exists!", formNotify.AlertType.warning)
                Else
                    insert_query = "INSERT INTO tblproducts(product_barcode, product_name, product_sprice, product_type, product_supplier, product_unit) " & _
                        "VALUES(@barcode, @name, @sprice, @type, @supplier, @unit);"
                    command = New SqlCommand
                    With command
                        .Connection = connection
                        .CommandText = insert_query
                        .Parameters.Clear()
                        .Parameters.Add(New SqlParameter With {.ParameterName = "@barcode", .SqlDbType = SqlDbType.VarChar, .Value = productsView.txtProductBarcode.Text})
                        .Parameters.Add(New SqlParameter With {.ParameterName = "@name", .SqlDbType = SqlDbType.VarChar, .Value = productsView.txtProductName.Text})
                        .Parameters.Add(New SqlParameter With {.ParameterName = "@sprice", .SqlDbType = SqlDbType.Int, .Value = productsView.txtSellingPrice.Text})
                        .Parameters.Add(New SqlParameter With {.ParameterName = "@type", .SqlDbType = SqlDbType.VarChar, .Value = productsView.cbProductType.SelectedItem})
                        .Parameters.Add(New SqlParameter With {.ParameterName = "@supplier", .SqlDbType = SqlDbType.VarChar, .Value = productsView.cbSupplierName.SelectedItem})
                        .Parameters.Add(New SqlParameter With {.ParameterName = "@unit", .SqlDbType = SqlDbType.VarChar, .Value = productsView.cbUnit.SelectedItem})
                        result = .ExecuteNonQuery()
                        If result = 0 Then
                            formNotify.Show("Error in adding product!", formNotify.AlertType.err0r)
                        Else
                            formNotify.Show("Product added!", formNotify.AlertType.success)
                        End If
                    End With
                End If
            End With
        Catch ex As SqlException
            MsgBox("Error: " + ex.Message)
        Finally
            connection.Close()
            command.Dispose()
        End Try
    End Sub

解决方案

Simple: validate your inputs.
When you blithely pass user inputs direct to your DB you lose all control of what they are doing. And when it's a numerical value that you are passing, you really do have to check it's a number that you are sending.
The best way to do that is to use Int32.TryParse[^] to convert the Price (and any other numberic values) to numbers, reporting problems to teh user so he can correct them, and then pass the number directly to SQL:

    Dim price As Integer
    If Not Integer.TryParse(productsView.txtSellingPrice.Text, price) Then
        ' Report problem to user.
        Return
    End If
...
    .Parameters.AddWithValue("@sprice", price)

First of all - underlined piece of code will never get true:

 
search_query = "SELECT * FROM tblproducts WHERE product_name = @product_name;"
 command = New SqlCommand
 With command
     .Connection = connection
     .CommandText = search_query
     .Parameters.Clear()
     .Parameters.Add(New SqlParameter With {.ParameterName = "@product_name", .SqlDbType = SqlDbType.VarChar, .Value = productsView.txtProductName.Text})
     Dim check As String = .ExecuteScalar().ToString()
     If check > 0 Then

Why? Because of set of reason:
1. SELECT * will return data from all fields in a table.
2. ExecuteScalar[^] method retrieves a single value from a database.
3. check is declared as a string variable, but you're trying to use it as an integer variable.

There's few more mistakes... I'm won't to enumerate it here... Please, check OriginalGriff[^]'s answer (solution #1).

How to resolve it?
Ad. 1.
Use:

SELECT COUNT(*) As  FROM tblproducts WHERE product_name = @product_name;

Ad. 2. and 3

Dim check As Integer = .ExecuteScalar()
If check >0 Then

这篇关于如何处理从字符串到类型整数的转换无效的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！