Select statement no case sensitive

C

Chong

Well-known member
Joined
Apr 4, 2003
Messages
79
Can anyone tell me why my select statement is not case sensitve regarding the usrPassword=strPassword? Below are my codes:

Dim strUser, strPassword As String
Dim recLength, recCounter As Integer
Dim usrDataset As New DataSet()
strUser = txtUserName.Text
strPassword = txtPassword.Text
Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:\...\schUser.mdb;"
Dim cn As New OleDbConnection(strConn)
cn.Open()
Dim cmd As OleDbCommand = cn.CreateCommand()
Dim strSQL As String = "SELECT * FROM USERS WHERE usrName like " & "" & strUser & "" & " AND usrPassword=" & "" & strPassword & ""
cmd.CommandText = strSQL
Dim intRecordMatched As String = cmd.ExecuteScalar
If intRecordMatched <> Nothing Then
cn.Close()
Me.Close()
Exit Sub
Else
MsgBox("Incorrect Password or User Name", MsgBoxStyle.Critical)
End If
Click to expand...
Please help.

Many thanks in advance.

Chong

:confused:
 
With a default installation, all columns in SQL Server are not case-sensitive. This is set by Collation and can be overridden.

For instance, on your USERS table you probably have the collation set to SQL_Latin1_General_CP1_CI_AS. Check it by using Enterprise Manager, right click on the table and select Design. The last property for a column will be the collation. All values that have "...CI..." are case-insensitive. So change
SQL_Latin1_General_CP1_CI_AS
to
SQL_Latin1_General_CP1_CS_AS

and your usrPassword column will be case-sensitive.

-Nerseus
 
Im not sure if you can change the Collation (or anything equivalent) in Access, but you can use the StrComp function to do binary compares (case-sensitive):
Code: 
-- Below will match "hello" but not "Hello" or "heLLO"
SELECT * FROM Table1 where StrComp(test, hello, 0) = 0

-Nerseus
 
First, thanks for responding. So for my select statment it will be something like this?

Dim strSQL As String = "SELECT * FROM USERS WHERE usrName like " & "" & strUser & "" & " AND StrComp(usrPassword, strPassword,0)
Click to expand...

The concern I have is because my strPassword is a variable that hold a string so how would I specify that in the StrComp function?

Thanks!

Chong
 
Code: 
Dim strPassword As String = "Test"

Dim strSQL As String = "SELECT * FROM USERS WHERE usrName like " & "" & strUser & "" & " AND StrComp(usrPassword, " & strPassword & ",0) = 0"

Andy
 
This wont work!

"WHERE StrComp(STATUS, " + CB_study_status.Text + ",0) = 0"

I keep getting a error when I try to fill my dataset.
 
The function StrConv doesnt work in SQL Server. As stated above, youll have to change the collation in SQL Server as by default, all columns are case-insensitive. I cant think of a way to do a case-sensitive search if you want the column to remain case-insenstive.

-Nerseus
 
Looking at the MS SQL Server, I can not find the Collation. After opening up the table in design mode, its not one of the properties for the columns. Is there a way I can be case-sensitive on a select statment without iterating through the Dataset after the fact and deleting the unwanted information? Any ideas?
 
Back
Top