CODEWITHSUNDEEP
c#sqlwinforms
frionx
frionx

Reputation: 15

C# SQL SERVER - Cannot convert nvarchar to text?

I have a problem with a huge project that I am working on. I never had this problem before but it started occurring today. Any fixes?

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

I don't understand what It means. I don't want to have an sql injection so I am using parameters.. But it still gives errors.

Please ask me if you need me to describe something... This is like my 5th post.


            using (SqlCommand StrQuer = new SqlCommand("SELECT * FROM login WHERE username=@userid AND password=@password", connection))
            {
                StrQuer.Parameters.AddWithValue("@userid", username.Text.ToLower().Trim());
                StrQuer.Parameters.AddWithValue("@password", obf.Obfuscate(password.Text).ToString());
                SqlDataReader dr = StrQuer.ExecuteReader();
                if (dr.HasRows)
                {
                    Properties.Settings.Default.username = username.Text.ToLower();
                    Properties.Settings.Default.username = obf.Obfuscate(password.Text);
                    RegistryKey key = Registry.CurrentUser.CreateSubKey(@"SOFTWARE\howcoolitisX");
                    key.SetValue("username", username.Text.ToLower());
                    key.SetValue("password", obf.Obfuscate(password.Text));
                    key.Close();
                    Form executor = new Loader();
                    executor.Show();
                    this.Hide();
                }
            }

Upvotes: 0

Views: 2723

Answers (1)

Eugene Podskal
Eugene Podskal

Reputation: 10401

Well, first I'd say that you should probably take a look at how can passwords be stored securely.

And as for the issue at hand there are multiple solutions

  1. Either cast the parameters in SQL
new SqlCommand("SELECT * FROM login WHERE username= CAST(@userid as text) AND password= CAST(@@passwordas text)", connection)
  1. Or use Parameters.Add instead of Parameters.AddWithValue and set the parameter type explicitly.
StrQuer.Parameters.Add("@userid", SqlDbType.Text);
command.Parameters["@userid"].Value = username.Text.ToLower().Trim();
  1. Or if you can easily change the database then by all means do as Crowcoder suggested and change columns to nvarchar as text is an obsolete datatype

IMPORTANT! ntext, text, and image data types will be removed in a future version of SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.

Upvotes: 3

Related Questions