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