LOGIN: How to change for loop to Sql

[saintrenz]

THIS CODE IS WORKING,, BUT WHEN THE SECOND TIME LOGGING IN IT DOUBLE CHECKED LIKE 2 FORMS OPEN and 4 MESSAGE BOX APPEAR

 Private Sub btnSign_Click(sender As Object, e As EventArgs) Handles btnSign.Click

        Dim connectionString As String
        connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Renz\Documents\Visual Studio 2012\FINAL\Database\AuditDB.mdb"
        sql = "Select ID, LASTNAME, FIRSTNAME, LOGINSTATUS from Users"
        cnn = New OleDbConnection(connectionString)
        Try
            cnn.Open()
            adptr = New OleDbDataAdapter(sql, cnn)
            adptr.Fill(ds)
            For i = 0 To ds.Tables(0).Rows.Count - 1
                'check if username and password is matched'
                If ds.Tables(0).Rows(i).Item(0) = txtID.Text Then
                    MessageBox.Show("UserName Matched")
                    If ds.Tables(0).Rows(i).Item(1) = txtPassword.Text Then
                        MessageBox.Show("Password Matched")
                        'check if admin'
                        If txtPassword.Text = "admin" Then
                            MessageBox.Show("You are now Logged In as Admin.")
                            frmFaculty.Show()
                            Me.Hide()
                        Else 'fill student information'
                            frmStudentIn.lblSI.Text = ds.Tables(0).Rows(i).Item(0)
                            frmStudentIn.lblLN.Text = ds.Tables(0).Rows(i).Item(1)
                            frmStudentIn.lblFN.Text = ds.Tables(0).Rows(i).Item(2)
                            frmStudentIn.lblLS.Text = ds.Tables(0).Rows(i).Item(3)
                            frmStudentOut.lblSI.Text = ds.Tables(0).Rows(i).Item(0)
                            frmStudentOut.lblLN.Text = ds.Tables(0).Rows(i).Item(1)
                            frmStudentOut.lblFN.Text = ds.Tables(0).Rows(i).Item(2)
                            frmStudentOut.lblLS.Text = ds.Tables(0).Rows(i).Item(3)
                            MessageBox.Show("You are now Logged In.")
                            'check if already signed in or not'
                            If ds.Tables(0).Rows(i).Item(3) = "Logged In" Then
                                frmStudentOut.Show()
                                Me.Hide()
                            Else
                                frmStudentIn.Show()
                                Me.Hide()
                            End If
                        End If
                    Else
                        MessageBox.Show("Invalid Password.")
                        MessageBox.Show("Please Try Again." & vbNewLine & "ATTEMPT: " & attempt & " out of 3")
                        attempt = attempt + 1
                    End If
                End If


            Next
            cnn.Close()
        Catch ex As Exception
        End Try

        'log-in attempt 3x fail'
        If attempt > 3 Then
            MessageBox.Show("You have exceeded the number of login attempt." & vbNewLine & "Please Contact the Administrator.")
        End If
    End Sub

[Begginnerdev]

After reading this code, I hope that this system is not being implemented in a corporate environment. This is horribly insecure - using some one's last name as a password will almost guarantee an easy data breach.

As for the code, why are you selected the contents of the entire table?

Just look for the contents by ID.

For example:

'Class wide variable
Dim iAttempts As Integer = 0


If iAttempts > 3 Then
    MsgBox("Login attempts have exceeded 3!")
    Exit Sub
End If

Dim da As New OleDBDataAdapter(New OleDBCommand("Select ID, LASTNAME, FIRSTNAME, LOGINSTATUS from Users WHERE ID='" & txtID.Text & "'",New OleDBConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Renz\Documents\Visual Studio 2012\FINAL\Database\AuditDB.mdb")))
Dim ds As New DatSet

da.Fill(ds,"Login")

If ds.Tables("Login") And ds.Tables("Login").Rows.Count > 0 Then
    If ds.Tables("Login").Rows(0)("LASTNAME") Then
        If txtPassword.Text = "admin" Then
            'Staff
            iAttemtps = 0
        Else
            'Student
            iAttempts = 0
        End If     
    Else
        MsgBox("Password was incorrect!")
        iAttempts += 1
    End If
Else
    MsgBox("Could not find ID!")
    iAttempts += 1
End If

[saintrenz]

it says Error 1 Operator 'And' is not defined for types 'System.Data.DataTable' and 'Boolean'.

[Begginnerdev]

Sorry, I typed the code into the editor.

Try wrapping the first half of the if with a Not IsNothing()

If Not IsNothing(ds.Tables("Login")) And ds.Tables("Login").Rows.Count > 0 Then

[saintrenz]

Thanks btw.. If I added "Password" Field to the database.. what will be the new output??