VB6 login password

[VirtualBaby]

Dear VB gurus,
I'm using VB6 and SQL2005 Express.
I've a SQL table with 2 fields and records below:
UserName1 UCSIPassword1
--------------- ---------------------
z z
bird bird
abc abc

I've created a login VB6 interface, connected to the SQL and have put in
the correct data source and data field for the two text boxes. However,
with the following code, only first record can allow a login. Please advice what mistake
I have done in the coding:

Private Sub Form_Load()
Form6.txtUserName1.Text = ""
Form6.txtpw1.Text = ""
End Sub

Private Sub txtpw1_Change()
'Set cmdok1 as default after Password and UserName entered
cmdok1.Default = True

End Sub

Private Sub cmdok1_Click()
Dim Response As Integer
'Dim UserName As String
'Dim Password As String

If (txtUserName1.Text = Form7.DBpassword2.Recordset.Fields("UserName1")) Then
If (txtpw1.Text = Form7.DBpassword2.Recordset.Fields("UCSIPassword1")) Then
'Login Succeeded
Load Form3
Form3.Show
Unload Me

Else
'Wrong password cannot login to system
Response = MsgBox("Wrong password, try again!", vbOKOnly + vbCritical + vbApplicationModal, "System Security")
txtpw1.SetFocus
SendKeys "{Home}+{End}"
End If
Else
'Wrong UserName cannot login to system
Response = MsgBox("Wrong username, try again!", vbOKOnly + vbCritical + vbApplicationModal, "System Security")
txtUserName1.SetFocus
SendKeys "{Home}+{End}"
End If

End Sub

Thank you

[GremlinSA]

Loop through the record set until you find the correct username and then check the password...

[VirtualBaby]

Dear GremlinSA,
I'm not expert in programming & hv spent hours trying to correct this code.
Can you pls advice me where is the right place to put the Loop?
btw, do I need to include .MoveNext anywhere in the code to instruct the SQL pointer to
move to the next record? If yes, where is the right place to code it?
Your help is much appreciated. TQ.

[GremlinSA]

while not  Form7.DBpassword2.Recordset.EOF
    If (txtUserName1.Text = Form7.DBpassword2.Recordset.Fields("UserName1")) Then
    If (txtpw1.Text = Form7.DBpassword2.Recordset.Fields("UCSIPassword1")) Then
    'Login Succeeded
        Load Form3
        Form3.Show
        Unload Me
    Else
    'Wrong password cannot login to system
        Response = MsgBox("Wrong password, try again!", vbOKOnly + vbCritical + vbApplicationModal, "System Security")
        txtpw1.SetFocus
        SendKeys "{Home}+{End}"
    End If
    End If
    Form7.DBpassword2.Recordset.movenext
Wend

[VirtualBaby]

Thanks for your reply. All usernames and passwords can log in now.
However, when a wrong password is used, the prompt message doesn't appear.
btw, how to also include the prompt message for wrong user name in the code.
Pls help. TQ.

[VirtualBaby]

Okay, i got it!!! I just added .Refresh for both the Form Load and cmdok1_Click().
But now, where do I add in the wrong username prompt message into the code ?
Pls help. TQ.

Private Sub Form_Load()
Form7.DBpassword2.Refresh
Form6.txtUserName1.Text = ""
Form6.txtpw1.Text = ""
End Sub

Private Sub cmdok1_Click()
Form7.DBpassword2.Refresh
Dim Response As Integer
While Not Form7.DBpassword2.Recordset.EOF

If (txtUserName1.Text = Form7.DBpassword2.Recordset.Fields("UserName1")) Then
If (txtpw1.Text = Form7.DBpassword2.Recordset.Fields("UCSIPassword1")) Then
'Login Succeeded
Load Form3
Form3.Show
Unload Me
Else
'Wrong password cannot login to system
Response = MsgBox("Wrong password, try again!", vbOKOnly + vbCritical + vbApplicationModal, "System Security")
txtpw1.SetFocus
SendKeys "{Home}+{End}"
End If
End If
Form7.DBpassword2.Recordset.MoveNext
Wend

'If (txtpw1.Text <> Form7.DBpassword2.Recordset.Fields("UserName1")) Then
'Wrong UserName cannot login to system
'Response = MsgBox("Wrong username, try again!", vbOKOnly + vbCritical + vbApplicationModal, "System Security")
'txtUserName1.SetFocus
'SendKeys "{Home}+{End}"
'Form7.DBpassword2.Recordset.MoveNext
'End If

End Sub

[dglienna]

Better check your logic, and read it again...

'If (txtpw1.Text <> Form7.DBpassword2.Recordset.Fields("UserName1")) Then

if pw = un?

[GremlinSA]

Well if the user successfuly logs in we can add

Dim Logedin as Boolean

'Login Succeeded
Load Form3
Form3.Show
Unload Me
Logedin = true

If Not Logedin then
    'invalid username
end if

[DataMiser]

Looping through the recordset will work but I would use a query by user id instead and then if found compare the password.

In a small database the looping method would be ok but in a very large one the query would be much faster.

Something like "Select UserPassword from tblUsers where UserID='" & txtUserID.Text & "'"

The recordset will be empty if a bad user name is given and will return the password if the correct username is entered, should be fast no matter if the database has 1 or 1,000,000 users

[GremlinSA]

Looping through the recordset will work but I would use a query by user id instead and then if found compare the password.

In a small database the looping method would be ok but in a very large one the query would be much faster.

Something like "Select UserPassword from tblUsers where UserID='" & txtUserID.Text & "'"

The recordset will be empty if a bad user name is given and will return the password if the correct username is entered, should be fast no matter if the database has 1 or 1,000,000 users

....

So true...

However looking at the posted code, It appeared that the OP is Using Bound data recordsets.. and not SQL queries.. Solution was provided that supports the Provided info...

I Too much prefer the SQL Query method...

[VirtualBaby]

Thank you, VB gurus. Your advice has been helpful.
The While...Wend loop will make sure every data in database is checked.
I hv been trying to workout but I'm still having problems not sure where to place the wrong username prompt message. The idea is to show the user when a wrong password has been used and when a wrong username has been used.
Where do I best place this code below?

Else
'Wrong UserName cannot login to system
'Response = MsgBox("Wrong username, try again!", vbOKOnly + vbCritical + vbApplicationModal, "System Security")
'txtUserName1.SetFocus
'SendKeys "{Home}+{End}"

If I place it after the wrong password prompt message and within the While...Wend loop, it still gets messy.
Pls help. TQ

[dglienna]

In that case, create a BOOLEAN variable that you can use in the WHILE LOOP.

While UserFound AND txtUser=...

then, when it is the wrong user, set UserFound to FALSE, and then you will exit the WHILE loop

[GremlinSA]

Thank you, VB gurus. Your advice has been helpful.
The While...Wend loop will make sure every data in database is checked.
I hv been trying to workout but I'm still having problems not sure where to place the wrong username prompt message. The idea is to show the user when a wrong password has been used and when a wrong username has been used.

Post #8 ...

Dim Logedin as Boolean

'Login Succeeded
Load Form3
Form3.Show
Unload Me
Logedin = true

If Not Logedin then
    'invalid username
end if

But I guess i have to spoon feed...

Dim Logedin as Boolean

while not (Form7.DBpassword2.Recordset.EOF or Logedin)
    If (txtUserName1.Text = Form7.DBpassword2.Recordset.Fields("UserName1")) Then
    If (txtpw1.Text = Form7.DBpassword2.Recordset.Fields("UCSIPassword1")) Then
    'Login Succeeded
        Load Form3
        Form3.Show
        Unload Me
        Logedin = true
    Else
    'Wrong password cannot login to system
        Response = MsgBox("Wrong password, try again!", vbOKOnly + vbCritical + vbApplicationModal, "System Security")
        txtpw1.SetFocus
        SendKeys "{Home}+{End}"
    End If
    End If
    Form7.DBpassword2.Recordset.movenext
Wend

If Not Logedin then
    'Wrong UserName cannot login to system
    Response = MsgBox("Wrong username, try again!", vbOKOnly + vbCritical + vbApplicationModal, "System Security")
    txtUserName1.SetFocus
    SendKeys "{Home}+{End}"
end if

[DataMiser]

I would avoid the send keys and use the select methods instead

Text1.SelStart = 0
Text1.SelLength = Len(Text1.Text)

[VirtualBaby]

Thank you, GremlinSA and Datamiser.
Now it works very well.
I have added 'Exit Sub' after the wrong password prompt message block.
Or else the wrong username msg will prompt after the wrong password prompt even though the correct username is used.
Learning is fun. TQ