Search button, to search in Repeater

I want to create a search button, so that I can search based on what I want to search in repeater table. I do not know how to it, cause I already try repGender.Rebind(), but still wrong... I do not know how to the behind code, as I know that we need:

Protected Sub BtnSearch_Click(sender As Object, e As EventArgs) Handles BtnSearch.Click

This is the UI, the table is in repeater

FRONT CODE

<label for="name">Jantina</label>
       <asp:DropDownList ID="ddlGender" runat="server"></asp:DropDownList>
        <asp:Button ID="BtnSearch" runat="server" Text="Search" />
        <br/><br/>
        <asp:Repeater ID="repGender" runat="server">
            <HeaderTemplate>
                <table cellspacing="0" rules="all" border="1">
                    <tr>
                        <th>Gender</th>
                        <th>Total</th>
                    </tr>
            </HeaderTemplate>
            <ItemTemplate>
                <tr>
                    <td>
                        <%# Eval("GENDER")%>
                    </td>
                    <td style="text-align: center">
                       <%# Eval("TOTAL")%></a>
                    </td>
                </tr>
            </ItemTemplate>
            <FooterTemplate>
                <tr style="font-weight: bold">
                    <td>Grand Total</td>
                    <td style="text-align: center">
                        <asp:Label runat="server" ID="lblTotal"></asp:Label>
                    </td>
                </tr>
                </table>
            </FooterTemplate>
        </asp:Repeater>

BEHIND CODE

Partial Class Statistics
    Inherits App.Main

  
    Dim MyTotal As Integer
    Private Property Label As Object

    Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
        If Not IsPostBack Then
            BindData()
            ddlGender_Bind()
        End If
    End Sub


    Sub BindData()
        Dim dal As New DBWrapper.DAL(strConnectionString, strProviderName)
        Dim dt As New DataTable
        dal.DB.Parameters.Clear()
        dal.DB.AddParameter("@GENDER", ddlGender.Text)
        If dal.DB.ExecuteProcedure(dt, "GENDER_BB") Then
            repGender.DataSource = dt
            repGender.DataBind()

            
        End If

    End Sub
  

    Protected Sub repGender_ItemDataBound(sender As Object, e As RepeaterItemEventArgs) Handles repGender.ItemDataBound
        If e.Item.ItemType = ListItemType.Item OrElse e.Item.ItemType = ListItemType.AlternatingItem Then
            Dim gData As DataRowView = e.Item.DataItem

            MyTotal = MyTotal + gData.Item("TOTAL")

        End If

        If e.Item.ItemType = ListItemType.Footer Then

            ' set the total value in footer
            Dim lblTotal As Label = e.Item.FindControl("lblTotal")
            lblTotal.Text = MyTotal

        End If
    End Sub

    Public Sub ddlGender_Bind()
        Dim dal As New DBWrapper.DAL(strConnectionString, strProviderName)
        Dim dt As New DataTable

        If dal.DB.ExecuteProcedure(dt, "GENDER_R") Then
            Share.LoadList(ddlGender, "GENDER", "GENDER_ID", dt, Enums.MsgCode.PleaseSelect.ToString, ListOrder.ByValue)
        End If
    End Sub
     
    
    Protected Sub BtnSearch_Click(sender As Object, e As EventArgs) Handles BtnSearch.Click

    End Sub
End Class

Solution

Here I share the answer for people to refer...

Protected Sub BtnSearch_Click(sender As Object, e As EventArgs) Handles BtnSearch.Click Dim dal As New DBWrapper.DAL(strConnectionString, strProviderName) Dim dt As New DataTable dal.DB.Parameters.Clear() dal.DB.AddParameter("@GENDER", ddlGender.SelectedValue) If dal.DB.ExecuteProcedure(dt, "GENDER_B") Then repGender.DataSource = dt repGender.DataBind()

    End If
End Sub

At Stored Procedure GENDER_B

ALTER PROCEDURE [dbo].[GENDER_B]
     @GENDER as INT
AS
BEGIN
    declare @sql as varchar(max)

set @sql='SELECT b.GENDER, COUNT(b.GENDER) AS TOTAL FROM USERS a 
JOIN GENDER b ON b.GENDER_ID = a.GENDER
WHERE IS_DELETED = 0
GROUP BY b.GENDER'

if @GENDER<>'' 
begin
set @sql = @sql + 'and a.GENDER='''  + @GENDER + ''' '
end

--select @sql 
exec(@sql)
END