Saturday, June 28, 2014

Need to replace null value by zero to calculate

I have a datagridview (dgv) that is bound to database:



QtyOnHand QtyOnHold QtySold QtyAvailable
10 2 1 null
12 null null null
null null null null
7 5 null null
25 null 5 null

I want to iterate through the datagridview and calculate the last column with this formula:

QtyAvailable = QtyOnHand - QtyOnHold - QtySold


so the datagridview should become:



QtyOnHand QtyOnHold QtySold QtyAvailable
10 2 1 7
12 null null 12
null null null null
7 5 null 2
25 null 5 20



I use this code:



foreach (DataGridViewRow row in dgv.Rows)
{
if (string.IsNullOrEmpty(row.Cells["QtyOnHand"].Value.ToString()))
{
row.Cells["QtyAvailable"].Value = Convert.ToInt32(row.Cells["QtyOnHand"].Value) - Convert.ToInt32(row.Cells["QtyOnHold"].Value)
- Convert.ToInt32(row.Cells["QtySold"].Value) - Convert.ToInt32(row.Cells["QtyAvailable"].Value)
}
}



The problem is when any of the value on the right side of the equation is null, the whole equation is invalid. I need to replace the null values by 0 on the fly so that it can be calculated. Note that I do not want to actually replace the null values in the second and third columns of the table, just replace it in the equation whenever it applies to make the calculation work.





No comments:

Post a Comment