0

This code was working perfectly fine, today I tried to execute it, and now I am facing this exception:

I have this SQL error :

Exception Details: System.Data.SqlClient.SqlException: The data types text and nvarchar are incompatible in the equal to operator.

My code is:

  protected Boolean is_valid(string a, string b) {

   SqlConnection connection = getConnection();
    SqlCommand cmd = new SqlCommand();
    cmd.Connection = connection;

    cmd.CommandText = ("SELECT [Email_id] FROM [logintable] WHERE [Email_id] = @a AND [Password]=@b ");



    cmd.Parameters.Add("a", SqlDbType.NVarChar).Value = a;
    cmd.Parameters.Add("b", SqlDbType.NVarChar).Value = b;




    object obj = cmd.ExecuteScalar();
    connection.Close();
       ...
     }`

My DB fields type are nvarchar(50). logintable its just a simple table, i am using SQL express with VS. It has 3 coloumns, with nvarchar(50) as its datatype. and it doesnt allow nulls.

I have tried to resolve this error by changing nvarchar(50) data type in db to nvarchar(MAX). but it didn't make a difference.

Also, I have tried to write my query like this:

cmd.CommandText = ("SELECT [Email_id] FROM [logintable] WHERE Email_id like @a AND Password like @b ");

but, it didnt resolve or make any difference.Please help? Thanks in advance.

RogueSpear00
  • 619
  • 2
  • 9
  • 24
Loony.malik
  • 23
  • 1
  • 1
  • 9
  • Take a look http://stackoverflow.com/questions/2761901/the-data-types-text-and-nvarchar-are-incompatible-in-the-equal-to-operator-in and http://stackoverflow.com/questions/2726649/the-data-types-text-and-nvarchar-are-incompatible-in-the-equal-to-operator – Soner Gönül May 22 '13 at 14:07
  • What is the schema of `logintable`? Does it have any `TEXT` columns? You don't open your connection and you parameter names are wrong. – Jodrell May 22 '13 at 14:11
  • 1
    sounds like you are not looking at the correct field or are connecting to a different database that has the db fields of type `TEXT`. are you sure sure sure they are varchar? – Hogan May 22 '13 at 14:11
  • its just a simple table, i am using SQL express with VS. It has 3 coloumns, with nvarchar(50) as its datatype. and it doesnt allow nulls. – Loony.malik May 22 '13 at 14:14
  • yes, it was working fine previously, i didnt even change anything, only tried to execute it. @Hogan – Loony.malik May 22 '13 at 14:15
  • better if you put it in the question. – Jodrell May 22 '13 at 14:15
  • Please tell me you don't have your passwords stored as plain text in your database? – Joel Coehoorn May 22 '13 at 14:40
  • lol for the testing purposes they are stored as plain text. type nvarchar. @joel but thats not relevant here. – Loony.malik May 22 '13 at 15:32
  • Thanks everyone, apparently my page was not redirecting properly. its working fine now.! – Loony.malik May 22 '13 at 15:36
  • 1
    Security issues are always relevant. – Joel Coehoorn May 22 '13 at 15:48
  • @JoelCoehoorn - I would say they are eventually relevant -- they become relevant at the point your site / application is hacked. (If you don't make sure they are relevant prior). – Hogan May 22 '13 at 16:19
  • thanx for suggestions! ill handle that! – Loony.malik May 22 '13 at 16:55

1 Answers1

0

Make sure your "a" and "b" input variables are under 4000 characters in length. Variables over 4000 characters are automatically converted to text variables by your ado.net driver.

Try changing your code to this:

protected Boolean is_valid(string a, string b) {

    if (string.IsNullOrEmpty(a) || a.Length > 50) {
        throw new ArgumentException("a is invalid");
    }

    if (string.IsNullOrEmpty(b) || b.Length > 50) {
        throw new ArgumentException("b is invalid");
    }

    // Do Work

}
bastos.sergio
  • 6,684
  • 4
  • 26
  • 36