Imports Telerik.WebControls
Imports System.Data
Imports System.Data.OleDb


Public Class MyCustomFilteringColumn
    Inherits GridTemplateColumn

    Public Shared connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & System.Web.HttpContext.Current.Server.MapPath("~/App_Data/Nwind.mdb")
    Public Function GetDataTable(ByVal query As String) As DataTable
        Dim connection1 As New OleDbConnection(connectionString)
        Dim adapter1 As New OleDbDataAdapter
        adapter1.SelectCommand = New OleDbCommand(query, connection1)
        Dim table1 As New DataTable
        connection1.Open()
        Try
            adapter1.Fill(table1)
        Finally
            connection1.Close()
        End Try
        Return table1
    End Function

    Protected Overrides Sub SetupFilterControls(ByVal cell As TableCell)
        Dim DropDownList1 As DropDownList = New DropDownList
        DropDownList1.ID = "DropDownList1"
        DropDownList1.Width = Unit.Percentage(100)
        DropDownList1.AutoPostBack = True
        DropDownList1.DataTextField = Me.DataField
        DropDownList1.DataValueField = Me.DataField
        AddHandler DropDownList1.SelectedIndexChanged, AddressOf DropDownList1_SelectedIndexChanged
        Dim table As DataTable = GetDataTable(String.Format("SELECT DISTINCT {0} FROM {1}", Me.DataField, "Customers"))
        Dim row As DataRow = table.NewRow
        row(Me.DataField) = ""
        table.Rows.InsertAt(row, 0)
        DropDownList1.DataSource = table
        cell.Controls.Add(DropDownList1)
    End Sub

    Protected Overrides Sub SetCurrentFilterValueToControl(ByVal cell As TableCell)
        If Not (Me.CurrentFilterValue = "") Then
            CType(cell.Controls(0), DropDownList).Items.FindByText(Me.CurrentFilterValue).Selected = True
        End If
    End Sub

    Protected Overrides Function GetCurrentFilterValueFromControl(ByVal cell As TableCell) As String
        Dim currentValue As String = CType(cell.Controls(0), DropDownList).SelectedItem.Value
        Me.CurrentFilterFunction = Microsoft.VisualBasic.IIf((Not (currentValue = "")), GridKnownFunction.EqualTo, GridKnownFunction.NoFilter)
        Return currentValue
    End Function

    Private Sub DropDownList1_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs)
        CType((CType(sender, DropDownList).Parent.Parent), GridFilteringItem).FireCommandEvent("Filter", New Pair)
    End Sub
End Class
