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.
  1. Create procedure [dbo].[USP_GetEmployeesByDeptId]
  2. @DeptId int
  3. AS
  4. Begin
  5. Select * from Mas_Employee
  6. where DeptId = @DeptId
  7. 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.
  1. <connectionStrings>
  2. <add name="conStr" connectionString="Password = 1234; User ID=sa; Database = DB_DEV_JAI; Data Source = ."
  3. providerName="System.Data.SqlClient" />
  4. </connectionStrings>
Design Your.aspx:

Copy the following code in your design page.
  1. <div>
  2. <asp:TextBox ID="txtDeptID" runat="server" placeholder="Enter DeptId"></asp:TextBox>
  4. <asp:Button ID="btnGetData" runat="server" Text="GetData" OnClick="btnGetData_Click" />
  5. <br />
  6. <br />
  7. <asp:GridView ID="gvEmptyDta" runat="server" AutoGenerateColumns="False">
  8. <Columns>
  9. <asp:BoundField DataField="Name" HeaderText="Emp_Name" />
  10. <asp:BoundField DataField="Salary" HeaderText="Emp_Salary" />
  11. <asp:BoundField DataField="DeptId" HeaderText="Emp_DeptId" />
  12. </Columns>
  13. </asp:GridView>
  14. </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:
  1. protected void btnGetData_Click(object sender, EventArgs e)
  2. {
  3. int DeptId = Convert.ToInt32(txtDeptID.Text.Trim());
  4. BindGrid(DeptId);
  5. }
  7. private void BindGrid(int DeptID)
  8. {
  9. SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString);
  10. SqlCommand cmd = new SqlCommand("USP_GetEmployeesByDeptId", con);
  11. cmd.CommandType = CommandType.StoredProcedure;
  12. cmd.Parameters.AddWithValue("@DeptId", DeptID);
  13. SqlDataAdapter adp = new SqlDataAdapter(cmd);
  14. DataSet ds = new DataSet();
  15. adp.Fill(ds);
  16. if (ds.Tables[0].Rows.Count > 0)
  17. {
  18. gvEmptyDta.DataSource = ds;
  19. gvEmptyDta.DataBind();
  20. }
  21. else
  22. {
  23. BingEmpyGridViewWithHeader(gvEmptyDta, ds, "No Data Found");
  24. }
  25. }
  27. //If there are no records to bind the gridview then the following method will be executed.
  29. protected void BingEmpyGridViewWithHeader(GridView grd, DataSet ds, String msg)
  30. {
  31. try
  32. {
  33. if (ds.Tables[0].Rows.Count == 0)
  34. {
  35. //Add a blank row to the dataset
  36. ds.Tables[0].Rows.Add(ds.Tables[0].NewRow());
  37. //Bind the DataSet to the GridView
  38. grd.DataSource = ds;
  39. grd.DataBind();
  40. //Get the number of columns to know what the Column Span should be
  41. int columnCount = grd.Rows[0].Cells.Count;
  42. //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.
  43. grd.Rows[0].Cells.Clear();
  44. grd.Rows[0].Cells.Add(new TableCell());
  45. grd.Rows[0].Cells[0].ColumnSpan = columnCount;
  46. grd.Rows[0].Cells[0].Text = "<font color=Red><b><i><center>" + msg + "</center></i></b></font>";
  47. }
  48. }
  49. catch (Exception ex)
  50. {
  51. //Do your exception handling here
  52. }
  53. }
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.