In this Blog you will learn the following concepts -
- How to bind the data to Repeater using stored procedure with parameters.
- How to get the data by using DeptId.
- How to bind empty Repeater with header.
- How to create custom message when no data present in DataSet in Asp.net.
At Database
Create a table like as follows to demonstrate the above concepts.
Copy the following script 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
Steps
Create new ASP.NET Empty Web Application give it to meaning full name.
Add one webfrom to your project.
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>
.aspx:
Copy the following code in your design page.
- <div align="center" style="width:30%;">
- <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:Repeater ID="rptEmpDetails" runat="server" OnItemDataBound="rptEmpDetails_ItemDataBound">
- <HeaderTemplate>
- <table border="1" style="width:100%;">
- <tr style="background-color: Blue; color: #FFF;" align="center">
- <th>
- Name
- </th>
- <th>
- Salary
- </th>
- <th>
- DeptId
- </th>
- </tr>
- </HeaderTemplate>
- <ItemTemplate>
- <tr style="background-color: white;" align="center">
- <td><%#Eval("Name") %>
- </td>
- <td><%#Eval("Salary")%>
- </td>
- <td><%#Eval("DeptId")%>
- </td>
- </tr>
- </ItemTemplate>
- <FooterTemplate>
- </table>
- <div id="dvNoRecords" runat="server" visible="false" style="text-align: center; color: Red;">
- <font>
- <b>
- <i>No records to display.</i>
- </b>
- </font>
- </div>
- </FooterTemplate>
- </asp:Repeater>
- </div>
CodeBehind
When you click the button by providing DeptId through textbox it binds the data to Repeater with respect to DeptId. If there are no employees with respect to DeptId then it shows empty Repeater with header and custom message.
- protected void btnGetData_Click(object sender, EventArgs e)
- {
- int DeptId = Convert.ToInt32(txtDeptID.Text.Trim());
- BindRepeater(DeptId);
- }
BindRepeater
BindRepeater method is used to bind the data to Repeater with respect to DeptId. So we have to pass the DeptId to BindRepeater method. In this example we pass DeptId through TextBox.
- private void BindRepeater(int DeptID)
- {
- try
- {
- 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);
- rptEmpDetails.DataSource = ds;
- rptEmpDetails.DataBind();
- }
- catch (Exception ex)
- {
-
- }
- }
If there are no records to bind the Reapeter, then the following Reapeter event will be executed.
- protected void rptEmpDetails_ItemDataBound(object sender, RepeaterItemEventArgs e)
- {
- if (rptEmpDetails.Items.Count < 1)
- {
- if (e.Item.ItemType == ListItemType.Footer)
- {
- HtmlGenericControl dvNoRec = e.Item.FindControl("dvNoRecords") as HtmlGenericControl;
- if (dvNoRec != null)
- {
- dvNoRec.Visible = true;
- }
- }
- }
- }
Output :
When no data is found
Repeater with records
I hope you enjoyed it. please provide your valuable feedback and suggestions if you found this article is helpful.