Comparing Uppercase and Lowercase characters(Login Form)

[mbowenitj]

Hi guys,

I've created a login form which works fine, i need assistance in comparing uppercase and lowercase input from a login form. Whenever the password is entered, the system has to compare uppercase and lowercase characters stored in the data..following is my code:

'Logic for login...
Dim conn As New OleDb.OleDbConnection
Dim cmd As OleDb.OleDbCommand
Dim selectQuery As String
Dim dr As OleDb.OleDbDataReader
Dim userProfile As String
Dim MainrMenur As New frmMainrMenur
Try
    conn = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Video Store\Video Store\DvDSystem.mdb")
    conn.Open()
    selectQuery = "SELECT * FROM Userr WHERE [UserID] = '" & txtUserName.Text & "' AND [User Password] = '" & txtUserPassword.Text & "'"
    cmd = New OleDb.OleDbCommand(selectQuery, conn)
    dr = cmd.ExecuteReader()
    'conn.Open()
    'if successfull then log in
    If dr.Read() Then
        'Get user profile
        userProfile = dr.GetString(3)
        keptProfile = userProfile
        MainrMenur.Show()
        If keptProfile = "admin" Then
            MainrMenur.Show()
        End If
    ElseIf txtUserName.Text <> "" Then
        If txtUserPassword.Text <> "" Then
            MessageBox.Show("Wrong login, please try again ...", "Login Denied", MessageBoxButtons.OK)
        Else
            MessageBox.Show("Please enter password", "Login Error", MessageBoxButtons.OK)
            txtUserPassword.Focus()
        End If
    Else
        MessageBox.Show("Please enter user id", "Login Error", MessageBoxButtons.OK)
        txtUserName.Focus()
    End If
    'Clear password field after login
    txtUserPassword.Clear()
    dr.Close()
Catch ex As Exception
    MessageBox.Show("Please exit system and login again.", "Login Error", MessageBoxButtons.OK)
End Try
conn.Close()

[Reverend Jim]

I'm not sure I understand what you are asking but if you want to do a case sensitive selection on the database you can do

SELECT *
  FROM Userr
 WHERE [UserID] COLLATE Latin1_General_CS_AS = 'username'
   AND [User Password] COLLATE Latin1_General_CS_AS = 'password'

If you are only interested in whether the case sensitive username and password are present then just do SELECT COUNT(*) rather than SELECT *

[mbowenitj]

i tried the code nd still the password goes through even when i entered the password with either lowercase and uppercase. I need my code to check for case sensitive selection only in the password field.

[Reverend Jim]

I don't have Access installed so I can't test this but try

cmd.CommandText = "SELECT COUNT(*)" &
                  "  FROM Userr" &
                  " WHERE StrCmp([UserID],?,0) = 0" &
                  "   AND StrCmp([User Password],?,0) = 0"

cmd.Parameters.AddWithValue("@parm", txtUserName.Text)
cmd.Parameters.AddWithValue("@parm", txtUserPassWord.Text)

con.Open()

Dim numrec As Integer = cmd.ExecuteNonQuery()
MsgBox(If(numrec = 1, "match", "no match"))

con.Close()

[mbowenitj]

still goes through with either lowercase and uppercase

[mbowenitj]

Managed to get it right...I used txtUserPassword.text.ToString method.

thanx for all your efforts champ.