Error converting varchar to integer

Question

Error converting varchar to integer

Asked 6 years, 1 month ago

Viewed 157 times

5

By performing the following UPDATE through my API (.NET Core):

UPDATE Aula
SET WHATEVER = WHATEVER
WHERE ID_AULA = @examID

Code:

string query = builder
    .AppendLine("UPDATE Aula")
    .AppendLine("SET WHATEVER = WHATEVER")
    .AppendLine("WHERE ID_AULA = @examID").ToString();

SqlCommand command = new SqlCommand(query, sqlConnection);

command.Parameters.AddWithValue("@examID", item.ExamID);

sqlConnection.Open();
command.ExecuteNonQuery();

I get the following error: Conversion failed when converting the varchar value '22234390|22234391' to data type int.

Being that my field ID_AULA is a field varchar and the parameter @examID comes from a property string.

If I do the same thing for the bank that way, it works:

UPDATE Aula
SET WHATEVER = WHATEVER
WHERE ID_AULA = '22245089|22245090'

Why it happens and how to correct?

Put a larger snippet of code please

– Rovann Linhalis

2018/05/10 at 17:00
@Rovannlinhalis made

– perozzo

2018/05/10 at 17:04
you speak of the field ID_AULA, but in your code you use the field ID_AULA_DETRAN, you are sure it is the same field?

– Júlio Neto

2018/05/10 at 17:15
@Júlioneto was my mistake when omitting the name of haha fields, but yes, I am using the right field.

– perozzo

2018/05/10 at 17:16
how is the statement of item.ExamID ?

– Rovann Linhalis

2018/05/10 at 17:19
@Rovannlinhalis a property string normal public string Examid {get;set;}

– perozzo

2018/05/10 at 17:22
2

tried to declare the type in Sqlparameter? command.Parameters.Add(new SqlParameter { ParameterName = "@examID", DbType = DbType.String, Value = item.ExamID });

– Leandro Angelo

2018/05/10 at 17:25
I think it might be what Leandro Angelo said. Related: https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/configuring-parameters-and-parameter-data-types

– Rovann Linhalis

2018/05/10 at 17:31
@Leandroangelo thank you very much!

– perozzo

2018/05/10 at 20:14

Show 4 more comments

1 answer

Browser other questions tagged c# sql sql-server asp.net-core

You are not signed in. Login or sign up in order to post.

by Rovann Linhalis • **14,881** points · Answer 1 · 2018-05-10T17:42:05+00:00

By not specifying the Dbtype of the parameter, SqlCommand is interpreting it as whole, and thus the character | in the SQLServer is an operator OR bitwise.

If you execute the following command:

select 22245089|22245090;

The return is:

22245091 (whole) (see in Sqlfiddle)

Because: (Bit by bit comparison)

0001010100110110111011100001 = 22245089
0001010100110110111011100010 = 22245090
=======================================
0001010100110110111011100011 = 22245091

Then for the query:

UPDATE Aula SET WHATEVER = WHATEVER WHERE ID_AULA = @examID

The resulting query would be:

UPDATE Aula SET WHATEVER = WHATEVER WHERE ID_AULA = 22245089|22245090

that processed the operator, would be like this:

UPDATE Aula SET WHATEVER = WHATEVER WHERE ID_AULA = 22245091

therefore the failure.

Do as Leandro said, and specify the format of the parameter:

command.Parameters.Add(new SqlParameter { ParameterName = "@examID", DbType = DbType.String, Value = item.ExamID });

The resulting query must be:

UPDATE Aula SET WHATEVER = WHATEVER WHERE ID_AULA = '22245089|22245090'

About the Sqlparameter:

According to the documentation, the builder expects a Object, and when the type of the parameter is not specified, it will be inferred by the type of the Object informed.

My opinion: If the value of the object only contains numbers and an operator, it might be understanding this as integer, you could try something like:

command.Parameters.AddWithValue("@examID","'"+ item.ExamID + "'");