-
Does any one have code out there to sort arrays or recordsets?
Here's the problem:
I'm bringing back a recordset from the database and loading a data grid. When someone clicks on the column, I want to be able to sort either ascending or descending. So I have to sort this recordset.
I don't want to have to call the stored procedure, I want to reduce the calls and place data on the client side.
Can anyone help or come up with a good alternative. Or there any data grids that do this for me?
I'd appreciate your help.
Mike
-
I found this code that uses an ADO Control on the form to sort by column in a dataGrid.
Code:
Private Sub DataGrid1_HeadClick(ByVal ColIndex As Integer)
Adodc1.RecordSource = "Select * From user Order By " & _
DataGrid1.Columns(ColIndex).DataField
Adodc1.Refresh
End Sub
Maybe you could adapt it to your situation.
-
If you are willing to change from a data grid to a ListView, the you can use the ListView's SortOrder property to sort automatically descending or ascending by any column you want. You can also allow the user to do the sorting whichever way he/she wants.
-
I adapted the QuickSort algorithm to sort any 2-dimensional array based on any column, assuming that the first dimension is the row and the second dimension is the column:
Array(0,0) = First row first column
Array(0,3) = First row fourth column
Creat a new module and paste all this code in. Then run TestSort from the immediate window to see it in action.
Code:
Option Explicit
Option Compare Text
Private mlngSort As Long ' Column # to sort on (first column = 0)
Private mlngColFirst As Long ' LBound of second index
Private mlngColLast As Long ' UBound of second index
Public Function TestSort()
Dim lngRow As Long
Dim lngCol As Long
Dim MyArray() As Variant
Randomize Timer
ReDim MyArray(10 To 19, 0 To 3)
For lngRow = LBound(MyArray, 1) To UBound(MyArray, 1)
MyArray(lngRow, 0) = lngRow
MyArray(lngRow, 1) = Int((199 - 100 + 1) * Rnd + 100)
MyArray(lngRow, 2) = MyArray(lngRow, 1)
MyArray(lngRow, 3) = Int((199 - 100 + 1) * Rnd + 100)
Debug.Print lngRow & ": " & MyArray(lngRow, 0) & ", " & MyArray(lngRow, 1) & ", " & MyArray(lngRow, 2) & ", " & MyArray(lngRow, 3)
Next
SortArray MyArray, 2
Debug.Print "Sorted"
For lngRow = LBound(MyArray, 1) To UBound(MyArray, 1)
Debug.Print lngRow & ": " & MyArray(lngRow, 0) & ", " & MyArray(lngRow, 1) & ", " & MyArray(lngRow, 2) & ", " & MyArray(lngRow, 3)
Next
End Function
' Entry Function
Public Function SortArray(ByRef pvarArray As Variant, ByVal plngSortColumn As Long)
mlngSort = plngSortColumn
mlngColFirst = LBound(pvarArray, 2)
mlngColLast = UBound(pvarArray, 2)
ReDim mvarExchange(mlngColFirst To mlngColLast)
QuickSort pvarArray, LBound(pvarArray, 1), UBound(pvarArray, 1)
End Function
Private Function QuickSort(ByRef pvarArray As Variant, ByVal plngLeft As Long, ByVal plngRight As Long)
Dim lng As Long
Dim lngFirst As Long
Dim lngLast As Long
Dim varMid As Variant
Dim varExchange As Variant
lngFirst = plngLeft
lngLast = plngRight
varMid = pvarArray((plngLeft + plngRight) / 2, mlngSort)
Do
Do While pvarArray(lngFirst, mlngSort) < varMid And lngFirst < plngRight
lngFirst = lngFirst + 1
Loop
Do While varMid < pvarArray(lngLast, mlngSort) And lngLast > plngLeft
lngLast = lngLast - 1
Loop
If lngFirst <= lngLast Then
For lng = mlngColFirst To mlngColLast
varExchange = pvarArray(lngFirst, lng)
pvarArray(lngFirst, lng) = pvarArray(lngLast, lng)
pvarArray(lngLast, lng) = varExchange
Next
lngFirst = lngFirst + 1
lngLast = lngLast - 1
End If
Loop Until lngFirst > lngLast
If plngLeft < lngLast Then QuickSort pvarArray, plngLeft, lngLast
If lngFirst < plngRight Then QuickSort pvarArray, lngFirst, plngRight
End Function
