This tip shows you how to sort a DataGrid ascending and descending for any column while retaining the filter criteria.

Create an OleDbCommand that has a stored procedure from MS Access, e.g.:

"Select * From tblOrder Where (([tblOrder].[Shipped]=[@Shipped] Or" &_ 
"[@Shipped]=”All”) And ([tblOrder].[Invoiced]=[@Invoiced] Or" &_ 
"[@Invoiced]=”All”) And ([tblOrder].[OrderDate]>=" &_ 
"[@SDate] And [tblOrder].[OrderDate]<=(DateAdd("d",1,[@EDate]))))" &_
"Order By [tblOrder].[OrderNumber] Desc;"

Add these parameters to the parameter collection for the command.

The other actions, such as connecting to the Access database, filling the DataGrid on Page.Load, and using the other controls on the form page, are not discussed here.

Function CreateDataTable() As DataTable


        OleDbCommand1.Parameters("@SDate").Value = TextBox1.Text
        OleDbCommand1.Parameters("@EDate").Value = TextBox2.Text


        If DropDownList1.SelectedIndex = "0" Then
           OleDbCommand1.Parameters("@Shipped").Value = “All”
        Else
           OleDbCommand1.Parameters("@Shipped").Value = _
             DropDownList1.SelectedItem.Text
        End If


        If DropDownList2.SelectedIndex = "0" Then
          OleDbCommand1.Parameters("@Invoiced").Value = “All”
        Else
          OleDbCommand1.Parameters("@Status").Value =  _
          DropDownList2.SelectedItem.Text
        End If


        Dim adp As New OleDb.OleDbDataAdapter(OleDbCommand1)
        Dim ds As New DataSet


        adp.Fill(ds, "tblOrder")


        Return ds.Tables("tblOrder")


    End Function


  Sub SortCommand_OnClick(ByVal Source As Object, _
      ByVal E As DataGridSortCommandEventArgs)


        Dim dataGrid As DataGrid = Source
        Dim strSort = dataGrid.Attributes("SortExpression")
        Dim strASC = dataGrid.Attributes("SortASC")


        dataGrid.Attributes("SortExpression") = E.SortExpression
        dataGrid.Attributes("SortASC") = "Yes"


        If E.SortExpression = strSort Then


            If strASC = "Yes" Then
                dataGrid.Attributes("SortASC") = "No"
            Else
                dataGrid.Attributes("SortASC") = "Yes"
            End If


        End If


        Dim DTable As DataTable = CreateDataTable()
        Dim DView As DataView = New DataView(DTable)
        DView.Sort = dataGrid.Attributes("SortExpression")


        If dataGrid.Attributes("SortASC") = "No" Then
            DView.Sort &= " DESC"
        End If


        dataGrid.DataSource = DView


        dataGrid.DataBind()


    End Sub

Please do not forget to go to the Property Builder for the DataGrid and, on the General tab, check the box to allow sorting. Uncheck the auto generate column property and mark all the columns as bound columns with the sorting expression as the corresponding field.  Also, you will need to change to the HTML view and add the following code for sorting.

(OnSortCommand="SortCommand_OnClick")

Your HTML code will look something like this (note the OnSortCommand property):

<asp:datagrid id="DataGrid1" style="Z-INDEX: 101; LEFT: 10px; 


POSITION: absolute; TOP: 144px"runat="server" 


OnSortCommand="SortCommand_OnClick"


 AllowSorting="True" Font-Size="X-Small" CellPadding="4" 


BackColor="LightCyan" BorderWidth="1px" 


BorderStyle="None" BorderColor="#3366CC" 


AutoGenerateColumns="False">

I hope you find this method of bidirectional sorting while maintaining the criteria state easy to use.