Select statement no case sensitive

[Chong]

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

Please help.

 

Many thanks in advance.

 

Chong

 

:confused:

[Nerseus]

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

[Derek Stone]

Will that work for Access as well, Nerseus? I've never bothered to try.

[Nerseus]

I'm 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):

-- Below will match "hello" but not "Hello" or "heLLO"
SELECT * FROM Table1 where StrComp(test, 'hello', 0) = 0

 

-Nerseus

[Chong]

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)

 

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

[a_jam_sandwich]

Dim strPassword As String = "Test"

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

 

Andy

[Chong]

Thanks, a_jam_sandwich . You code you showed me works!

 

Chong

[Enigma151]

This won't work!

 

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

 

I keep getting a error when I try to fill my dataset.

[Nerseus]

Are you using Access?

 

-Nerseus

[Enigma151]

I am connecting to a MS SQL Database.

[Nerseus]

The function StrConv doesn't work in SQL Server. As stated above, you'll have to change the collation in SQL Server as by default, all columns are case-insensitive. I can't think of a way to do a case-sensitive search if you want the column to remain case-insenstive.

 

-Nerseus

[Enigma151]

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?