filter gridview using dropdownlist Vb.NET

filter gridview using dropdownlistVb.NET

DropDownList to Filter GridView

This example explains how to filter gridview using DropDownlist in asp.net Vb.net

Step 1) Add GridView and DropDownList.

        <asp:ScriptManager ID="ScriptManager1" runat="server" ></asp:ScriptManager>
       
        <table>
                  
            <asp:Label ID="Label4" runat="server"
                Text="<h1>Filter Gridview using DropDownList<h1>"
                Width="300"></asp:Label> 
            <tr><td style="vertical-align:top;">
                 <asp:UpdatePanel ID="UpdatePanel1" runat="server">
            <ContentTemplate>
        <asp:DropDownList ID="DropDownList1" runat="server"
            AutoPostBack="true"
            OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged">

        </asp:DropDownList>
            
               

            </ContentTemplate>
             
//Update Panel should be conditional because DropDownList exists outside of the
//UpdatePanel  .
//Only this UpdatePanel content will be Updated and UpdatePanels with UpdateMode='Always' also will be updated.

</asp:UpdatePanel>
            </td><td style="vertical-align:top">
    <div>
        <asp:UpdatePanel runat="server" UpdateMode="Conditional">
            <ContentTemplate>
        <asp:GridView ID="GridView1" runat="server"
             ShowFooter="True" AutoGenerateColumns="false"
             AllowPaging="true"
            ShowHeaderWhenEmpty="true"
             EmptyDataText="No data found"
            >
            <FooterStyle BackColor="YellowGreen" />
            <Columns>
                <asp:TemplateField HeaderText="Shipped Date">
                    <ItemTemplate>
                        <asp:Label runat="server" Text='<%#Eval("ShippedDate","{0:D}") %>'>

                        </asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="OrderID">
                    <ItemTemplate>
                        <asp:Label ID="Label1" runat="server" Text='<%#Eval("OrderID") %>'>

                        </asp:Label>
                    </ItemTemplate>
                    <FooterTemplate>
                        <asp:Label ID="Label3" runat="server" Text="Total">

                        </asp:Label>
                    </FooterTemplate>

                </asp:TemplateField>
                <asp:TemplateField HeaderText="Subtotal">
                    <ItemTemplate>
                        <asp:Label ID="Label2" runat="server" Text='<%#Eval("Subtotal","{0:C}") %>'>

                        </asp:Label>
                    </ItemTemplate>
                   
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Year">
                    <ItemTemplate>
                        <asp:Label ID="Label2" runat="server" Text='<%#Eval("Year") %>'>

                        </asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>

            </Columns>
        </asp:GridView>
                            </ContentTemplate>
            <Triggers>
                <asp:AsyncPostBackTrigger ControlID="DropDownList1" />
            </Triggers>
        </asp:UpdatePanel>

    </div>
                </td></tr></table>
    </form>

Step 2)  Call SQL Stored Procedure to get the DATA.

  Private Sub Calling_Stored_Procedures_using_adonet(begingdate As DateTime, enddate As DateTime)
    Try
        Dim conn As New SqlConnection("Server=.\sqlexpress2012;" & vbCr & vbLf & "                                               database=northwind;trusted_connection=yes;")


        'SqlCommand sel = new SqlCommand(stroredProc);
        Dim sel As New SqlCommand()
        Dim sqlBegin As SqlParameter = sel.Parameters.Add("@Beginning_Date", SqlDbType.DateTime)
        sqlBegin.Direction = ParameterDirection.Input
        sqlBegin.Value = begingdate
        Dim sqlEnd As SqlParameter = sel.Parameters.Add("@Ending_Date", SqlDbType.DateTime)
        sqlEnd.Direction = ParameterDirection.Input
        sqlEnd.Value = enddate
        sel.CommandType = CommandType.StoredProcedure
        sel.CommandText = "[dbo].[Sales by Year]"
        sel.Connection = conn
        Dim ad As New SqlDataAdapter(sel)

        conn.Open()
        ad.Fill(dt)

        BindDropDownList()
    Catch ex As SqlException
        Response.Write(ex.Message)
    End Try
End Sub


Step 3)  First Bind to DropDownList  

Private Sub BindDropDownList()


    If Not IsPostBack Then
        'Using LINQ to DATATABLE , select only Distinct Values
        Dim query = ().Distinct()
        DropDownList1.DataSource = query
        DropDownList1.DataBind()
        DropDownList1.Items.Insert(0, "--Year--")
    End If

End Sub

Step 4)   Add DropDownList   Event Handler SelectedIndexChanged,

Make AutoPostBack=true

In this actually bind to GridView using Selected Value in DropDownList

       Protected Sub DropDownList1_SelectedIndexChanged(sender As Object, e As EventArgs)


    Dim year As [String] = DropDownList1.SelectedValue.ToString()

    Dim view As DataView = dt.DefaultView

    view.RowFilter = "Year='" + year + "'"

    GridView1.DataSource = view
    'dt.Select("Year='"+year+"'");
    GridView1.DataBind()


End Sub


OUTPUT

Tags:Asp:GridView,asp:DropDownList,ADO.NET Stored Procedures,ADO.NET DataTable,DropDownList SelectIndexChanged,FIlter GridView based on DropDownList value,Filtering GridView using DropDownList VB.NET,LINQ to DataTable Distinct,UPDATEPANEL CONDITIONAL,AJAX GRIDVIEW,AJAX DROPDOWNLIST, GridView Template Controls,GridView FooterTemplate