Datareader does not work

Question

This is my code and it keep getting errors

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Dim connection As New OleDbConnection("Provider = Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\Database1.accdb")
    Dim dataread As OleDbDataReader
    Dim f2 As New Form2
    Dim com As OleDbCommand
    connection.Open()
    com = New OleDbCommand("SELECT * FROM User WHERE usr_name='" & TextBox2.Text & "' AND password='" & TextBox1.Text & "'", connection)

    dataread = com.ExecuteReader()

    If dataread.HasRows = True Then
        Me.Hide()
        f2.Show()
    End If
    dataread.Close()
    dataread.Close()
    connection.Close()
End Sub

***WHAT*** errors do you get?? Remember: we **cannot** read your screen, nor your mind - if you want us to help you, you **need to SHOW US** what you have and what errors you get !!! — marc_s, Jun 29 '16 at 12:04

score 3 · Accepted Answer · edited May 23 '17 at 12:32

First thing to fix is the User tablename and the Password column name. Both User and Password are reserved words in Access Jet-SQL, you need square brackets around them. Then there is the problem of string concatenation in building your query. This leads to parsing errors and to Sql Injection (albeit less easy to implement in MS-Access). Anyway use always a parameterized query specifying exactly what is the datatype of the parameters passed leaving no 'guess what is that' on the database side.

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Using connection = New OleDbConnection("Provider = Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\Database1.accdb")
    Using com = New OleDbCommand("SELECT * FROM [User] " & _
                                 "WHERE usr_name=@name " & _
                                 "AND [password]=@pwd", connection)]
        connection.Open()
        com.Parameters.Add("@name", OleDbType.VarWChar).Value = TextBox1.Text
        com.Parameters.Add("@pwd", OleDbType.VarWChar).Value = TextBox2.Text
        Using dataread = com.ExecuteReader()
           If dataread.HasRows = True Then
                Dim f2 As New Form2
                Me.Hide()
                f2.Show()
           End If
        End Using
    End Using
    End Using
End Sub

Finally consider that having your passwords stored in clear text in a database table is a very bad practice from a security standpoint. Moreover with databases like MS-Access that are file based. Simply looking at your table could reveal the passwords of your users. This site has many questions answered about storing passwords on databases.

Datareader does not work

1 Answers1