Vb.net [2008] DataSelect using COLLATE (case sensitve query).

[rw409168]

Greetings,

I am designing a login screen and require to count the rows where the login and password are the same.

The password is case sensitive, the login isn't as selected from a pre-populated combo box and can never be changed.

I know I can set collation at table creation time but how do I apply it to the sql statement below?

At the moment I get the syntax error Syntax error: Missing operand after'COLLATE' operator.

Dim validuser As Integer = usersDS.Tables(0).Select("Name = '" & cboLogin.SelectedValue.ToString & "' and password COLLATE SQL_Latin1_General_CP1_CI_AS = '" & txtPassword.Text & "' ").Count

I have done other login screens running commands against a connection, I was curious to see if the same could be done using a dataset.

I have never had case-sensitive passwords before so am new to COLLATE.

Apologoies if this is the wrong section as is ADO.net/T-SQL based.


Thanks
Rob

 

[RiverGuy]

Your code is not querying SQL Server. It's looking for DataRows in your DataTable. And your DataTable doesn't understand T-SQL or the COLLATE statement.

Your best bet is to query SQL Server directly. I'm not sure why you are bringing in rows which might not match your user. If User A tries to log on to your app, you should query for User A, and return only rows which are applicable to User A. You should not return a DataTable of all of your users.

 

[rw409168]

It's a count so if rows is 0 they don't exist else 1 they do.

I understand Im querying a dataset, the code works without the COLLATE statement and returns 0 or 1, HOWEVER the clause then isn't case sensitive.

I assumed the SQL query in the dataset used T-SQL syntax.

Is COLLATE the only way to peform case sensitve queries?

Thanks
Rob

 

[RiverGuy]

Is COLLATE the only way to peform case sensitve queries?

No.

1. Strings in .Net are case sensitive. So, if you loop through your DataTable, and extract some of the values as strings to compare against your TextBox, then you'll have a case-sensitive comparison.

2. There is a .CaseSensitive property for your DataTable which will affect the filters your pass into the .Select function. But this would affect the whole table, and there is no equivalent I know of for a single column.

3. As I said above, offload this to the database server. It would be considered insecure to fill up a DataTable of Users with the usernames and passwords for all of your users. If you do this, this means that each client application (assuming Windows Forms) will have an in-memory datastore of every other users' name and passwords. So you want to create a stored procedure to query SQL Server itself. Something like this:

CREATE PROCEDURE p_CheckAuthorization
@UserName VARCHAR(100),
@Password VARCHAR(100)
AS
IF EXISTS (SELECT * FROM UserTable WHERE UserName = @UserName AND Password COLLATE SQL_Latin1_General_CP1_CS_AS = @Password)
BEGIN
     SELECT 1 --User is found
END
ELSE
BEGIN
     SELECT 0 --User is not found
END

Then, when you call this stored procedure, you don't even need to use a DataTable if you're not interested in returning other information. You can simply use CommandObject.ExecuteScalar() to return a single row, single column value, which in this case would either be 0 or 1.

 

[gmmastros]

I agree with RiverGuy. Go back to the database to do the comparison. However, the code, as posted, will perform an accent sensitive, but case insensitive comparison. For case sensitive comparisons, I usually use a binary collation.

CREATE PROCEDURE p_CheckAuthorization
@UserName VARCHAR(100),
@Password VARCHAR(100)
AS
IF EXISTS (SELECT * FROM UserTable WHERE UserName = @UserName AND Password [!]COLLATE Latin1_General_BIN[/!] = @Password)
BEGIN
     SELECT 1 --User is found
END
ELSE
BEGIN
     SELECT 0 --User is not found
END

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[RiverGuy]

However, the code, as posted, will perform an accent sensitive, but case insensitive comparison

George, are you sure about that? Run this:

SELECT *
FROM fn_helpcollations()
WHERE [name] = 'SQL_Latin1_General_CP1_CS_AS'

 

[gmmastros]

Sorry. I was looking at the collation posted by rw409168 in the original question. It is a case insensitive collation. Clearly you caught it and changed it to a case sensitive collation. Anyway.... everyone.... please feel free to continue ignoring me. Sorry.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[rw409168]

Thanks for the replies I will try some code out.

The application is for some course work which states "DO NOT USE" MS SQL Server 2005 for anything other than storage of data.

So I can't use a stored procedure but will do this via SQLCommand.

Sorry I didn't mention that in the initial post, least I know It can't be done via a dataset.

Thanks again.