Handling Null Values in DataGrid

This article shows you how to handle no data found or null values in an ASP.NET DataGrid control.

Introduction:

Going through the newsgroups I have come across few queries given below.

• Common task is a user searches for some data. If the Data is found its populated in DataGrid or else a label control/any equivalent web control displays "No Data/Result Found". The query was how to display the result "No Data/Results Found" in the DataGrid itself (perhaps if DataGrid had Text Property it could be changed to this message).
  
  
• In case the records are found there is possibility that there are null values in the database. The DataGrid changes null values to  .Is there any workaround so that  's are displayed as "".
  
  

Solution:

Lets design the User Interface. Just a simple drag and drop the DataGrid

<asp:DataGrid id="DataGrid1" OnItemDataBound="ItemDB" runat="server" BorderColor="#CCCCCC" BorderStyle="None" BorderWidth="1px" BackColor="White" CellPadding="3" Font-Names="Verdana" Font-Size="X-Small">
<SelectedItemStyle Font-Bold="True" ForeColor="White" BackColor="#669999"></SelectedItemStyle>
<ItemStyle ForeColor="#000066"></ItemStyle>
<HeaderStyle Font-Bold="True" ForeColor="White" BackColor="#006699"></HeaderStyle>
<FooterStyle ForeColor="#000066" BackColor="White"></FooterStyle>
</asp:DataGrid>

In the Page_Load

SqlConnection mycn;
SqlDataAdapter myda;
DataSet ds;
String strConn;
private void Page_Load(object sender, System.EventArgs e)
{
// Put user code to initialize the page here
strConn="Data Source=localhost;uid=sa;pwd=;Initial Catalog=northwind";
mycn = new SqlConnection(strConn);
myda = new SqlDataAdapter ("Select * FROM Orders where orderid<=10250", mycn);
ds= new DataSet ();
myda.Fill (ds,"Table");
DataView dv = new DataView (ds.Tables [0]);
if (!Page.IsPostBack )
{
DataGrid1.DataSource=dv;
DataGrid1.DataBind ();
}
}

The solution for above queries is done in ItemDataBound

protected void ItemDB(object sender, System.Web.UI.WebControls.DataGridItemEventArgs e)
{
DataView dv =(DataView)DataGrid1.DataSource ;
DataRowView drv = (DataRowView)e.Item.DataItem ;
//Check if the Data Exists
if (dv.Table.Rows.Count == 0 )
{
//By default the Datagrid Header is shown in case there is no Data Avaiable
//So in case of No Data found
//Check the ListItemType.Header
if ((e.Item.ItemType == ListItemType.Header))
{
int i= e.Item.Cells.Count;
//Assign "No Search result Found in one of the cells of DataGrid
e.Item.Cells [0].Text = "No Search Results Found";
//Remove Rest of the empty cells from Datagrid
for (int j=i-1;j>0;j--)
{
e.Item.Cells.RemoveAt(j);
}
}
}
else
{
//If the Data Exists
//Navigate through each cell
for (int x=0;x < e.Item.Cells.Count -1;x++)
{
//Check for the ListItemType.Item or ListItemType.AlternatingItem
if ((e.Item.ItemType == ListItemType.Item )||
(e.Item.ItemType == ListItemType.AlternatingItem))
{
//To check if Column Data = null
if (drv[x].ToString().Trim() == System.DBNull.Value.ToString())
{
//For null values Datagrid shows
//Here we will display ""
e.Item.Cells[x].Text ="";
}
}
}
}
}

Note: The desired output i.e. view Source having "" and not "&nbsp;"'s would work provided the Sql Statement is "Select * from <tableName>".