Android Question Clicking column header displays numbers incorrectly.

[Shelby]

My SQLite db displays well until I click on my header column called 'PDF Page'. Instead of displaying the column page numbers vertically as, '0, 56, 57, ...' it displays them as 0,114, 115, ..135,...187, 199, 203 never getting to 56 until far below. Do you think I need to add a statement like:

SELECT
select_list
FROM
table
ORDER BY
column_1 ASC,
column_2 DESC;

which I found on sqlitetutorial.net?

I'll attach my zipped code which Klaus so generously generated for me a while ago. Whoops, can't upload 7zip file. I'm trying to compress the project so I can upload it here. Looking for thread to help.

 

[Mahares]

hem as 0,114, 115, ..135,...187, 199, 203 never getting to 56 until far below.

You did not indicate what kind of table you are dealing with, but I am assuming is B4XTable header column. The reason they are are not sorted correctly is because you probably have the B4XTable1.COLUMN_TYPE_TEXT identified as TEXT instead of B4XTable1.COLUMN_TYPE_NUMBERS when you displayed the data on the table. If they are all numeric, it is better to define the column as B4XTable1.COLUMN_TYPE_NUMBERS. They will sort correctly.

 

[Shelby]

Hopefully this zip file will suffice.

 

[Shelby]

Thanks Mahares,
I'll check those identified types but I thought they say int.

 

[Mahares]

I'll check those identified types but I thought they say int.

Not the SQLite table definition. Check the B4XTable set up definition: it should be: B4XTable1.COLUMN_TYPE_NUMBERS

 

[klaus]

You should give more information !
What column header are you speaking of ?
What is the data type of 'PDF Page ?
Above is what I was about to post!

Then, in the mean time I looked at all your posts to find which project I had posted and found it.
The Table is the Table class.
Your problem is, as Mahares suspected already, that, by default, the table class is filled with texts even if the column data in the database is integer.
The solution is to replace the line below in the project I posted:

tblItems.LoadSQLiteDB(Starter.sql,"SELECT DescriptionSubjectLookup, TableNumber as [Table], PageIRC As [IRC Page], PgPDF As [PDF Page] FROM TableList221",True)

by

tblItems.LoadSQLiteDB2(Starter.sql,"SELECT DescriptionSubjectLookup, TableNumber as [Table], PageIRC As [IRC Page], PgPDF As [PDF Page] FROM TableList221", True, Array As String("T", "T", "T", "I"))

Attached the project modified, not the one in your previous post.

For other helpers, the project you posted is of no use, because of lot of data missing!

 

[Shelby]

Thanks Klaus,
I'll study your information and try to make the changes. I deleted the irrelevant zip file.

 

[klaus]

In the project you will also see this line commented:

tblItems.LoadSQLiteDB(Starter.sql,"SELECT DescriptionSubjectLookup, TableNumber as [Table], PageIRC As [IRC Page], PgPDF As [PDF Page] FROM TableList221 ORDER BY PgPDF ASC",True)

This one fills the Table with the PDF Page already sorted correctly. But, if click on the header the sorting will be wrong.
Then, if you want to have the column already sorted you can use this line:

tblItems.LoadSQLiteDB2(Starter.sql,"SELECT DescriptionSubjectLookup, TableNumber as [Table], PageIRC As [IRC Page], PgPDF As [PDF Page] FROM TableList221 ORDER BY PgPDF ASC", True, Array As String("T", "T", "T", "I"))

 

[Shelby]

I do have a column with text and will hope that column will still sort alphabetically.

 

[Shelby]

OK,
I'll try to digest all this and understand. Thanks

 

[Shelby]

In the project you will also see this line commented:

tblItems.LoadSQLiteDB(Starter.sql,"SELECT DescriptionSubjectLookup, TableNumber as [Table], PageIRC As [IRC Page], PgPDF As [PDF Page] FROM TableList221 ORDER BY PgPDF ASC",True)

This one fills the Table with the PDF Page already sorted correctly. But, if click on the header the sorting will be wrong.
Then, if you want to have the column already sorted you can use this line:

tblItems.LoadSQLiteDB2(Starter.sql,"SELECT DescriptionSubjectLookup, TableNumber as [Table], PageIRC As [IRC Page], PgPDF As [PDF Page] FROM TableList221 ORDER BY PgPDF ASC", True, Array As String("T", "T", "T", "I"))

OK, yes the first two columns of 4 are text, the second two columns are integers.

 

[klaus]

The will be sorted correctly.
Before sending the modified project I looked at the database and know the data type of the columns.
I defined the call of LoadSQLiteDB2 at the and according to these.
At the end you see: Array As String("T", "T", "T", "I")
"T" means TEXT and "I" mean INTEGER.

 

[Shelby]

The will be sorted correctly.
Before sending the modified project I looked at the database and know the data type of the columns.
I defined the call of LoadSQLiteDB2 at the and according to these.
At the end you see: Array As String("T", "T", "T", "I")
"T" means TEXT and "I" mean INTEGER.

So it I guess I can modify that to: Array As String("T", "T", "I", "I") since my third column is composed of integers.

 

[klaus]

Sure !