upper and lower case

[plusplus]

I'm comparing a text box to a field in my database
I want it to give back true only if upper\lower case match and not always when the words are the same. Can you help me?

[binoj_daniel]

SO you mean to say that the function should treat the upper nad lower case word as two seperate words? Can you be nore specific. I can help you if you explain a bit.

[plusplus]

I have an sqlstatement
Select * from tblusers where name = txtname
This sqlstatement is not case sensitive, it gives me a match even when
name = Martin and txtname = martin. I don't want it to return a match unless it's exactly the same.

[binoj_daniel]

well the ideal way to handle this is follow a uniform data storage in the database, that will give more control over the database.
You should always follow proper casing when storing the data back to the DB.

If you want to be able to compare two case-insensitive strings and have the comparison be false when the two strings are the same except for the case of the characters then you need to use the COLLATE clause when comparing the two strings.

Do something like this
Select * from tblusers where name
COLLATE SQL_Latin1_General_CP1_CI_AS
= txtname

[plusplus]

Can you give me the exact syntax, I tried
adoLogin.RecordSource = "Select * from tbluser where Name COLLATE SQL_Latin1_General_CP1_CI_AS ='" & txtUsername & "'"

and it gives me an error

[binoj_daniel]

Use this query and it should definitely work

Select * from tblusers where name COLLATE Latin1_General_CS_AS = 'Martin'

WHich version of SQL are you using?

[plusplus]

Still gives me an error, no idea which version of sql I have, where can I look that up?

[binoj_daniel]

You can check the SQL version by opening Query Analyzer and click on "Help" --> "About"

Also send me the error message.

[plusplus]

Error message : ado failed, error unknown

where do I open query analyzer?

[binoj_daniel]

Just a flash back. Are you using MsAccess Database? You can find SQL Query Analyzer only if you have SQL Server installed on your machine. Iam guessing at this point that you dont have SQL server installed.

[plusplus]

found it, not sure which of the following describes the version, either
sql 800.194 or
5.1.2600
and yes I am using msaccess database and all my sql statements work, even this one worked, my only problem in this case is it is not case sensitive

[binoj_daniel]

ok! for ms access database the previous SQL COLLATE statement wont work, that's why you were getting the error.

For MS Access you have to do like this:

Select * from tblusers where name = 'Martin' and StrComp(tblsusers.name, 'Martin', 0) = 0

[binoj_daniel]

SELECT *
FROM tblusers
WHERE (name = 'Martin' and StrComp(tblusers.name, 'Martin', 0) = 0);

Note: You can replace the parameters with any variable instead of giving constant values to compare.

[plusplus]

Here is what I did
Select * from tblusers where name = txtuser
and afterwards I have an if statement
if strcomp(ado.recordset("name"),txtuser,0)=0

It works. I could not get the syntax right to have the strcomp within the select statement, so I did it this way. good enough for me. Thanks for your help and time

[binoj_daniel]

ok! good atleast you got a work around. But that should have worked in one statement as I would like the program to be as light as possible.
Let me know.. You can register on my site for more tech artciles. click here