VB NET + Substring + Mysql + Check Rows

Question

I have a simple form with a button and 2 textbox.

    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

when i put in textbox2 "1" i have my desired value. But when i put "2", this id_product dont exist, i dont have "0". It's blank and Textbox1 has my previous value.

What i do wrong ??

If the query doesn't return any record, your code doesn't enter the while loop (dbReader.Read return false) and thus you don't set the textbox to "0" but you should start to use parameterized queries. It is very important — Steve, May 04 '17 at 18:12
If it doesn't exists, your code won't even go inside the While loop. You should put a breakpoint and step through. Also, don't concatenate your queries, use parameters! — the_lotus, May 04 '17 at 18:14

score 0 · Accepted Answer · edited May 23 '17 at 12:10

If the query doesn't return any record, your code doesn't enter the while loop (dbReader.Read return false) and thus you don't set the textbox to "0" but you should also start to use parameterized queries. It is very important to avoid possible parsing errors and mainly to avoid Sql Injection attacks

So, you could test if your query has produced any record verifying the property HasRows of the DataReader

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

VB NET + Substring + Mysql + Check Rows

1 Answers1