Wednesday, 16 January 2013

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"

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

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

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)
        var ds = Function DataSourceSelectArguments() As SqlDataSource1.Select(Shadows
        End Function
        view = CType(ds, DataView)
    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
    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

End Sub

Step 5) Run the Page

Filter GridView using DropDownList SQlDataSource C# VB.NET

Filter GridView using DropDownList SQlDataSource VB.NET

No comments:

Post a Comment