System.Data.Sqlclient.Sqlexception: Conversion failed when Converting the varchar value to int

Question

I’m getting this Exception in c# when I try to read SQL data. I’m wondering if this error is a problem with my SQL or C#.

My SQL code that makes 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 on the c# that reads it 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 disclosure of the code, but in their places I have the fields that check data of the user context.

Can anyone help me with that? I can’t receive table data due to this Exception!

Answer 1

This error is that you are converting some string value to int and this is not possible. You think you would receive something like "01", do the above query directly in SQL and see the result q it is returning.

Ex:

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

Where is it happening? Almost impossible to know with the piece of code you posted, probably in the creation of 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 to do something like this

Solution 01 - Breakpoint and analyze the 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 in your system and not valueZero, etc

Answer 2

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

This is a mistake common to the database, when you try to pass a value like varchar to a field of the kind int.

Probably the error is in one of the two variables.

TIPOUSUARIO ou CODUSUARIO = @CODUSUARIO

Case TIPOUSUARIO be a int you cannot pass the values of your case 'A' ou 'P' or if one of the two CODUSUARIO = @CODUSUARIO are of divergent types would cause the same error.