vb.net - problem in loging in

I use the following code in my login form. but it doesn't ca开发者_StackOverflowre 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.

---

You should generally:

a) Compare usernames in a case-*in*sensitive fashion - you should only have one user with the username admin, however it's spelled. Having usernames that only differ by case can lead to confusion for other users, trying to work out who the real admin is.

b) Store hashes (preferably salted hashes) of passwords in a binary column.

That being said, if you want to force a case-sensitive comparison to occur, you can try using a COLLATE clause:

SELECT * FROM tblStaff WHERE Username = @User AND Password = @Pass COLLATE Latin1_General_CS_AS

The above only applies the collate clause to the password comparison (in this case, we're forcing Case Sensitive (CS) and Accent Sensitive (AS)) - you'd need to duplicate it for the username column (not recommended, see (a) above).

---

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)