System.Data.SqlClient.SqlException: Conversion failed when converting the varchar value to int

Question

System.Data.SqlClient.SqlException: Conversion failed when converting the varchar value to int

Navigation

#1 by (0 votes)
#2 by (0 votes)

1

I'm getting this exception in C # when I try to read SQL data. I am having doubts if this error is problem with my SQL or in C #.

My SQL code that does the conversion is this:

'$
(SELECT ORDEM 
                FROM TABELA UP (NOLOCK) 
                    WHERE UP.LAYOUT = r.LAYOUT
                    AND TIPOUSUARIO =CASE
                                        WHEN TIPOUSUARIO = 2 THEN 'A'
                                        WHEN TIPOUSUARIO = 3 THEN 'P'
                                        END
                    AND CODUSUARIO = @CODUSUARIO)
            , r.IDSQL+100) AS ORDER

$

And the code in C # that reads is this:

using (SqlConnection connection = new SqlConnection(WebConfig.ConnectionString))
            {
                connection.Open();

                using (SqlCommand command = connection.CreateCommand())
                {
                    command.CommandType = CommandType.StoredProcedure;
                    command.CommandText = @"sp_vm_getUserItems";
                    command.Parameters.AddWithValue("@...", context.UserId);
                    command.Parameters.AddWithValue("@USERTYPE", (int)context.UserType);
                    command.Parameters.AddWithValue("@...", context.AffiliateId);
                    command.Parameters.AddWithValue("@..", context.CorporateId);
                    command.Parameters.AddWithValue("@...", context.CourseId);
                    command.Parameters.AddWithValue("@..", context.SchoolYearId);
                    command.Parameters.AddWithValue("@...", context.LevelEducationId);
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                            result.Add(new ReportItem() { Layout = Convert.ToInt32(reader[0]), Tipo = Convert.ToString(reader[1]), Ordem = Convert.ToInt32(reader[2]) });
                    }

$

Note: The dots are simply to avoid disclosing the code, but in their places I have the fields that verify data from the user context.

Can anyone help me with this? I can not receive the data from the table due to this exception!

sql sql-server c# asp.net-web-api asp.net-mvc

asked by anonymous 20.10.2016 / 13:47

2 answers

Send Ajax Variant to PHP Compare char in an array function

score 0 · Answer 1

This error is that you are converting some string value to int and this is not possible. You think you'd get something like "01", make the above query straight into SQL and see the result it's returning.

Ex:

var numero = Convert.ToInt32(""teste"");// esse é seu problema esta vindo uma string que não é possível converter.

Where is it occurring? Almost impossible to know with the piece of code you posted, probably in creating some int parameter. or in the code

result.Add(new ReportItem() { Layout = Convert.ToInt32(reader[0]), Tipo = Convert.ToString(reader[1]), Ordem = Convert.ToInt32(reader[2]) });

try doing something like this

Solution 01 - Breakpoint and analyze values

while (reader.Read())
{
    var valorZero = reader[0]; //add breackpoint e verifique cada valor
    var valorUm = reader[1];
    var valorDois = reader[2];


    result.Add(new ReportItem() { 
         Layout = Convert.ToInt32(valorZero) , 
         Tipo = valorUm.ToString(), 
        Ordem = Convert.ToInt32(valorDois)
     });
}

Solution 02 - tryparse

You can also try a int.TryPare

var valorZero = 0;
int.TryParse(reader[0].ToString(), valorZero);

NOTE: These variable names do not follow a good practice, use a name that represents something on your system and does not value Zero, etc.

score 0 · Answer 2

Your question is not clear, your code is incomplete, you do not understand what you have in your sp_vm_getUserItems proc and where you are using your first code that seems to be in the application, but ....

This is an error database, when you try to pass a value of type varchar to a field of type int .

Probably the error is in one of two variables.

TIPOUSUARIO ou CODUSUARIO = @CODUSUARIO

If TIPOUSUARIO is a int you can not pass the values of your case 'A' ou 'P' or if one of the two CODUSUARIO = @CODUSUARIO are of different types would cause the same error.