ListBox sort a date column

Getting Started
1

Hi,

After read a SQLite record, I load in ListBox.
But I have a column like a DATE, how can I sort this column ?

I have this code :

[code] If B_DataOpen Then
DataList.DeleteAllRows

Dim sql As String
sql = "SELECT * FROM PERSONNE"

Dim data As RecordSet
data = mDB.SQLSelect(sql)

If mDB.Error Then
  MsgBox("DB Error: " + mDB.ErrorMessage)
  Return
End If

If data <> Nil Then
  While Not data.EOF
    DataList.AddRow(_
    data.Field("ID").Value, _
    data.Field("Nom").StringValue, _
    data.Field("Prenom").StringValue, _
    data.Field("Naissance").StringValue,_
    data.Field("CA").Value)
    data.MoveNext
  Wend
  data.Close
End If

Else
MsgBox(“Create the database first, the table and add the data.”)
End If[/code]

And if I change like this :

…
data.Field(“Naissance”).DateValue,_
data.Field(“CA”).CurrencyValue)
…

why it’s not correct ?
How Can I tell this column of ListBox is a date column ?

2

You don’t, you display the data as a string, but store the date in a CellTag. When it comes time to sort, use the CompareRows event to check which column you’re sorting, then use the CellTag to make the comparison if it’s the date column. Ditto for the currency column.

However, if you can, you’re better off using ORDER BY to sort your data straight out of the database.

3

Kem, I can’t you understand !
What is a CellTag ?

I must precise the date is like this 30/12/2014 (French format : dd/mm/yyyy)

I know I can sort directly with ORDER BY of database,but the data it’s in Listbox.
But how can the user sort the columns ?

If I click on the header of Listbox, how can I know the nb of column and the direction (A->Z or Z->A) of this.

it’s possible to attach directly the column’s Listbox and the column’s table ?
Like this I can sort the database and the listbox it’s automatically sorted.

And if I use ORDER BY, I must take SELECT before, and my selection can be changed.

4

Look at the Language Reference for the documentation on ListBox. Specifically, CellTag.

In short, while a Cell is visible to users, CellTag lets you store information next to that Cell that is not visible, but that can be used internally. In your case, when you’re setting the Cell, you will also set the CellTag.

        DataList.AddRow(_
        data.Field("ID").Value, _
        data.Field("Nom").StringValue, _
        data.Field("Prenom").StringValue, _
        data.Field("Naissance").StringValue,_
        data.Field("CA").Value)
        DataList.CellTag( DataList.LastIndex, 3 ) = data.Field("Naissance").DateValue

Next, look at the documentation for CompareRows event. There, you will check to see which column is being sorted and, if it’s column 3, you would compare the CellTag( Row1, column ) to CellTag( Row2, column ) and set Result accordingly. Then you would return True to force Xojo to use your result.

5

Hello,
another way is to add an extra column to the listbox and put the Date.TotalSeconds value in it. When the table header of your date column is clicked, then sort by the Date.TotalSeconds row.
You can hide this additional listbox row.

6

Torsten, I don’t recommend that you do this. While this was the way you had to do this in the past, the CellTag is now available and should be taken advantage of.

7

Yes, CellTag is the better way to do this. For a working example that shows how to sort using the ListBox.CompareRows event handler, check out the example included with Xojo:

Examples/Desktop/Controls/ListBoxExample

Also, the Using the ListBox webinar might be useful.

8

Kem,

after having a closer look, CompareRows together with CellTag is a very nice solution.
Then I would save Date.TotalSeconds (language independend) in the CellTag and used it for CompareRows.

New lesson learned :slight_smile: