Sorting Listview

[mosman]

I have a listview with 5 columnheaders, the first one is integer type and the rest are string type, when I click on the the first column header, it sorts it as a string (i.e. 1,11,2,22,3,4,5,6 and so on). could someone help with how to sort this as number?
thanks

[Johnny101]

Its kind of a pain to do becuase you have to do some wierd formatting and extra columns. I have this procedure to do this for me:
It takes an ADO recordset and a list view control and builds itself - sortable and all.

If you aren't using an ADO recordset to build it, then yuo can modify this. The essential thing is the building of the extra column headers for sorting. You can see from the code that if a field is numeric, i build a "copy" of that field in a hidden column header and when the user clicks on the visible column header, i actually sort on the hidden column. Because the listview sorts everything like a string, i format the number to some rather odd length. At least, its a format that would never occur in my data. you can, of course, change it to meet your needs.


public Function Populate(byval rs as ADODB.Recordset, lvwMain as ListView) as Boolean
Dim iCount as Integer
Dim kCounter as Integer
Dim iOffset as Integer
Dim iSortBy as Integer
Dim iExtra as Integer
Dim xHead as ColumnHeader
Dim yHead as ColumnHeader
Dim itm as ListItem

on error GoTo EH

lvwMain.ListItems.Clear
lvwMain.ColumnHeaders.Clear

lvwMain.View = lvwReport

With lvwMain 'build the listview column headers
iOffset = rs.Fields.Count
for iCount = 1 to rs.Fields.Count
set xHead = .ColumnHeaders.Add(iCount, , _
rs.Fields(iCount - 1).Name, rs.Fields(iCount - 1).ActualSize)
xHead.Tag = rs.Fields(iCount - 1).Type
next iCount

'build the sorting columns for columns that are non string data types.
for iCount = 1 to .ColumnHeaders.Count
Select Case .ColumnHeaders(iCount).Tag
Case adInteger
set yHead = .ColumnHeaders.Add(.ColumnHeaders.Count + 1, , , 0)
.ColumnHeaders(iCount).Tag = "Numeric"
Case adDate
set yHead = .ColumnHeaders.Add(.ColumnHeaders.Count + 1, , , 0)
.ColumnHeaders(iCount).Tag = "date"
Case adDouble
set yHead = .ColumnHeaders.Add(.ColumnHeaders.Count + 1, , , 0)
.ColumnHeaders(iCount).Tag = "Numeric"
Case adSingle
set yHead = .ColumnHeaders.Add(.ColumnHeaders.Count + 1, , , 0)
.ColumnHeaders(iCount).Tag = "Numeric"
Case else
.ColumnHeaders(iCount).Tag = .ColumnHeaders(iCount).Index
End Select
next iCount
End With

set xHead = nothing
set yHead = nothing

'now build each list item with subitems and it's respective sorting column
If Not rs.EOF then
iExtra = 1
While Not rs.EOF

set itm = lvwMain.ListItems.Add(, , rs.Fields(0) & "")

'have to check the first column as well
If IsNumeric(rs.Fields(0)) then
If lvwMain.ColumnHeaders(1).Tag = "Numeric" then
itm.SubItems(rs.Fields.Count) = Format(rs.Fields(0), "0000000000")
'iExtra = iExtra + 1
End If
ElseIf IsDate(rs.Fields(0)) then
If lvwMain.ColumnHeaders(1).Tag = "date" then
itm.SubItems(rs.Fields.Count) = Format(rs.Fields(kCounter), "YYYY/MM/DD")
'iExtra = iExtra + 1
End If
End If

for kCounter = 1 to rs.Fields.Count - 1
'fill the rows with data from the table
itm.SubItems(kCounter) = rs.Fields(kCounter) & ""
iExtra = 1
'fill the sort by columns for the non-string columns
If IsNumeric(rs.Fields(kCounter)) then
If lvwMain.ColumnHeaders(kCounter + 1).Tag = "Numeric" then
itm.SubItems(rs.Fields.Count + iExtra) = Format(rs.Fields(kCounter), "0000000000")
iExtra = iExtra + 1
End If
ElseIf IsDate(rs.Fields(kCounter)) then
If lvwMain.ColumnHeaders(kCounter + 1).Tag = "date" then
itm.SubItems(rs.Fields.Count + iExtra) = Format(rs.Fields(kCounter), "YYYY/MM/DD")
iExtra = iExtra + 1
End If
End If

next kCounter

rs.MoveNext

Wend
End If

Call AdjustColumnWidth(true)

'done setting up the sort fields, so now set up the tag so that when headers
'are clicked, they can be sorted correctly
iExtra = 1
for iCount = 0 to rs.Fields.Count - 1
Select Case rs.Fields(iCount).Type
Case adInteger
lvwMain.ColumnHeaders(iCount + 1).Tag = rs.Fields.Count + iExtra
iExtra = iExtra + 1
Case adDate
lvwMain.ColumnHeaders(iCount + 1).Tag = rs.Fields.Count + iExtra
iExtra = iExtra + 1
Case adDouble
lvwMain.ColumnHeaders(iCount + 1).Tag = rs.Fields.Count + iExtra
iExtra = iExtra + 1
Case adSingle
lvwMain.ColumnHeaders(iCount + 1).Tag = rs.Fields.Count + iExtra
iExtra = iExtra + 1
Case else
lvwMain.ColumnHeaders(iCount + 1).Tag = iCount + 1
End Select
next iCount

for iCount = rs.Fields.Count + 1 to lvwMain.ColumnHeaders.Count
lvwMain.ColumnHeaders(iCount).Width = 0
next

me.Refresh

Populate = true
Exit Function

EH:
Populate = false
End Function




hope this helps,
John

John Pirkey
MCSD
www.ShallowWaterSystems.com

[Chris Eastwood]

There's a good article and code available on the CodeGuru site, under Controls->ListView (see http://codeguru.developer.com/vb/articles/1818.shtml)

- This provides sorting for numeric and date columns - without using the API or callback routines.

Chris Eastwood

CodeGuru - the website for developers
http://codeguru.developer.com/vb