Especified cast is not valid

Question

I don't understand why I'm getting this error, I have a datatable with the columns : integer numeromesa , string status and integer valortotal.

When I try to import the data with a select query to into my Pedido variable it gets me the cast error. This is the class that will receive the data from the table

public class Pedido
{
    public int numeromesa { get; set; }
    public int valorpedido { get; set; }
    public string status { get; set; }

}

Now this is the method I made use the select query

    public List<Pedido> listaPedido()
    {
        vsql = "SELECT * FROM pedidos";
        List<Pedido> bancoPedidos = new List<Pedido>();
        NpgsqlCommand objcmd = null;
         objcmd = new NpgsqlCommand(vsql, con);
                NpgsqlDataAdapter adp = new NpgsqlDataAdapter(objcmd);
                DataTable dt = new DataTable();
                adp.Fill(dt);
                foreach (DataRow row in dt.Rows)
                {
                 //This is line 185
                    Pedido p = new Pedido
                    {
                        numeromesa = (int)row[1],
                        status = (string)row[2],
                        valorpedido = (int)row[3]                            
                    };
                   bancoPedidos.Add(p);

                    return bancoPedidos;
         }

The problem seems to be with the valorpedido variable if I take it out, the code works fine

************** Exception Text ************** System.InvalidCastException: Specified cast is not valid. PostgreSQL.cs:line 185

Answer 1

The original code addresses columns by index. Indexes start at zero, and they are prone to misalignment as the schema changes.
Instead, use names to access the columns:

var p = new Pedido
{
    numeromesa = (int)row["numeromesa"],
    status = row["status"].ToString(),
    valorpedido = (int)row["valortotal"]
};

This code assumes that neither status nor valortotal are nullable. If they were, you need to account for that, example:

public class Pedido
{
    public int numeromesa { get; set; }
    public int? valorpedido { get; set; }
    public string status { get; set; }
}

and

var p = new Pedido
{
    numeromesa = (int)row["numeromesa"],
    status = row["status"].ToString()
};

if(row["valortotal"].Equals(DBNull.Value))
{
    p.valorpedido = null;
}
else
{
    p.valorpedido = Convert.ToInt32(row["valortotal"]);
}

The performance penalty of looking up values by name instead of ordinal is negligible in this context (except when we're talking about tens of thousands of records), and gives you one important advantage: If the order of the columns ever changed due to modifications of the pedidos table or view schema, your code will continue to work. As it is originally written, it would break.

Answer 2

Since the error is on:

 valorpedido = (int)row[3]   

The issue is that row[3] isn't of type integer when it comes out of the database.

Most likely you're off by one column because it's a zero based index with the data row.