I have written a routine that allows you to add row sorting on any WebListBox when a person clicks on a column header please use it as you wish! I have placed it in every WebListBox I have and it works really well. It also uses a RAM-based SQLite database which I sometimes find easiest for working with variable data.
It checks to see if the column is full of numbers (it removes comma’s and spaces) and it so sorts numerically, otherwise it sorts alphabetically. If it sees the column is already sorted in ascending order, it re-sorts in descending order.
Place the following inside the HeaderPressed(Column As Integer) Event:
commonWEonlyWAD.doSortListBoxWAD(me, Column)
This is the Method it runs:
[code]Sub doSortListBoxWAD(myWebListBox As WebListBox, sortColumn As Integer)
Dim memoryDB As New SQLiteDatabase
Dim rec As New DatabaseRecord
Dim rs As RecordSet
Dim numericalColumn As Boolean = True
Dim tempSQL As String
Dim tempString As String
Dim sortAscending As Boolean = True
if myWebListBox = nil then Return
if myWebListBox.ColumnCount < 1 then Return 'no point sorting when no columns!
if myWebListBox.RowCount <= 1 then Return 'no point sorting when no rows, or only one row!
if not memoryDB.Connect then Return 'can’t access the database
'check if the column is already sorted by going down the column until we find two in descending order, otherwise exit and keep as ascending order. This fixes the issue when lots of values are the same.
for tempInt As Integer = 1 to myWebListBox.RowCount - 1
if (myWebListBox.Cell(tempInt - 1, sortColumn).val = 0 and myWebListBox.Cell(tempInt - 1, sortColumn) < myWebListBox.Cell(tempInt, sortColumn)) or ReplaceAll(ReplaceAll(myWebListBox.Cell(tempInt - 1, sortColumn), “,”, “”), " ", “”).val < ReplaceAll(ReplaceAll(myWebListBox.Cell(tempInt, sortColumn), “,”, “”), " ", “”).val then
sortAscending = False
Exit for
end if
next
'build an in-memory SQLite database to hold the column data
tempSQL = “CREATE TABLE sortTable (”
for tempInt As Integer = 0 to myWebListBox.ColumnCount - 1
tempSQL = tempSQL + “myColumn” + str(tempInt) + " TEXT, "
next
tempSQL = tempSQL + “extraSortColumnWAD DOUBLE”
tempSQL = tempSQL + “)”
memoryDB.SQLExecute(tempSQL)
if memoryDB.Error then Return 'not sure why we got an error, but leave the table alone
'place the WebListBox data into the database
for tempInt As Integer = 0 to myWebListBox.RowCount - 1
rec = New DatabaseRecord
for tempInt2 As Integer = 0 to myWebListBox.ColumnCount - 1
rec.Column(“myColumn” + str(tempInt2)) = myWebListBox.Cell(tempInt, tempInt2)
'check if the chosen column is full of only numerical data
if sortColumn = tempInt2 and numericalColumn then
tempString = myWebListBox.Cell(tempInt, tempInt2)
tempString = ReplaceAll(tempString, “,”, “”) 'remove commas
tempString = ReplaceAll(tempString, " ", “”) 'remove spaces
if tempString <> “” and tempString <> str(tempString.val) and tempString.val < 1000000 then
numericalColumn = False 'check if what’s left over is a number
end if
end if
next
memoryDB.InsertRecord(“sortTable”, rec)
if memoryDB.Error then Return 'not sure why we got an error, but leave the table alone
next
'extract the sorted table from the database, wipe the table then return the data
tempSQL = "SELECT * FROM sortTable "
if numericalColumn then
memoryDB.SQLExecute(“UPDATE sortTable SET extraSortColumnWAD = REPLACE(REPLACE(myColumn” + str(sortColumn) + ", ’ ', ‘’), ‘,’, ‘’) ") 'convert the text to a number
if memoryDB.Error then Return 'not sure why we got an error, but leave the table alone
tempSQL = tempSQL + "ORDER BY extraSortColumnWAD " + if(sortAscending, “ASC”, “DESC”)
else
tempSQL = tempSQL + “ORDER BY myColumn” + str(sortColumn) + " " + if(sortAscending, “ASC”, “DESC”)
end if
rs = memoryDB.SQLSelect(tempSQL)
if commonWAD.isDatabaseRecordSetErrorWAD(memoryDB, rs, False) then Return
myWebListBox.DeleteAllRows
while not rs.EOF
myWebListBox.AddRow “”
for tempInt As Integer = 0 to rs.FieldCount - 2 'don’t place extraSortColumnWAD into the original WebListBox!
myWebListBox.Cell(myWebListBox.LastIndex, tempInt) = rs.IdxField(tempInt + 1).StringValue
next
rs.MoveNext
wend
if myWebListBox.RowCount > 0 then myWebListBox.ListIndex = 0 'highlight the first row
End Sub
[/code]