Handling Null Values in DataGrid

Introduction

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

• Common task is a user search for some data. If the Data is found it's 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 the 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

Let's 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 the 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 Available
        // 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>".