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

Question

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

Does this answer your question? [OleDbParameters and Parameter Names](https://stackoverflow.com/questions/2407685/oledbparameters-and-parameter-names) — GSerg, Jun 04 '20 at 19:24
The code looks ok, please double check your connection string. I am not sure if this would make a difference, but you're adding a `@Password` parameter that is not used in the query (again, I don't know if this makes any difference...) — nurchi, Jun 04 '20 at 19:24
You can't call `MsgBox(dr.Item("Gebruikersnaam").ToString)` before you `dr.Read()` it. — LarsTech, Jun 04 '20 at 19:25
@JoelCoehoorn, what's wrong with plain-text passwords? :) :P — nurchi, Jun 04 '20 at 19:30
@nurchi, nothing wrong with plain-text passwords (Hardcoded passwords), except that they are week and RISKY. — evry1falls, Jun 04 '20 at 19:36
@evry1falls, it was a rhetorical question and didn't require an answer :) — nurchi, Jun 04 '20 at 19:37
@Bjorn M, please confirm that there is a Table called (Gebruikers) and has a Column called (Gebruikersnaam) and it has values in it. — evry1falls, Jun 04 '20 at 19:38
@evry1falls, there is a table called Gebruikers and a Column called Gebruikersnaam. And yes it has values in it. I even changed the select statement to <> '@Username. But it still can't find any records.... — Bjorn M, Jun 04 '20 at 20:26
@LarsTech, I placed the `MsgBox(dr.Item("Gebruikersnaam").ToString)` after the `dr.Read()`, but the result is still the same. — Bjorn M, Jun 04 '20 at 20:50

Joel Coehoorn · Accepted Answer · 2020-06-04T21:49:33.250

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.

the password field was a test. First I wanted to find out why I didn't get any results. Thanks for the solution. — Bjorn M, Jun 04 '20 at 21:10
@Joel Therefore, the order in which OleDbParameter objects are added to the OleDbParameterCollection must directly correspond to the position of the question mark placeholder for the parameter in the command text. (Source https://learn.microsoft.com/en-us/dotnet/api/system.data.oledb.oledbcommand.parameters?view=dotnet-plat-ext-3.1) — evry1falls, Jun 04 '20 at 21:48

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

1 Answers1