Filter GridView using DropDownList SQlDataSource VB.NET

Filter GridView using DropDownList SQlDataSource VB.NET

 

Step 1) Add DropDownList and GridView and SQLDataSource to Page

 

 <asp:DropDownList ID="DropDownList1"
            runat="server" AutoPostBack="True"
            OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged">

   <asp:GridView ID="GridView1" runat="server"
            AllowSorting="True"
            EmptyDataText="no data found"
            ShowFooter="True" ShowHeaderWhenEmpty="True">
        </asp:GridView>

        <asp:SqlDataSource ID="SqlDataSource1" runat="server"
            ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"
            SelectCommand="Sales by Year" SelectCommandType="StoredProcedure">
            <SelectParameters>
                <asp:Parameter DefaultValue="1996-01-01"
                    Name="Beginning_Date" Type="DateTime" />
                <asp:Parameter DefaultValue="1998-01-01"
                    Name="Ending_Date" Type="DateTime" />
            </SelectParameters>
        </asp:SqlDataSource>

in above SQLDataSource binding to Stored Procedure(i.e Sales by Year), Which accepts 2 parameters
Bengin Date and EndDate(Hard-coded).

Step 2) Get SQLDataSource DataSet

 //note: Select Statement in SQLDATASource creates a DataSet(stored in Memory)

   
    Protected  Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
        BindCulture()
        var ds = Function DataSourceSelectArguments() As SqlDataSource1.Select(Shadows
        End Function
)
        view = CType(ds, DataView)
        BindDropDownWithDistinctValues()
        GridView1.DataBind()
    End Sub


Step 3) Bind Distinct Values from DataSet to DropDownList

Private Sub BindDropDownWithDistinctValues()
    If Not IsPostBack Then
        Dim dt As DataTable = view.Table
        Dim query = ().Distinct()
        DropDownList1.DataSource = query
        DropDownList1.DataBind()
    End If
End Sub

Step 4)   Add SelectionIndexChanged for DropDownList and Set AutoPostback=true

Protected Sub DropDownList1_SelectedIndexChanged(sender As Object, e As EventArgs)
    view.RowFilter = "Year ='" + DropDownList1.SelectedValue + "'"

    GridView1.DataSource = view
    GridView1.DataBind()

End Sub

Step 5) Run the Page

Filter GridView using DropDownList SQlDataSource VB.NET