PLease help me. I'm doing a login form with ms access but this line of code having problem "Dim dr As OleDbDataReader = cmd.ExecuteReader"

Below is the source code

Imports System.Data.OleDb

Public Class Form1
    Dim con As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\leong\documents\visual studio 2010\Projects\WindowsApplication4\WindowsApplication4\coursework.mdb")
    Dim cmd As OleDbCommand

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim sql = "SELECT Username ,Password FROM AdminUser WHERE Username='" & Logintxt.Text & "' AND Password='" & Passwordtxt.Text & "'"

        cmd = New OleDbCommand(sql, con)
        con.Open()
        Dim dr As OleDbDataReader = cmd.ExecuteReader

        Try
            If dr.Read = False Then
                MessageBox.Show("Authentication failed...")
                Me.Show()
            Else
                MessageBox.Show("Login successfully...")

            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

        con.Close()
    End Sub
End Class

help3

Edited 3 Years Ago by devianleong: image upload

Try to escape the reserved field names and also use parameterized query.

Dim sql = "SELECT [Username],[Password] FROM [AdminUser] WHERE [Username]=@user and [Password]=@pass"
cmd = New OleDbCommand(sql, con)
cmd.Parameters.AddWithValue("@user",Logintxt.Text)
cmd.Parameters.AddWithValue("@pass",Passwordtxt.Text)
con.Open()
Dim dr As OleDbDataReader = cmd.ExecuteReader
...

Try moving the line con.open() to above cmd = new oledbcommand(sql, con) i.e. have your connection open first
To check that the connection is open before executing the command, you may want to try this:

If Con.State  = ConnectionState.Open Then
    dim dr as OleDBDataReader = cmd.ExecuteReader
    ....
End if

I had tried this code but still get the same error:

Imports System.Data.OleDb

Public Class Form1
    Dim con As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\leong\documents\visual studio 2010\Projects\WindowsApplication4\WindowsApplication4\coursework.mdb")

    Dim cmd As OleDbCommand

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click


        Dim sql = "SELECT [Username],[Password] FROM [AdminUser] WHERE [Username]=@user and [Password]=@pass"
        con.Open()
        cmd = New OleDbCommand(sql, con)
        cmd.Parameters.AddWithValue("@user", Logintxt.Text)
        cmd.Parameters.AddWithValue("@pass", Passwordtxt.Text)
        If con.State = ConnectionState.Open Then
            Dim dr As OleDbDataReader = cmd.ExecuteReader

            Try
                If dr.Read = False Then
                    MessageBox.Show("Authentication failed...")
                    Me.Show()
                Else
                    MessageBox.Show("Login successfully...")

                End If
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
        Else
            MsgBox("Error")
        End If
        con.Close()
    End Sub
End Class
This article has been dead for over six months. Start a new discussion instead.