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.