VB.NET SQL statement doesn’t return any rows form Access database

I have the following code for a login winform. When I make the connection to the database and make a select statement I get no rows back. I’m getting the message “No data exists for the row/column.”

But there are rows and columns in the database.

Can someone tell me what I do wrong?

Private Sub OK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OK.Click
    Dim connectionString As String = System.Configuration.ConfigurationManager.ConnectionStrings("MySqlConnectionString").ConnectionString
    Using con As New OleDbConnection(connectionString)
        Dim intResult As Integer = 0
        ' MsgBox(connectionString)
        Try
            con.Open()

            Using cmd As New OleDbCommand("SELECT Gebruikersnaam FROM Gebruikers WHERE Gebruikers.Gebruikersnaam = @Username", con)
                cmd.Parameters.AddWithValue("@Username", UsernameTextBox.Text)
                cmd.Parameters.AddWithValue("@Password", PasswordTextBox.Text)
                Using dr As OleDbDataReader = cmd.ExecuteReader()

                    'intResult = CInt(cmd.ExecuteScalar)

                    'If intResult > 0 Then
                    MsgBox(dr.Item("Gebruikersnaam").ToString)
                    'End If

                    With dr
                        While .Read()
                            MsgBox(.HasRows)
                            'MsgBox(.Item("Gebruikersnaam"))
                            'TextBox1.Text = .Item("Gebruikersnaam") & vbCrLf
                        End While
                    End With
                End Using
            End Using

        Catch ex As Exception
            MsgBox(ex.Message)

            con.Close()
        End Try

        Me.Close()
    End Using
End Sub

Answer

The problem was checking dr.Item() before ever calling dr.Read(). Aside from the that, make sure the username in UsernameTextBox actually exists in the database, fix those nasty plain-text passwords, and you’ll be fine.

Private Sub OK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OK.Click
    Dim connectionString As String = System.Configuration.ConfigurationManager.ConnectionStrings("MySqlConnectionString").ConnectionString
    Try
        Dim result As New StringBuilder()
        Using con As New OleDbConnection(connectionString)
        Using cmd As New OleDbCommand("SELECT Gebruikersnaam FROM Gebruikers WHERE Gebruikersnaam = @Username", con)

            cmd.Parameters.AddWithValue("@Username", UsernameTextBox.Text)
            cmd.Parameters.AddWithValue("@Password", PasswordTextBox.Text)
            con.Open()

             Using dr As OleDbDataReader = cmd.ExecuteReader()
                  While dr.Read() 
                      'MsgBox(dr("Gebruikersnaam"))
                      result.AppendLine(dr("Gebruikersnaam"))
                  End While
              End Using
        End Using
        End Using
        TextBox1.Text = result.ToString()
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try
End Sub

One final concern. I’m not sure which OLE provider you’re using, but last time I checked most providers where it makes sense to use OLE want you to use ? placeholders instead of named parameters. So the SQL command would look like this:

SELECT Gebruikersnaam FROM Gebruikers WHERE Gebruikersnaam = ?

But if you’re really using MySql, as the connection string name suggests, you really do so much better getting the real MySql ADO.Net library instead of OleDB: minor performance gain, better error messaging, etc.