TMJayalath TMJayalath - 5 months ago 15
Vb.net Question

Datareader does not work

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

Answer

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.

Comments