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?

Re: upper and lower case 80 80

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.

Re: upper and lower case 80 80

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.

Re: upper and lower case 80 80

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

Re: upper and lower case 80 80

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

Re: upper and lower case 80 80

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?

Re: upper and lower case 80 80

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

Re: upper and lower case 80 80

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

Also send me the error message.

Re: upper and lower case 80 80

Error message : ado failed, error unknown

where do I open query analyzer?

Re: upper and lower case 80 80

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.

Re: upper and lower case 80 80

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

Re: upper and lower case 80 80

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

Re: upper and lower case 80 80

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.

Re: upper and lower case 80 80

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

Re: upper and lower case 80 80

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.18 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.