Convert.ToInt throws Object cannot be cast from DBNull to other types

Question

What seems to be wrong with the below code? I think there are null in the database, how do I sum a column that contains null?

int Total_QtyinHand = 0;
int Total_QtyAllocated = 0;
int Total_QtyinStock = 0;
int Total_QtyUpcoming = 0;

protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
    if (e.Row.RowType == DataControlRowType.DataRow)
    {
        // VVV  --- Exception happens on the line below --- VVV
        Total_QtyinHand += Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "QuantityonHand"));
        Total_QtyAllocated += Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "Allocated_Quantity"));
        Total_QtyinStock += Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "Qty_in_Stock"));
        Total_QtyUpcoming += Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "UpcomingStock"));
    }

    else if (e.Row.RowType == DataControlRowType.Footer)
    {
        e.Row.Cells[3].Text = "Total Qty";
        e.Row.Cells[3].Font.Bold = true;

        e.Row.Cells[4].Text = Total_QtyinHand.ToString();
        e.Row.Cells[4].Font.Bold = true;

        e.Row.Cells[5].Text = Total_QtyAllocated.ToString();
        e.Row.Cells[5].Font.Bold = true;

        e.Row.Cells[6].Text = Total_QtyUpcoming.ToString();
        e.Row.Cells[6].Font.Bold = true;
    }

}

Answer 1

When using the database API, a value will be substituted with DBNull.Value in case of a null value in the database. Your code tries to directly convert that to an int which fails. Comparing the value directly to null doesn't help either, as the value is not null, but DBNull.Value.

So:

object value = DataBinder.Eval(e.Row.DataItem, "QuantityonHand");
if (value != DBNull.Value) { Total_QtyinHand += (int)value; }

Or if the value is actually a string under the hood, you may need to parse it instead. It's better to upgrade the schema of your database to actually store and return a numeric value though, more on that later:

object value = DataBinder.Eval(e.Row.DataItem, "QuantityonHand");
if (value != DBNull.Value)
{ 
    int result = -1;
    if (int.TryParse((string)value, NumberStyles.Integer, CultureInfo.InvariantCulture, out result))
    {
       Total_QtyinHand += result;
    }
}

You can of course capture this logic in a separate function, or play around with the ?: operator:

object value = DataBinder.Eval(e.Row.DataItem, "QuantityonHand");
Total_QtyinHand += (value == DBNull.Value ? 0 : (int)value);

While researching answers, you might find a lot of suggestions to round-trip the value through a string and parse it again. This can lead to very strange behavior and I strongly suggest not to do that. I've even written an FxCoprule in the past to catch these buggers in the code. The value in the database is already a numeric format if your schema is correct and there should be no reason to introduce all kinds of freaky behavior with Culture parsing sand such. When using a .Parse or .TryParse method, make sure you supply it with the expected NumberStyle and the culture the number is stored in to prevent accidental parse mistakes (like multiplying the value by 100 if the decimal separator is set incorrectly).

Answer 2

this code working fine in my test

tot += DataBinder.Eval(e.Row.DataItem, "mylab").ToString() != string.Empty 
    ? Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "mylab")) 
    : 0;

e.Row.Cells[0].Text = tot.ToString();