[ 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.