Maintain sorting with paging in a GridView

Question

I'm currently trying write code that will maintain the sorting preference while changing page on a GridView. My current code is working fine to sort the data based on the user sorting selection. But when changing page, the sorting doesn't work. I don't get an error but rather unsorted results.

Here's my aspx code:

<asp:GridView ID="tblAdministrators" runat="server" AutoGenerateColumns="false" EmptyDataText="No records found" PageSize="75" AllowPaging="True" AllowSorting="True">

  <Columns>
    <asp:BoundField HeaderText="Name" InsertVisible="False" DataField="FULLNAME" SortExpression="FULLNAME"></asp:BoundField>
  </Columns>
  <Columns>
   <asp:BoundField HeaderText="Active" InsertVisible="False" DataField="ACTIVE" SortExpression="ACTIVE"></asp:BoundField>
  </Columns>

</asp:GridView>

Here's my VB code:

Public Class ViewUsers
Inherits SolutionBasePage

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

    If Not Page.IsPostBack Then
        tblAdministrators.DataSource = GetData()
        tblAdministrators.DataBind()
    End If

End Sub

Private Const ASCENDING As String = " ASC"
Private Const DESCENDING As String = " DESC"

Private Function GetData() As Data.DataView

    Dim connection As OracleDBConnect = DAL.GetOracleDBConnection()
    Dim request As OracleDBRequest = Nothing
    Dim result As OracleDBResult = Nothing
    Dim trace As OracleDBChronoTrace = Nothing
    Dim status As DBStatus
    Dim sb As New StringBuilder
    Dim dv As DataView
    Dim ds As Data.DataSet = New Data.DataSet

    Try

        With sb
            .Append("SELECT FULLNAME, ACTIVE FROM USERS")
        End With

        request = New OracleDBRequest(sb.ToString, CommandType.Text)

        status = connection.Execute(request, result, trace)

        dv = New DataView(result.DataSet.Tables(0))

        If (ViewState("sortExp") IsNot Nothing) Then
            dv = New Data.DataView(result.DataSet.Tables(0))

            If (GridViewSortDirection = SortDirection.Ascending) Then
                GridViewSortDirection = SortDirection.Descending
                dv.Sort = CType(ViewState("sortExp") & DESCENDING, String)
            Else
                GridViewSortDirection = SortDirection.Ascending
                dv.Sort = CType(ViewState("sortExp") & ASCENDING, String)
            End If
        Else
            dv = result.DataSet.Tables(0).DefaultView
        End If

        Return dv

    Catch ex As Exception

    Finally
        'ds.Dispose()
        'dp.Dispose()
    End Try

End Function

Public Property GridViewSortDirection() As SortDirection
    Get
        If ViewState("sortDir") Is Nothing Then
            ViewState("sortDir") = SortDirection.Ascending
        End If

        Return CType(ViewState("sortDir"), SortDirection)
    End Get

    Set(ByVal value As SortDirection)
        ViewState("sortDir") = value
    End Set

End Property


Protected Sub tblAdministrators_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs) Handles tblAdministrators.PageIndexChanging

    tblAdministrators.PageIndex = e.NewPageIndex
    tblAdministrators.DataSource = GetData()
    tblAdministrators.DataBind()

End Sub


Protected Sub tblAdministrators_Sorting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewSortEventArgs) Handles tblAdministrators.Sorting

    ViewState("sortExp") = e.SortExpression
    tblAdministrators.DataSource = GetData()
    tblAdministrators.DataBind()

End Sub

End Class

Answer 1

The problem is whenever you call the GetData() function, your code is changing the dataview's sort property even if it's being called by PageIndexChaging event.

If (GridViewSortDirection = SortDirection.Ascending) Then
    GridViewSortDirection = SortDirection.Descending
    dv.Sort = CType(ViewState("sortExp") & DESCENDING, String)
Else
    GridViewSortDirection = SortDirection.Ascending
    dv.Sort = CType(ViewState("sortExp") & ASCENDING, String)
End If

You can modify your GetData() function to accept a SortDirection. Then, in your tblAdministrators_PageIndexChanging and tblAdministrators_Sorting events, just pass the GridViewSortDirection. Something like this:

Private Function GetData(sort As SortDirection) As Data.DataView
' your code here...
End Function

Protected Sub tblAdministrators_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs) Handles tblAdministrators.PageIndexChanging
    tblAdministrators.PageIndex = e.NewPageIndex
    tblAdministrators.DataSource = GetData(GridViewSortDirection)
    tblAdministrators.DataBind()
End Sub

Protected Sub tblAdministrators_Sorting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewSortEventArgs) Handles tblAdministrators.Sorting
    ViewState("sortExp") = e.SortExpression
    GridViewSortDirection = If(GridViewSortDirection = SortDirection.Descending, SortDirection.Ascending, SortDirection.Descending)
    tblAdministrators.DataSource = GetData(GridViewSortDirection)
    tblAdministrators.DataBind()
End Sub