While i thought this would be really simple im really struggling on filtering a gridview twice!

I can bind data to my gridview on the choice of my first dropdown box from a dataset

but i would like to then filter those results from a selection in a second dropdown box but for the life of me i cant get it to work!

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If IsPostBack Then

            Me.drpDecision.Items.Clear()

            Dim conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("~") & "\App_Data\referrals.mdb;")

            Dim sql As String = "SELECT  * FROM tbl_ WHERE RefService = '" & Me.drpCriteria.SelectedValue & "'"

            'Dim SQL As String = "SELECT * FROM tbl_Service WHERE RefService LIKE '" & Me.drpCriteria.SelectedValue & "'"

            Dim da As New OleDbDataAdapter(sql, conn)
            Dim ds As New DataSet

            da.Fill(ds)

            GridView1.DataSource = ds
            GridView1.DataBind()
            MsgBox("Selection")

            Dim sql2 As String = "select Distinct(RefDecision) FROM tbl_Service WHERE RefService = '" & Me.drpCriteria.SelectedValue & "'"
            Dim da2 As New OleDbDataAdapter(sql2, conn)
            Dim ds2 As New DataSet
            Dim i As Integer

            da2.Fill(ds2)

            If ds2.Tables(0).Rows.Count > 0 Then

                For i = 0 To ds2.Tables(0).Rows.Count - 1
                    Me.drpDecision.Items.Add(ds2.Tables(0).Rows(i).Item(0))
                Next


            Else
                Me.drpDecision.Items.Add("No matching records")
            End If

            If Me.drpDecision.SelectedValue <> "" Then
                MsgBox("Selection")
                FilterSearch(ds)
            End If
        End If
    End Sub

    Private Sub FilterSearch(ByRef ds As DataSet)

        GridView1.Dispose()
        Dim SearchTB As DataTable = ds.Tables(0)

        Dim Rows As DataRow() = SearchTB.Select("RefDecision = " & Me.drpDecision.SelectedValue)

        MsgBox(SearchTB.Rows.Count)

        GridView1.DataSource = SearchTB.DataSet
        GridView1.DataBind()

    End Sub

Hi ninj

First of all, put your data bind logic in a separate sub not in the page load event you will be needing it again.

Next call the new sub from the page load to make sure it works,

In the designer, double click the second dropdown box - it should take you to the OnSelectionChanged sub in the code behind- enter the name of the sub you created above.

If this does not work correctly it's probably because you are not storing the original value in the session object.

Sorry, incomplete answer - you obviously need to reset your search criteria by the value of the second dropdown before calling the sub. If you are using Ajax make sure the viewstate is set correctly.

thanks tony - ill give it a go and let u know how i get on!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.