Sort WebListBox by Column Headings

Targets Web
1

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]

2

@David Cox , Nice share! I’ve never thought about loading the data into a temp SQL Lite DB table to do the sorting, granted my listboxes are usually already filled from a SQL DB so I do the sorting already in the SQL command. Otherwise I usually like to use the cell tag or row tag to provide the values for sorting so for instance, it can compare a Double by value instead of by String representation. Providing a custom compare delegate (either for all columns or individual columns) also provides a lot of power and reusability.

One thing that might be nice to add to yours is changing the column headers with the Unicode up/down arrows to show sorted direction. I also usually change the column style.

c.header = c.header + if(OrderDescending, " ?"," ?")

Also when determining if all the columns have values you might want to also filter out “$” etc… A regular expression might work best for this.

3

Good ideas, I’ll try to add them. Thank you!

4

Hi David,

I’m trying to implement your solution. Can you tell me what commonWAD.isDatabaseRecordSetErrorWAD(memoryDB, rs, False) is about?

Thanks!

5

It works very well. I just made a few changes. I added “COLLATE NO CASE” to the column creation SQL so words composed with Uppercases dont affect the “sortAscending detection”.

Thanks again for sharing!

6

It’s a generic method I call after every time I retrieve a recordSet. If the recordSet is nil or the database has an error it displays a MsgBox with the error and returns True. I can then decide if I want to break out of the main method.

I can turn off the MsgBox on error if I’m running in a thread too.

Here it is:

[quote]Function isDatabaseRecordSetErrorWAD(myDatabase As Database, myRecordSet As RecordSet, displayErrors As Boolean = True, myRecordSetMessage As String = “Sorry, but there was a problem retrieving the database records.”) As Boolean
'if there was a problem with either the database, or the recordset, then display the error and return false

if myDatabase = nil then
if displayErrors then commonWAD.doDisplayErrorWAD(myRecordSetMessage)
Return True
elseif myDatabase.Error then
if displayErrors then commonWAD.doDisplayDatabaseErrorWAD(myDatabase)
Return True
elseif myRecordSet = nil then
if displayErrors then commonWAD.doDisplayErrorWAD(myRecordSetMessage)
Return True
end if

Return False

End Function
[/quote]

7

Thanks again!