Search code examples
ms-accessoledbcase-insensitiveoledbconnection

Query comparing values for case sensitive

I'm trying to compare the values for my password using this query but When I try using COLLATE SQL_Latin1_General_CP1_CI_AS it doesn't work.

Please assist me in this problem

Error Message

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll Additional information: IErrorInfo.GetDescription failed with E_FAIL(0x80004005).

My Codes

OleDbConnection con = new OleDbConnection();
con.ConnectionString =     ConfigurationManager.ConnectionStrings["Connection"].ToString();
con.Open();
OleDbCommand cmd = new OleDbCommand();
cmd.CommandText = "SELECT UserName, Password, ID FROM AdminLogin WHERE UserName='" + txtUserName.Text + "' AND Password='" + txtPassword.Password + "' COLLATE SQL_Latin1_General_CP1_CI_AS";
cmd.Connection = con;
OleDbDataReader rd = cmd.ExecuteReader();
Solution

  • You can use this option that will also increase security for SQL Injection 

    cmd.CommandText = "SELECT UserName, Password, ID FROM AdminLogin WHERE StrComp(UserName,'" + txtUserName.Text + "',0)=0 AND StrComp(Password,'" + txtPassword.Password + "',0)=0 ";