[Solved] Grid Filter Issue

[Julian Welby-Everard]

I am having an issue with RadGrid.

What I want to do.

Display a data grid allow paging and filtering, well that sounds easy but....

My recordset may have 100000’s of rows so I have implemented AJAX to load the data page at a time, and use VirtualItemCount to set the total number of rows.

This all works fine.

I have tried to add filtering to the grid and again this works sortof. The issue I have is as follows, when a user selects the filter value, this is done in the SQL backend as there is more that 1 possible column to be filtered and the total number of rows is returned and the first page of results therefore changing the total number of pages.

What I am seeing.

When the filter is applied it filters the result in the column first column even through the data set is already filtered.  What I want to do is just display the rows returned in the dataset.

Also I want to disable the filter type list and just have it as a click and go button.

Is there any way of stopping the grid filtering the data as I already do this in my SQL. I still want the filter box at the top of the screen.

Code below...

Mark up

 

<%@ Page Language="VB" MasterPageFile="~/CRM.master" AutoEventWireup="false" CodeFile="Default.aspx.vb"
    Inherits="_Default" Title="Untitled Page" %>

<%@ Register Assembly="Telerik.Web.UI" Namespace="Telerik.Web.UI" TagPrefix="telerik" %>
<asp:Content ID="Content1" ContentPlaceHolderID="head" runat="Server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="cph" runat="Server">
    <telerik:RadAjaxManager ID="RadAjaxManager1" runat="server">
        <AjaxSettings>
            <telerik:AjaxSetting AjaxControlID="RadGrid1">
                <UpdatedControls>
                    <telerik:AjaxUpdatedControl ControlID="RadGrid1" />
                </UpdatedControls>
            </telerik:AjaxSetting>
        </AjaxSettings>
    </telerik:RadAjaxManager>
    <telerik:RadGrid
        ID="RadGrid1"
        runat="server"
        AutoGenerateColumns="False"
        OnNeedDataSource="RadGrid1_NeedDataSource"
        OnItemCommand="RadGrid1_ItemCommand"
        AllowPaging="True"
        AllowCustomPaging="True"
        GridLines="None"
        ShowFooter="True" AllowFilteringByColumn="True">
        <HeaderContextMenu EnableTheming="True">
            <CollapseAnimation Type="OutQuint" Duration="200"></CollapseAnimation>
        </HeaderContextMenu>
        <PagerStyle Mode="NextPrevNumericAndAdvanced" />
        <MasterTableView>
            <RowIndicatorColumn>
                <HeaderStyle Width="20px"></HeaderStyle>
            </RowIndicatorColumn>
            <ExpandCollapseColumn>
                <HeaderStyle Width="20px"></HeaderStyle>
            </ExpandCollapseColumn>
        </MasterTableView>
        <FilterMenu EnableTheming="True">
            <CollapseAnimation Type="OutQuint" Duration="200"></CollapseAnimation>
        </FilterMenu>
    </telerik:RadGrid>
    <br />
</asp:Content>

Code behind

Imports CRM.DAL
Imports System.Data
Imports Telerik.Web.UI

Partial Class _Default
    Inherits System.Web.UI.Page

    Private dt As DataTable
    Private Shared maxRows As Integer
    Private Shared currentFilter As String

    Private Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Me.RadGrid1.MasterTableView.Columns.Clear()
        Dim gridColumn As GridBoundColumn = New GridBoundColumn()
        gridColumn.AllowFiltering = True
        gridColumn.DataField = "ContactSurname"
        gridColumn.HeaderText = "Surname"
        gridColumn.UniqueName = "ContactSurname"
        RadGrid1.MasterTableView.Columns.Add(gridColumn)

        gridColumn = New GridBoundColumn()
        gridColumn.AllowFiltering = False
        gridColumn.DataField = "ContactFirstForename"
        gridColumn.HeaderText = "First Forename"
        gridColumn.UniqueName = "ContactFirstForename"
        RadGrid1.MasterTableView.Columns.Add(gridColumn)

        gridColumn = New GridBoundColumn()
        gridColumn.AllowFiltering = False
        gridColumn.DataField = "ClientName"
        gridColumn.HeaderText = "Client Name"
        gridColumn.UniqueName = "ClientName"
        RadGrid1.MasterTableView.Columns.Add(gridColumn)

        RadGrid1.VirtualItemCount = maxRows
        RadGrid1.GroupingSettings.CaseSensitive = False
    End Sub

    Protected Sub RadGrid1_NeedDataSource(ByVal source As Object, ByVal e As GridNeedDataSourceEventArgs)
        dt = GetDataTable(currentFilter, RadGrid1.CurrentPageIndex, RadGrid1.PageSize)
        Me.RadGrid1.DataSource = dt
    End Sub

    Protected Sub RadGrid1_ColumnCreating(ByVal sender As Object, ByVal e As GridColumnCreatingEventArgs)
        If (e.ColumnType = GetType(GridBoundColumn).Name) Then
            Dim gridColumn As GridBoundColumn = New GridBoundColumn()
            gridColumn.DataField = CType(e.Column, GridBoundColumn).DataField
            gridColumn.HeaderText = CType(e.Column, GridBoundColumn).HeaderText
            gridColumn.UniqueName = CType(e.Column, GridBoundColumn).UniqueName
            gridColumn.AllowFiltering = CType(e.Column, GridBoundColumn).AllowFiltering
            e.Column = gridColumn
        End If
    End Sub

    Protected Sub RadGrid1_ItemCommand(ByVal source As Object, ByVal e As GridCommandEventArgs)
        If (e.CommandName = RadGrid.FilterCommandName) Then
            Dim column As GridBoundColumn = e.Item.OwnerTableView.Columns(0)
            If column.AllowFiltering Then
                dt = GetDataTable(column.CurrentFilterValue, RadGrid1.CurrentPageIndex, RadGrid1.PageSize)
                currentFilter = column.CurrentFilterValue
                column.CurrentFilterValue = String.Empty
                column.CurrentFilterFunction = GridKnownFunction.NoFilter

                RadGrid1.DataSource = dt
                RadGrid1.MasterTableView.FilterExpression = String.Empty
                RadGrid1.Rebind()

                RadGrid1.VirtualItemCount = maxRows
                RadGrid1.GroupingSettings.CaseSensitive = False
            End If
        End If
    End Sub

    Public Shared Function GetDataTable(ByVal query As String, ByVal CurrentPageIndex As Integer, ByVal PageSize As Integer) As DataTable
        Dim myDataTable As DataTable
        If query = String.Empty Then
            query = "%"
        End If
        Dim ds As DataSet = DAL.SelectContacts(query, CurrentPageIndex, PageSize)
        myDataTable = ds.Tables(0)
        maxRows = ds.Tables(1).Rows(0).Item("TotalRecords")
        myDataTable.CaseSensitive = False
        Return myDataTable
    End Function

End Class

[Iana Tsolova]

Hi Julian,

I suggest that you define your custom FilterTemplate in order to achieve the desired functionality with the "go button". You can also canceling the default grid filtering functionality as below:

Protected Sub RadGrid1_ItemCommand(ByVal source As Object, _
     ByVal e As GridCommandEventArgs) Handles RadGrid1.ItemCommand
  If e.CommandName = RadGrid.FilterCommandName Then
    e.Canceled = True
    RadGrid1.Rebind()
  End If
End Sub

Find more about custom filtering with RadGrid in the following online resources:
http://www.telerik.com/help/aspnet-ajax/radgridfiltertemplate.html
http://www.telerik.com/help/aspnet-ajax/grdcustomoptionforfiltering.html
http://www.telerik.com/help/aspnet-ajax/grdcustomfilteroptionswithhandling.html

I hope this helps.

Regards,
Iana
the Telerik team

Check out Telerik Trainer, the state of the art learning tool for Telerik products.