How to Authentication Users Using MS Access

[Jim Clinton]

**          Here's my codes:

            Public Class frmLogin
                Dim sqlcode As String
                Dim connstring As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Jim Clinton Amarga\Desktop\Class Records\Class Records\bin\Debug\dbClassRecords.accdb"

            Private Sub btnLogin_Click(sender As Object, e As EventArgs) Handles btnLogin.Click

                    If cmbLoginType.Text = "Administrator" Then

                        Dim sqlLoginCode As String = "select * from tblusers where User_username = '" & txtUsername.Text & "' AND User_password = '" & txtPass.Text & "' AND User_usertype = '" & "Administrator" & "' "
                        Dim loginCommand As New OleDb.OleDbCommand(sqlLoginCode)
                        loginCommand.Connection = New OleDb.OleDbConnection(connstring)

                        loginCommand.Connection.Open()
                        loginCommand.ExecuteNonQuery()
                        Dim da As New OleDb.OleDbDataAdapter(loginCommand)

                        Dim ds As New DataSet

                        da.Fill(ds, "tblusers")

                        MessageBox.Show("Welcome Admin")
                        frmAdmin.Show()
                        Me.Hide()

                    ElseIf cmbLoginType.Text = "Teacher" Then

                        Dim sqlLoginCode As String = "select * from tblusers where User_username = '" & txtUsername.Text & "' AND User_password = '" & txtPass.Text & "' AND User_usertype = '" & "Teacher" & "' "
                        Dim loginCommand As New OleDb.OleDbCommand(sqlLoginCode)
                        loginCommand.Connection = New OleDb.OleDbConnection(connstring)

                        loginCommand.Connection.Open()
                        loginCommand.ExecuteNonQuery()
                        Dim da As New OleDb.OleDbDataAdapter(loginCommand)

                        Dim ds As New DataSet

                        da.Fill(ds, "tblusers")

                        MessageBox.Show("Welcome Teacher")
                        frmTeacherForm.Show()
                        Me.Hide()

                    Else
                        MessageBox.Show("Login Failed!!")
                        txtUsername.Clear()
                        txtPass.Clear()
                        txtUsername.Focus()

                    End If

            End Sub

            DATABASE ACCESS: tblusers
            User_Id         | PK
            User_fname | Short Text
            User_lname  |Short Text
            User_username |Short Text
            User_password  |Short Text
            User_usertype    |Short Text

            When I Selected "Administrator" from the combobox it logged in directly without input the Username and password. please help

**

[Santanu.Das]

From my opinion you already wrote a code in cmblogintype SelectionChanged event which redirecting you on selection.

Secondly, why are you using loginCommand.ExecuteNonQuery() . You can use it to write data in a table not for reading.
To read use ExecuteReader() method of the command object loginCommand.

Use parameterised query, which can protect your database from unexpected injections and alse you can handle your datatables most easily and efficiently. Like, You do not insert any special charactor to any field a table directly, but using parameterised query you can do it.

I just tried to modify your codes which can help you.

Dim sqlLoginCode As String = "select * from tblusers where User_username = ? AND User_password = ?"

        Dim loginCommand As New OleDb.OleDbCommand(sqlLoginCode)

        loginCommand.Connection = New OleDb.OleDbConnection(connstring)

        If loginCommand.Connection.State = ConnectionState.Open Then loginCommand.Connection.Close()
        loginCommand.Connection.Open()

        'Adding parameters
        loginCommand.Parameters.AddWithValue("@txtuname", txtUsername.Text)
        loginCommand.Parameters.AddWithValue("@txtpwd", txtPass.Text)

        'Declaring data reader to read table rows
        Dim loginreader As OleDbDataReader = loginCommand.ExecuteReader()

        'Before reading check if reader has any row or not
        'if not pass away.
        'If there is a row do something.
        If loginreader.HasRows() Then

            'read data reader
            loginreader.Read()

            'match with field value
            Select Case loginreader("User_usertype")

                Case "Administrator"

                    MessageBox.Show("Welcome Admin")
                    frmAdmin.Show()
                    Me.Hide()

                Case "Teacher"

                    MessageBox.Show("Welcome Admin")
                    frmTeacherForm.Show()
                    Me.Hide()

            End Select

        Else

            MessageBox.Show("Login Failed!!")
            txtUsername.Clear()
            txtPass.Clear()
            txtUsername.Focus()

        End If