Error converting varchar value to integer

Question

Error converting varchar value to integer

Navigation

#1 by (4 votes)

5

When 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.

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

If I do the same operation by the bank in this way, it works:

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

Why does this happen and how do I fix it?

sql sql-server c# asp.net-core

asked by anonymous 10.05.2018 / 18:47

1 answer

With grouping a list in javascript and calculating the subtotals? Query to find if value is between "1 | 3"

score 4 · Accepted Answer

By not specifying the parameter's DbType, SqlCommand is interpreting it as integer, and thus the | character in SQLServer is a OR bitwise operator.

If you run the following command:

select 22245089|22245090;

The return is:

22245091 (integer) (see SQLFiddle )

Why: (Comparison OU bit by bit)

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 look like this:

UPDATE Aula SET WHATEVER = WHATEVER WHERE ID_AULA = 22245091

so it fails.

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 should be:

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

About SqlParameter:

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

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

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