[Solved] How to add Sorting & Filtering to this Grid

[Chase Florell]

I have just implemented CustomPaging to my RadGrid, but now I seem to have broken sorting and filtering, as well as the fact that I can't get it to display the records in REVERSE order straight from the db (99,98,97,etc... the Order By [errDate] Desc doesn't seem to be doing exactly what I'm looking for).

Here is my Grid

<telerik:RadGrid ID="RadGrid1" PageSize="15" AllowFilteringByColumn="true" runat="server"
            AutoGenerateColumns="false" AllowAutomaticDeletes="false" AllowAutomaticUpdates="false"
            AllowAutomaticInserts="false" AllowSorting="true" AllowPaging="true" AllowCustomPaging="true"
            OnNeedDataSource="RadGrid1_NeedDataSource">
            <MasterTableView TableLayout="Auto" DataKeyNames="ID">
                <PagerStyle AlwaysVisible="true" Mode="NextPrevAndNumeric" />
                <ItemStyle Wrap="False" />
                <NoRecordsTemplate>
                    No Errors Logged at this time
                </NoRecordsTemplate>
                <NestedViewTemplate>
                    <fieldset>
                        <legend style="font-size: 14px; font-weight: bolder;">Error Details</legend>
                        <table>
                            <tr>
                                <td style="white-space: nowrap;">
                                    Error URL:
                                </td>
                                <td style="width: 100%;">
                                    <%#DataBinder.Eval(Container.DataItem, "errURL")%>
                                </td>
                            </tr>
                            <tr>
                                <td style="white-space: nowrap;">
                                    Error Source:
                                </td>
                                <td>
                                    <%#DataBinder.Eval(Container.DataItem, "errSource")%>
                                </td>
                            </tr>
                            <tr>
                                <td style="white-space: nowrap;">
                                    Exception:
                                </td>
                                <td>
                                    <%#DataBinder.Eval(Container.DataItem, "errEx")%>
                                </td>
                            </tr>
                            <tr>
                                <td style="white-space: nowrap;">
                                    Stack Trace:
                                </td>
                                <td>
                                    <%#DataBinder.Eval(Container.DataItem, "errStack")%>
                                </td>
                            </tr>
                            <tr>
                                <td style="white-space: nowrap;">
                                    Additional Notes:
                                </td>
                                <td>
                                    <%#DataBinder.Eval(Container.DataItem, "errNotes")%>
                                </td>
                            </tr>
                        </table>
                    </fieldset>
                    <fieldset>
                        <legend style="font-size: 14px; font-weight: bolder;">User Details</legend>
                        <table>
                            <tr>
                                <td style="white-space: nowrap;">
                                    User Name:
                                </td>
                                <td style="width: 100%;">
                                    <%#DataBinder.Eval(Container.DataItem, "errUser")%>
                                </td>
                            </tr>
                            <tr>
                                <td style="white-space: nowrap;">
                                    User IP Address:
                                </td>
                                <td>
                                    <%#DataBinder.Eval(Container.DataItem, "errIP")%>
                                </td>
                            </tr>
                            <tr>
                                <td style="white-space: nowrap;">
                                    User Browser:
                                </td>
                                <td>
                                    <%#DataBinder.Eval(Container.DataItem, "errBrowser")%>
                                </td>
                            </tr>
                            <tr>
                                <td style="white-space: nowrap;">
                                    User Operating System:
                                </td>
                                <td>
                                    <%#DataBinder.Eval(Container.DataItem, "errOS")%>
                                </td>
                            </tr>
                        </table>
                    </fieldset>
                    <div style="height: 20px;">
                        &nbsp;</div>
                </NestedViewTemplate>
                <Columns>
                    <telerik:GridBoundColumn DataField="ID" HeaderText="Error" UniqueName="ID" />
                    <telerik:GridBoundColumn DataField="errUser" HeaderText="User" UniqueName="errUser" />
                    <telerik:GridTemplateColumn DataField="errMessage" HeaderText="Message" UniqueName="Message">
                        <ItemTemplate>
                            <asp:Label ID="lblMessage" runat="server" Text='<%# ShortenField(DataBinder.Eval(Container.DataItem, "errMessage")) %>' />
                        </ItemTemplate>
                    </telerik:GridTemplateColumn>
                    <telerik:GridTemplateColumn Reorderable="true" DataField="errDate" HeaderText="Date"
                        UniqueName="Date">
                        <ItemStyle Width="100px" />
                        <ItemTemplate>
                            <asp:Label ID="lblDate" runat="server" Text='<%# FormatDate(DataBinder.Eval(Container.DataItem, "errDate")) %>' />
                        </ItemTemplate>
                    </telerik:GridTemplateColumn>
                </Columns>
            </MasterTableView>
        </telerik:RadGrid>

And my Code Behind

Partial Class App_Windows_Admin_Windows_Health_Monitor_Admin
    Inherits System.Web.UI.Page

    Function FormatDate(ByVal input As DateTime) As String
        Return Format(input, "MMMM dd, yyyy")
    End Function

    Function ShortenField(ByVal input As String) As String
        If input.Length > 50 Then
            Return input.Substring(0, 50) & "..."
        End If
    End Function

    Protected Sub Page_PreInit(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.PreInit
        If Not Page.IsPostBack Then
            Dim HealthMonitorDC As New DAL.HealthMonitorDataContext
            VirtualItemCount = Integer.Parse(HealthMonitorDC.bt_HealthMonitor_GetRecordCount().ReturnValue)
        End If
    End Sub

    Private Shared VirtualItemCount As Integer

    Protected Sub RadGrid1_NeedDataSource(ByVal source As Object, ByVal e As Telerik.Web.UI.GridNeedDataSourceEventArgs)


        RadGrid1.MasterTableView.VirtualItemCount = VirtualItemCount
        RadGrid1.VirtualItemCount = VirtualItemCount

        Dim startRowIndex As Integer = (RadGrid1.CurrentPageIndex * RadGrid1.PageSize)
        Dim maximumRows As Integer = RadGrid1.PageSize

        Dim HealthMonitorDC As New DAL.HealthMonitorDataContext

        Dim r = HealthMonitorDC.bt_HealthMonitor_GetAll(startRowIndex, maximumRows)
        RadGrid1.DataSource = r
    End Sub

End Class

My SQL Queries look like this

ALTER PROCEDURE [dbo].[bt_HealthMonitor_GetRecordCount]

AS
SET NOCOUNT ON

return (Select Count(ID) As TotalRecords From bt_HealthMonitor)

And

ALTER PROCEDURE [dbo].[bt_HealthMonitor_GetAll]
(
        @StartRowIndex      int,
        @MaximumRows        int
)

AS
SET NOCOUNT ON

Select
    RowNum,
    [ID],
    [errEx],
    [errURL],
    [errSource],
    [errUser],
    [errMessage],
    [errIP],
    [errBrowser],
    [errOS],
    [errStack],
    [errDate],
    [errNotes]
From
    (Select
        [ID],
        [errEx],
        [errURL],
        [errSource],
        [errUser],
        [errMessage],
        [errIP],
        [errBrowser],
        [errOS],
        [errStack],
        [errDate],
        [errNotes],
        Row_Number() Over(Order By [ID]) As RowNum
        From dbo.[bt_HealthMonitor] t)
As DerivedTableName
Where RowNum Between @StartRowIndex And (@StartRowIndex + @MaximumRows)

Order By [errDate] Desc

Any advice on this would be greatly appreciated, thanks.

[Chase Florell]

I did fix the ordering problem by changing this...

Row_Number() Over(Order By [ID]) As RowNum

to this...

Row_Number() Over(Order By [ID] Desc) As RowNum

[Chase Florell]

Ok, I think this is working. can anyone confirm this is the right way to do it?

    Protected Sub Page_PreInit(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.PreInit
        If VirtualItemCount = Nothing Then
            Dim HealthMonitorDC As New DAL.HealthMonitorDataContext
            VirtualItemCount = Integer.Parse(HealthMonitorDC.bt_HealthMonitor_GetRecordCount().ReturnValue)
        End If
    End Sub

    Private Shared VirtualItemCount As Integer

    Protected Sub RadGrid1_NeedDataSource(ByVal source As Object, ByVal e As Telerik.Web.UI.GridNeedDataSourceEventArgs)


        RadGrid1.MasterTableView.VirtualItemCount = VirtualItemCount
        RadGrid1.VirtualItemCount = VirtualItemCount

        Dim startRowIndex As Integer = IIf((ShouldApplySortFilterOrGroup()), 0, RadGrid1.CurrentPageIndex * RadGrid1.PageSize)
        Dim maximumRows As Integer = IIf((ShouldApplySortFilterOrGroup()), VirtualItemCount, RadGrid1.PageSize)

        Dim HealthMonitorDC As New DAL.HealthMonitorDataContext

        Dim r = HealthMonitorDC.bt_HealthMonitor_GetAll(startRowIndex, maximumRows)
        RadGrid1.DataSource = r
    End Sub

    Private isGrouping As Boolean = False
    Protected Sub RadGrid1_GroupsChanging(ByVal source As Object, ByVal e As GridGroupsChangingEventArgs)
        isGrouping = True
        If e.Action = GridGroupsChangingAction.Ungroup AndAlso RadGrid1.CurrentPageIndex > 0 Then
            isGrouping = False
        End If
    End Sub

    Public Function ShouldApplySortFilterOrGroup() As Boolean
        Return RadGrid1.MasterTableView.FilterExpression <> "" _
        OrElse (RadGrid1.MasterTableView.GroupByExpressions.Count > 0 _
                OrElse isGrouping) OrElse RadGrid1.MasterTableView.SortExpressions.Count > 0
    End Function

[Iana Tsolova]

Hi Chase,

Indeed, this is a proper way to make your grid to sort or filter over the whole data when using custom paging.
You can find a similar approach is used in this online demo.

Sincerely yours,
Iana
the Telerik team

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.