vb.net - problem in loging in

Question

I use the following code in my login form. but it doesn't care about cases. Just like admin or Admin or whether ADmin as username can either login to my system when the real one is admin. these are my code:

sql = "SELECT * FROM tblStaff WHERE Username = @User AND Password = @Pass"
 myCommand = New SqlCommand(sql, myConnection)
 myCommand.Parameters.AddWithValue("@User", txtUser.Text)
 myCommand.Parameters.AddWithValue("@Pass", txtPassword.Text)
 myCommand.ExecuteReader()

..........

Please help.

Answer 1

If you want to be case insensitive then the following should work for you:

sql = "SELECT * FROM tblStaff WHERE UPPER(Username) = UPPER(@User) AND Password = @Pass

EDIT:

If you want the username to be case insensitive and the password to be case sensitive then the following should work for you:

SELECT * FROM Users WHERE Username = @User AND (Password = @Pass COLLATE Latin1_General_CS_AS)

If you want the username and password to be case sensitive then try the following:

SELECT * FROM Users WHERE (Username = @User COLLATE Latin1_General_CS_AS) AND (Password = @Pass COLLATE Latin1_General_CS_AS)