Android Question SQLite Won't Sort

Shelby

Well-Known Member
Licensed User
A couple of weeks ago, Klaus set me up with a new SQLite Project. I had a working B4A SQLite project with perfect column sorting which he converted to SQLite but with B4X pages now involved. From the very start, I was unable to get the columns to sort when the headers are clicked. It seems as though plenty of code is included with the new project which appears to enable the table to sort properly, but something is blocking the sort function. I have tried for weeks checking lines of code with my previous project, adding and removing lines of code but nothing has enabled the function. I'll include the zipped project here; it functions properly except for the column sorting. Hopefully, a knowledgeable member can spot the problem.
Thanks
 

Attachments

  • SS2021B4X Test.zip
    165.2 KB · Views: 134
Last edited:

LucaMs

Expert
Licensed User
Longtime User
There is something wrong with that xTableLight class.
In its Header_Click event routine:
Header_Click:
Private Sub Header_Click
    Dim L As B4XView   'SSS changed "B4XView" to "Label"  then back
    Dim col As Int
    L = Sender
    col = L.Tag
    'SSS===================Start
    If SubExists(cCallBack, cEventName & "_HeaderClick") Then ' <===== HERE
    CallSub2(cCallBack, cEventName & "_HeaderClick", col)
    If cSortColumn = False Then   'SSS changed True to False ' <===== HERE
    End If
    'SSS===================Stop
        If (sortedCol = col) Then ' we are sorting the same col, reverse dir

'etc.

If the module (the B4XPage) that contains this CV does not have the [EventName]_HeaderClick event routine, the sort is not executed.

Also there are two probably "spurious" lines:
B4X:
    If cSortColumn = False Then   'SSS changed True to False

    End If

If you add to the B4XMainPage:
B4X:
Private Sub xtblItems_HeaderClick(col As Int)
 
End Sub
the sort is performed.
There is, however, a further error: a second tap/click on the same column header causes an error.


Another error:
1643332977491.png



In short: some corrections to be made in the xTableLight class.
 
Last edited:
Upvote 0

LucaMs

Expert
Licensed User
Longtime User
I thought (badly) that the class was developed by Klaus, so I didn't try to correct the errors I pointed out.

I haven't done much testing but I think that routine should be like this:
Header_Click:
Private Sub Header_Click
    Dim L As B4XView   'SSS changed "B4XView" to "Label"  then back
    Dim col As Int
    L = Sender
    col = L.Tag

    If cSortColumn = False Then   'SSS changed True to False
        sortedCol = col
        sortingDir = IIf(sortingDir = "ASC", "DESC", "ASC")
        If cColumnDataType(col) = "TEXT" Then
            sortTable(col, sortingDir)  'SSS added <=0 within parenthesis line 1752
        Else
            sortTableNum(col, sortingDir)
        End If
        showHeaderSorting(col, sortingDir)
    End If
    
    If xui.SubExists(cCallBack, cEventName & "_HeaderClick", col) Then
        CallSub2(cCallBack, cEventName & "_HeaderClick", col)
    End If
End Sub
 
Upvote 0

Shelby

Well-Known Member
Licensed User
Thanks again. I'll go through your ideas in the morning. Unwinding now; Almost bedtime.
 
Upvote 0

Shelby

Well-Known Member
Licensed User
Luca,
You are hereby another hero of mine here on the forum. The sorting immediately worked when I commented out my Header_Click sub and replaced it with yours.
Thanks again
 
Upvote 0

Shelby

Well-Known Member
Licensed User
There is something wrong with that xTableLight class.
In its Header_Click event routine:
Header_Click:
Private Sub Header_Click
    Dim L As B4XView   'SSS changed "B4XView" to "Label"  then back
    Dim col As Int
    L = Sender
    col = L.Tag
    'SSS===================Start
    If SubExists(cCallBack, cEventName & "_HeaderClick") Then ' <===== HERE
    CallSub2(cCallBack, cEventName & "_HeaderClick", col)
    If cSortColumn = False Then   'SSS changed True to False ' <===== HERE
    End If
    'SSS===================Stop
        If (sortedCol = col) Then ' we are sorting the same col, reverse dir

'etc.

If the module (the B4XPage) that contains this CV does not have the [EventName]_HeaderClick event routine, the sort is not executed.

Also there are two probably "spurious" lines:
B4X:
    If cSortColumn = False Then   'SSS changed True to False

    End If

If you add to the B4XMainPage:
B4X:
Private Sub xtblItems_HeaderClick(col As Int)
 
End Sub
the sort is performed.
There is, however, a further error: a second tap/click on the same column header causes an error.


Another error:
View attachment 124816


In short: some corrections to be made in the xTableLight cl
There is something wrong with that xTableLight class.
In its Header_Click event routine:
Header_Click:
Private Sub Header_Click
    Dim L As B4XView   'SSS changed "B4XView" to "Label"  then back
    Dim col As Int
    L = Sender
    col = L.Tag
    'SSS===================Start
    If SubExists(cCallBack, cEventName & "_HeaderClick") Then ' <===== HERE
    CallSub2(cCallBack, cEventName & "_HeaderClick", col)
    If cSortColumn = False Then   'SSS changed True to False ' <===== HERE
    End If
    'SSS===================Stop
        If (sortedCol = col) Then ' we are sorting the same col, reverse dir

'etc.

If the module (the B4XPage) that contains this CV does not have the [EventName]_HeaderClick event routine, the sort is not executed.

Also there are two probably "spurious" lines:
B4X:
    If cSortColumn = False Then   'SSS changed True to False

    End If

If you add to the B4XMainPage:
B4X:
Private Sub xtblItems_HeaderClick(col As Int)
 
End Sub
the sort is performed.
There is, however, a further error: a second tap/click on the same column header causes an error.


Another error:
View attachment 124816


In short: some corrections to be made in the xTableLight class.
There is something wrong with that xTableLight class.
In its Header_Click event routine:
Header_Click:
Private Sub Header_Click
    Dim L As B4XView   'SSS changed "B4XView" to "Label"  then back
    Dim col As Int
    L = Sender
    col = L.Tag
    'SSS===================Start
    If SubExists(cCallBack, cEventName & "_HeaderClick") Then ' <===== HERE
    CallSub2(cCallBack, cEventName & "_HeaderClick", col)
    If cSortColumn = False Then   'SSS changed True to False ' <===== HERE
    End If
    'SSS===================Stop
        If (sortedCol = col) Then ' we are sorting the same col, reverse dir

'etc.

If the module (the B4XPage) that contains this CV does not have the [EventName]_HeaderClick event routine, the sort is not executed.

Also there are two probably "spurious" lines:
B4X:
    If cSortColumn = False Then   'SSS changed True to False

    End If

If you add to the B4XMainPage:
B4X:
Private Sub xtblItems_HeaderClick(col As Int)
 
End Sub
the sort is performed.
There is, however, a further error: a second tap/click on the same column header causes an error.


Another error:
View attachment 124816


In short: some corrections to be made in the xTableLight class.
One note here: When I added the above sub into my B4XMainPage that you suggested in post #2:

"Private Sub xtblItems_HeaderClick(col As Int)
End Sub"

The text in my first header column disappeared so I took it back out.
Shelb
 
Last edited:
Upvote 0

klaus

Expert
Licensed User
Longtime User
Attached your project modified.
In the mean time I had improved the xTableLight class, I renamed it to xTableLite.
The only modification I made in your project is replacing the xTableLight class by the updated xTableLite class and replaced the object in the layout.
 

Attachments

  • SS2021B4XTestNew.zip
    165 KB · Views: 134
Upvote 0

Mahares

Expert
Licensed User
Longtime User
I had improved the xTableLight class, I renamed it to xTableLite.
You can use this to popluate the table: xtblItems.LoadSQLiteDB2(.. and its other forms. You can also use a saved text file, like with using StringUtils and parser. But, what if you have a list that is still not saved as a file. What is the command to populate the table.
 
Upvote 0

klaus

Expert
Licensed User
Longtime User
What is the command to populate the table.
You can add rows with xtblItems.AddRow(Values() As String)
Something like this:
B4X:
xtblItems.InitializeTable(3, "CENTER", "CENTER")
xtblItems.SetHeader(Array As String("Col 1", "Col 2", "Col 3"))
For i = 0 To 400
    xtblItems.AddRow(Array As String("Test 1 " & (i + 1), "Test 2 " & (i + 1), "Test 3 " & (i + 1)))
Next

xTableLite is a, not yet published, light weight cross-platform version of the B4A Table class.
 
Upvote 0

Shelby

Well-Known Member
Licensed User
You can add rows with xtblItems.AddRow(Values() As String)
Something like this:
B4X:
xtblItems.InitializeTable(3, "CENTER", "CENTER")
xtblItems.SetHeader(Array As String("Col 1", "Col 2", "Col 3"))
For i = 0 To 400
    xtblItems.AddRow(Array As String("Test 1 " & (i + 1), "Test 2 " & (i + 1), "Test 3 " & (i + 1)))
Next

xTableLite is a, not yet published, light weight cross-platform version of the B4A Table class.
Additional Problem:
I have 5 to 7 instances of this project in the making. Only one of those instances shows updating when I use the DB Browser. I love using the DB Browser to add, edit, or remove rows because the DB Browser shows a nearly identical instance of the project's table. Is there a simple way to make sure the DB Browser will function to update properly with any project or variant of a project? I realize that you, Klaus, and Mahares, had a short discussion above about how one can add lines, with your posts #'s 8 and 9 above.
Thanks
 
Upvote 0

klaus

Expert
Licensed User
Longtime User
Sorry, but I do not understand what you are speaking of !?
I have 5 to 7 instances of this project in the making.
What do you mean with this ?
Do you have several projects with almost the same code or what else?
I love using the DB Browser to add, edit, or remove rows because the DB Browser shows a nearly identical instance of the project's table.
Again, sorry, but what is DB Browser ?
I do not see any reference to it in your code ?
 
Upvote 0

Shelby

Well-Known Member
Licensed User
Sorry, I didn't know you wouldn't be familiar with it. There have been a few threads with it here.
DB Browser for SQLite (DB4S) is a high quality, visual, open source tool to create, design, and edit database files compatible with SQLite.

Do you have several projects with almost the same code or what else?
Yes I have several instances of almost the same code as I attempt to edit but not lose the original project.
 
Upvote 0

Shelby

Well-Known Member
Licensed User
Sorry, I didn't know you wouldn't be familiar with it. There have been a few threads with it here. Unfortunately it did not update the database when I added a new row/field after I made the screenshot. Darn it.
Here's a screenshot of the project you sent to me this morning in the DB Browser
 

Attachments

  • Example for Klaus 1 29 22.png
    Example for Klaus 1 29 22.png
    136 KB · Views: 125
Upvote 0

klaus

Expert
Licensed User
Longtime User
And what is the problem ?
The project I posted is yours from post #1, I only replaced xTableLight by the new version xTableLite, I did not change anything else
If your are using any older version you need to replace the xTableLight class by the xTableLite class in these projects and also in the Designer
So, where or what is the problem ?
I did not change anything in the database.
xTableLite displays any kind of data in a table nothing else.
There are two internal methods allowing to load data from SQLite databases or csv files or you can add rows with your data with the AddRow method.
There is no interaction with the database nor the csv file or any other data.
 
Upvote 0

Shelby

Well-Known Member
Licensed User
And what is the problem ?
The project I posted is yours from post #1, I only replaced xTableLight by the new version xTableLite, I did not change anything else
If your are using any older version you need to replace the xTableLight class by the xTableLite class in these projects and also in the Designer
So, where or what is the problem ?
I did not change anything in the database.
xTableLite displays any kind of data in a table nothing else.
There are two internal methods allowing to load data from SQLite databases or csv files or you can add rows with your data with the AddRow method.
There is no interaction with the database nor the csv file or any other data.
No, I don't have a problem with what you gave me. It works perfectly. However, I can't get many of the images to display. I have little or no idea what is wrong with my image repository but I guess I'll be able to use your and Mahare's suggestions with the above code in post #9, namely:

You can add rows with xtblItems.AddRow(Values() As String)
Something like this:
B4X:

xtblItems.InitializeTable(3, "CENTER", "CENTER")
xtblItems.SetHeader(Array As String("Col 1", "Col 2", "Col 3"))
For i = 0 To 400
xtblItems.AddRow(Array As String("Test 1 " & (i + 1), "Test 2 " & (i + 1), "Test 3 " & (i + 1)))
Next

I'm going to try that now (using your suggestion I mean). But I don't know how to populate the field's text with the above code. Oh, at the "Test 1", "Test 2"..... apparently; so never mind.
 
Upvote 0

klaus

Expert
Licensed User
Longtime User
However, I can't get many of the images to display. I have little or no idea what is wrong with my image repository ...
Sorry again what do you want to do !?
xTableLite does not show any image.
What image, where are they defined, where are they located, how is the display generated etc. etc. !!???
Please, please, to be efficient, explain in detail what you want to do, otherwise we are playing question/answer 'ping-pong'.
 
Upvote 0

Shelby

Well-Known Member
Licensed User
Sorry again what do you want to do !?
xTableLite does not show any image.
What image, where are they defined, where are they located, how is the display generated etc. etc. !!???
Please, please, to be efficient, explain in detail what you want to do, otherwise we are playing question/answer 'ping-pong'.
Every row represents an image. Maybe you remember using the following code in my starter module with this same project but B4A only from 2 months ago:

If File.Exists(ImagesDir, "R702.4.2.png") = False Then
File.Copy(File.DirAssets, "R702.4.2.png", ImagesDir, "R702.4.2.png") 'ignore

I tried your If file entry as a template with hundreds of images for a test and I'm not sure why but I struggled with getting all images to display. I also changed back and forth using IF True or if False to get them to display. I was also forced to put a large number of images into the files tab of the project and so on.

Here's how I attempted that with the B4A projects:
All in starter module.

If File.Exists(ImagesDir, "R702.4.2.png") = True Then
File.Copy(File.DirAssets, "R702.4.2.png", ImagesDir, "R702.4.2.png")
End If

If File.Exists(ImagesDir, "R403.3(2).png") = True Then
File.Copy(File.DirAssets, "R403.3(2).png", ImagesDir, "R403.3(2).png")
End If

If File.Exists(ImagesDir, "E3404.4.png") = True Then
File.Copy(File.DirAssets, "E3404.4.png", ImagesDir, "E3404.4.png")
End If

If File.Exists(ImagesDir, "R404.1.2(1).png") = True Then
File.Copy(File.DirAssets, "R404.1.2(1).png", ImagesDir, "R404.1.2(1).png")
End If

If File.Exists(ImagesDir, "R404.1.2(2).png") = True Then
File.Copy(File.DirAssets, "R404.1.2(2).png", ImagesDir, "R404.1.2(2).png")
End If

If File.Exists(ImagesDir, "502.3.3(1).png") = True Then
File.Copy(File.DirAssets, "502.3.3(1).png", ImagesDir, "502.3.3(1).png")
End If

If File.Exists(ImagesDir, "R602.10.1.3.png") = True Then
File.Copy(File.DirAssets, "R602.10.1.3.png", ImagesDir, "R602.10.1.3.png")
End If

If File.Exists(ImagesDir, "R602.10.3(1).png") = True Then
File.Copy(File.DirAssets, "R602.10.3(1).png", ImagesDir, "R602.10.3(1).png")
End If

If File.Exists(ImagesDir, "R602.10.3(2).png") = True Then
File.Copy(File.DirAssets, "R602.10.3(2).png", ImagesDir, "R602.10.3(2).png")
End If

If File.Exists(ImagesDir, "R602.10.3(3).png") = True Then
File.Copy(File.DirAssets, "R602.10.3(3).png", ImagesDir, "R602.10.3(3).png")
End If

If File.Exists(ImagesDir, "R602.10.3(3) cont.png") = True Then
File.Copy(File.DirAssets, "R602.10.3(3) cont.png", ImagesDir, "R602.10.3(3) cont.png")
End If

If File.Exists(ImagesDir, "R602.10.3(4).png") = True Then
File.Copy(File.DirAssets, "R602.10.3(4).png", ImagesDir, "R602.10.3(4).png")
End If

'If File.Exists(ImagesDir, ".png") = True Then
' File.Copy(File.DirAssets, ".png", ImagesDir, ".png")
' End If
'If File.Exists(ImagesDir, ".png") = True Then
' File.Copy(File.DirAssets, ".png", ImagesDir, ".png")
' End If


If File.Exists(ImagesDir, "R602.10.5.png") = True Then
File.Copy(File.DirAssets, "R602.10.5.png", ImagesDir, "R602.10.5.png")
End If

If File.Exists(ImagesDir, "R602.10.5.2.png") = True Then
File.Copy(File.DirAssets, "R602.10.5.2.png", ImagesDir, "R602.10.5.2.png")
End If

If File.Exists(ImagesDir, "R602.10.6.1.png") = True Then
File.Copy(File.DirAssets, "R602.10.6.1.png", ImagesDir, "R602.10.6.1.png")
End If

If File.Exists(ImagesDir, "R602.10.6.4.png") = True Then
File.Copy(File.DirAssets, "R602.10.6.4.png", ImagesDir, "R602.10.6.4.png")
End If

If File.Exists(ImagesDir, "R602.10.6.5.png") = True Then
File.Copy(File.DirAssets, "R602.10.6.5.png", ImagesDir, "R602.10.6.5.png")
End If

If File.Exists(ImagesDir, "R602.10.4.png") = True Then
File.Copy(File.DirAssets, "R602.10.4.png", ImagesDir, "R602.10.4.png")
End If
 
Last edited:
Upvote 0
Top