How to display message if no records found

Question

This is my code in filtering data into TextBoxes/Labels

Dim reader As MySqlDataReader
    Try
        If conn.State = ConnectionState.Closed Then
            conn.Open()
        End If

        Dim query As String
        query = "SELECT * FROM member WHERE (memor = '" & memor.Text & "')"

        sqlcom = New MySqlCommand(query, conn)
        reader = sqlcom.ExecuteReader
        While reader.Read()
            Me.lblname.Text = reader("membname").ToString
            Me.txtmembtype.Text = reader("membtype").ToString
            Me.lblmembdate.Text = reader("membdate").ToString
        End While

        conn.Close()
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    Finally
        conn.Dispose()
    End Try

Now I want to have a message box that prompts that there is no data found in my mysql database? Any idea?

You should look into `Using` blocks - your connection, command object and reader should all be [disposed of when you are done with them](http://stackoverflow.com/a/29187199/1070452). — Ňɏssa Pøngjǣrdenlarp, Oct 20 '15 at 15:13

score 0 · Accepted Answer · answered Oct 20 '15 at 15:09

You can simply use reader.HasRows() to check for records

reader = sqlcom.ExecuteReader
if Reader.HasRows() Then
    While reader.Read()
        Me.lblname.Text = reader("membname").ToString
        Me.txtmembtype.Text = reader("membtype").ToString
        Me.lblmembdate.Text = reader("membdate").ToString
    End While
Else
    MessageBox.Show("No records found")
End If

score 0 · Answer 2 · edited May 23 '17 at 12:03

Beaten, but still posting for the obligatory SQL parameterization warning.

Revised code:

Try
    If conn.State = ConnectionState.Closed Then
        conn.Open()
    End If


    Dim query As String
    query = "SELECT * FROM member WHERE (memor = @memor)"

    sqlcom = New MySqlCommand(query, conn)
    sqlcom.Parameters.AddWithValue("@memor", memor.Text)
    reader = sqlcom.ExecuteReader
    If reader.HasRows Then
        While reader.Read()
            Me.lblname.Text = reader("membname").ToString
            Me.txtmembtype.Text = reader("membtype").ToString
            Me.lblmembdate.Text = reader("membdate").ToString
        End While
    Else
        MsgBox("No data found.")
    End If

    conn.Close()
Catch ex As Exception
    MessageBox.Show(ex.Message)
Finally
    conn.Dispose()

End Try

How to display message if no records found

2 Answers2