How to Display the Empty GridView in case of no Records in Database

Scenario

Consider a table named Employees with the columns Id, Name, Designation and Salary and you have been asked to display the data on a web page.



The requirement is quite simple and can be done using a Grid View. Now consider a case when your query fetches no data and there is nothing to display in the Grid View. In that case, we need to display a custom message or an alert to the user that there is no data available. There are several ways to do that but I will show you a simple method to do it.

Suppose we need to find the list of Employees with Salary < 10,000. If you look at the preceding table, it is clear that no employee have a Salary less than 10,000. So our query fetches nothing for the Grid View.

In that case, we need to show the empty Grid View with a message that the records are not available. To do that, we need to enable the Boolean property ShowHeaderWhenEmpty to True. Be sure you're using the ASP.NET 4.0 or later version to use this property.

Also, we need to add the <EmptyDataTemplate></EmptyDataTemplate> property inside the grid view to display the message.

Code

The following is the EmployeeDetails.aspx page: 

  1. <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="EmployeeDetails.aspx.cs" Inherits="GridView.EmployeeDetails" %>  
  2. <!DOCTYPE html>  
  3. <html  
  4.     xmlns="http://www.w3.org/1999/xhtml">  
  5.     <head runat="server">  
  6.         <title>Employee Details</title>  
  7.     </head>  
  8.     <body>  
  9.         <form id="form1" runat="server">  
  10.             <div>  
  11.                 <h3>Employee Details</h3>  
  12.                 <asp:GridView ID="gvEmployee" runat="server"    
  13.                  AutoGenerateColumns="False" ShowHeaderWhenEmpty=”True”>  
  14.                     <Columns>  
  15.                         <asp:BoundField DataField="Id" HeaderText="Id" />  
  16.                         <asp:BoundField DataField="Name" HeaderText="Name" />  
  17.                         <asp:BoundField DataField="Designation" HeaderText="Designation" />  
  18.                         <asp:BoundField DataField="Salary" HeaderText="Salary"  />  
  19.                     </Columns>  
  20.                     <EmptyDataTemplate>No Record Available</EmptyDataTemplate>  
  21.                 </asp:GridView>  
  22.             </div>  
  23.         </form>  
  24.     </body>  
  25. </html>  
In the EmployeeDetails.aspx.cs page, we need to add a fix to check the condition if the Grid View is empty or not. I'm going to assign an empty DataTable to the grid view in case no records are fetched.
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Configuration;  
  4. using System.Data.SqlClient;  
  5. using System.Linq;  
  6. using System.Web;  
  7. using System.Web.UI;  
  8. using System.Web.UI.WebControls;  
  9.   
  10. namespace GridView   
  11. {  
  12.     public partial class EmployeeDetails: System.Web.UI.Page   
  13.     {  
  14.         protected void Page_Load(object sender, EventArgs e)   
  15.         {  
  16.             string connect = ConfigurationManager.ConnectionStrings["SQLConnect"].ConnectionString;  
  17.             using(SqlConnection con = new SqlConnection(connect))   
  18.             {  
  19.                 var query = "SELECT * from Employees where Salary < 10000";  
  20.                 SqlCommand cmd = new SqlCommand(query, con);  
  21.                 con.Open();  
  22.                 SqlDataReader dr = cmd.ExecuteReader();  
  23.                 if (dr.hasRows)   
  24.                 {  
  25.                     gvEmployee.DataSource = dr;  
  26.                     gvEmployee.DataBind();  
  27.                 }   
  28.                 else   
  29.                 {  
  30.                     //Empty DataTable to execute the “else-condition”  
  31.                     DataTable dt = new Datatable();  
  32.                     gvEmployee.DataSource = dt;  
  33.                     gvEmployee.DataBind();  
  34.                 }  
  35.             }  
  36.         }  
  37.     }  
  38. }  
It should be noted that the ADO.Net code present in this article is not optimized and is used only for the demo. In real projects, Stored Procedures and other conventions should be used for the query. Now run the application and the output is as follows:



Conclusion

Your feedback and constructive criticism is always appreciated, keep it coming. Until then, try to put a ding in the universe!