mysql - VB NET +子字符串+ Mysql +检查行

我有一个带有按钮和2个文本框的简单表单。

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    MysqlConn = New MySqlConnection(ConfigurationManager.ConnectionStrings("db.My.MySettings.dbConnectionString").ToString)

    Try
        'MysqlConn.Dispose()
        'MysqlConn.Close()
        MysqlConn.Open()
        Dim str As String
        str = "SELECT substring_index(substring(path,1,locate(substring_index(path,'\\',-1),path)-2),'\\',-1)as PATH FROM foto where id_product = '" & TextBox2.Text & "'"
        Dim dbCommand As New MySqlCommand(str, MysqlConn)
        Dim dbReader = dbCommand.ExecuteReader
        While dbReader.Read()
            If IsDBNull(dbReader(0)) OrElse String.IsNullOrEmpty(dbReader.GetString(0)) Then
                TextBox1.Text = "0"
            Else
                TextBox1.Text = dbReader.Item("PATH")
            End If
        End While
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    Finally
        MysqlConn.Dispose()
    End Try
End Sub

当我在textbox2中输入“ 1”时，我就有了所需的值。
但是，当我输入“ 2”时，此id_product不存在，我没有“ 0”。
它为空白，并且Textbox1具有我以前的值。

我做错了什么??

最佳答案

如果查询不返回任何记录，则您的代码不会进入while循环（dbReader.Read返回false），因此您无需将文本框设置为“ 0”，但还应该开始使用参数化查询。避免可能的解析错误，并且主要是避免Sql Injection attacks，这一点非常重要

因此，您可以测试查询是否生成了任何记录，以验证DataReader的属性HasRows

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Using MysqlConn = New MySqlConnection(ConfigurationManager.ConnectionStrings("db.My.MySettings.dbConnectionString").ToString)

        Try
            MysqlConn.Open()
            Dim str As String
            str = "SELECT substring_index
                          (substring(path,1,
                          locate(substring_index(path,'\\',-1),path)-2),
                          '\\',-1) as PATH
                   FROM foto where id_product = @pid"
            Dim dbCommand As New MySqlCommand(str, MysqlConn)
            dbCommand.Parameters.Add("@pid", MySqlDbType.VarChar).Value = textBox2.Text
            Using dbReader = dbCommand.ExecuteReader
               if dbReader.HasRows Then
                  While dbReader.Read()
                     If IsDBNull(dbReader(0)) OrElse String.IsNullOrEmpty(dbReader.GetString(0)) Then
                        TextBox1.Text = "0"
                     Else
                        TextBox1.Text = dbReader.Item("PATH")
                     End If
                  End While
               else
                 TextBox1.Text = "0"
               End If
           End Using
       Catch ex As Exception
             MessageBox.Show(ex.Message)
       End Try
    End Using
End Sub

关于mysql - VB NET +子字符串+ Mysql +检查行，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/43789966/