Custom Message in GridView when DataSet is empty

Here we will discuss the following concepts :

• How to bind the data to GridView using stored procedure with parameters or how to bind the data to GridView with respect to DeptId.
• How to bind empty GridView with header and custom message when no data is present in DataSet in ASP.NET.

At Database:

Create the following table to demonstrate the above concepts.

Copy the following script and run in your database to create USP_GetEmployeesByDeptId stored procedure.

Create procedure [dbo].[USP_GetEmployeesByDeptId]
@DeptId int
AS
Begin
Select * from Mas_Employee
where DeptId = @DeptId
END

Implementation:

Create new ASP.NET Empty Web Application and give it a meaningfull name. Add one webform to your project.

In Web.config:

Make the connection string in web.config.

<connectionStrings>
   <add name="conStr" connectionString="Password = 1234; User ID=sa; Database = DB_DEV_JAI; Data Source = ."
   providerName="System.Data.SqlClient" />
</connectionStrings>

Design Your.aspx:

Copy the following code in your design page.  

<div>
    <asp:TextBox ID="txtDeptID" runat="server" placeholder="Enter DeptId"></asp:TextBox>

    <asp:Button ID="btnGetData" runat="server" Text="GetData" OnClick="btnGetData_Click" />
    <br />
    <br />
    <asp:GridView ID="gvEmptyDta" runat="server" AutoGenerateColumns="False">
        <Columns>
            <asp:BoundField DataField="Name" HeaderText="Emp_Name" />
            <asp:BoundField DataField="Salary" HeaderText="Emp_Salary" />
            <asp:BoundField DataField="DeptId" HeaderText="Emp_DeptId" />
        </Columns>
    </asp:GridView>
</div>

CodeBehind

When you click the button by providing DeptId through textbox, it binds the data to GridView with respect to DeptId. If there are no employees with respect to DeptId, then it shows empty Gridview with header and custom message.

 

BindGrid(int DeptID): BindGrid method is used to bind the data to GridView with respect to DeptId. So we have to pass the DeptId to BindGrid method. In this example we will pass DeptId through TextBox.

 

Copy the following code in your .cs or codebehind file:

protected void btnGetData_Click(object sender, EventArgs e)
{
    int DeptId = Convert.ToInt32(txtDeptID.Text.Trim());
    BindGrid(DeptId);
}

private void BindGrid(int DeptID)
{
    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString);
    SqlCommand cmd = new SqlCommand("USP_GetEmployeesByDeptId", con);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@DeptId", DeptID);
    SqlDataAdapter adp = new SqlDataAdapter(cmd);
    DataSet ds = new DataSet();
    adp.Fill(ds);
    if (ds.Tables[0].Rows.Count > 0)
    {
        gvEmptyDta.DataSource = ds;
        gvEmptyDta.DataBind();
    }
    else
    {
        BingEmpyGridViewWithHeader(gvEmptyDta, ds, "No Data Found");
    }
}

//If there are no records to bind the gridview then the following method will be executed.

protected void BingEmpyGridViewWithHeader(GridView grd, DataSet ds, String msg)
{
    try
    {
        if (ds.Tables[0].Rows.Count == 0)
        {
            //Add a blank row to the dataset
            ds.Tables[0].Rows.Add(ds.Tables[0].NewRow());
            //Bind the DataSet to the GridView
            grd.DataSource = ds;
            grd.DataBind();
            //Get the number of columns to know what the Column Span should be
            int columnCount = grd.Rows[0].Cells.Count;
            //Call the clear method to clear out any controls that you use in the columns. E.g If you are using dropdown list etc. in any of the column then it is necessary.
            grd.Rows[0].Cells.Clear();
            grd.Rows[0].Cells.Add(new TableCell());
            grd.Rows[0].Cells[0].ColumnSpan = columnCount;
            grd.Rows[0].Cells[0].Text = "<font color=Red><b><i><center>" + msg + "</center></i></b></font>";
        }
    }
    catch (Exception ex)
    {
        //Do your exception handling here
    }
}

Output

When no data is found:

When data is found:

I hope you enjoyed it. Please provide your valuable suggestions and feedback if you found this article helpful.