[ Replacing nulls in table with space ]

We have a SQL table which has a cost column containing several NULL entries.

So before binding this table to a grid view, I would like to replace it with space.

foreach (DataRow row in ds.Tables[0].Rows)
{
 if (row["AverageCost"] is System.DBNull)
 {
   row["AverageCost"] = Convert.ToDecimal("0.00"); // works
   row["AverageCost"] = " "; // doesnt work
   row["AverageCost"] = Convert.ToDecimal(" "); // doesnt work
 }
}

I would like to have the grid contain empty string and not 0.00.

Please suggest.

Answer 1

---

The only way to account for that, would be to ensure your column is a VarChar or NVarChar for instance. A text field, rather than a numeric like you currently have. Once you've modified the column type, you would be able to assign a space.

However, the null that your attempting to replace is designed for exactly that scenario:

• Valid numeric value (Example: 5.00)
• Valid numeric value (Example: 0.00)

Those two instances actually represent that the value exist via the user, which contains a useful representation for you. The third choice:

• Null

That actually will indicate that the user hasn't modified said value, which is an indicator to you. In essence it is a viable option for controlling the state of that field. Which if you had a text field you would have:

• Valid Value ("5.00")
• Valid Value (" " or compared to numeric 0.00)
• null

So the question is, why would the space be better suited than the null or 0.00? Under most circumstances it will meet your criteria correctly.

Otherwise to perform said change you would need:

example.Tables[0].Columns.Add("...", typeof(string));
foreach(var row in example.Tables[0].Rows)
{
     if(row["AverageCost"] != DBNull.Value)
          row["AverageCostText"] = row["AverageCost"].ToString();
      else { row["AverageCostText"] = String.Empty; }
}

This would ensure the proper value is placed into your text field, but obviously the drawback should you need a decimal you'll always have to parse it. Hopefully this clarifies for you.

Answer 2

---

From your example, it appears that the column AverageCost is a decimal field. You won't be able to assign this to a "space".

You could, however, add a new column that contains the text representation of AverageCost...

ds.Tables[0].Columns.Add("AverageCostText", typeof(String));
foreach (DataRow row in ds.Tables[0].Rows)
{
     row["AverageCostText"] = (row["AverageCost"] == DBNull.Value) ? string.Empty : string.Format("{0}", row["AverageCost"]);
}

Answer 3

---

You can't assign a whitespace character (type char) to a column of type decimal. It might be useful for you to assign a 0 instead a whitespace.