DBNull Error when adding new column to a datatable

Question

I know there are A LOT of posts about this already, but I didn't find anything that helped me understand or fix my issue. I have a database filling a datatable. I am now trying to add a new column and set the value of the column row equal to 1 or 0 depending on the little formula below:

dt.Columns.Add("pViolation");

foreach (DataRow row in dt.Rows)
{
    /*exception thrown here*/ if (Convert.ToDecimal(row["oPrice"]) < Convert.ToDecimal(row["pPrice"]))
    {
        row["pViolation"] = 1;
    }
    else
    {
        row["pViolation"] = 0;
    }
}

I keep getting this error:

System.InvalidCastException: 'Object cannot be cast from DBNull to other types.'

Columns oPrice and pPrice have decimal values filled in them for each column already, so I am not casting them from null to a decimal. Can someone tell me what I am doing wrong?

Answer 1

It's throwing the exception because either row["oPrice"] or row["pPrice"] is null in the database.

You can check for null first, but you'll need to decide how to handle null values:

// check if either value is null
if (row["oPrice"] == DBNull.Value || row["pPrice"] == DBNull.Value)
{
    // what do you want to do when one value (or both values) is null?
}
else if (Convert.ToDecimal(row["oPrice"]) < Convert.ToDecimal(row["pPrice"]))
{
    row["pViolation"] = 1;
}
else
{
    row["pViolation"] = 0;
}