Android Question Displaying an SQL database in a list

[Colin Evans]

Hi, just wondered if it was possible and if anyone could give me a nudge in the right direction.

I have a database, table that contains a years worth of data, i.e. 365 rows each row having four fields,
Date, Time, Info, Name
I want to load the full list of data onto the screen into a scrolling list but also highlight today's date in the list nad then bea ble to scroll up or down the list, but then have a button to return me back to today's date entry in the list

Firstly is this possible and if so any idea's where I should start

 

[OliverA]

https://www.b4x.com/android/forum/threads/dbutils-android-databases-are-now-simple.8475/

Look at the ExecuteListView. The demo should show an usage example.

 

[Colin Evans]

Hi, Sorry but tried this and not getting anywhere fast, I may have been a little vague on what I want to achieve, basically I have a table in my database that contains four or five row entries for each date, each row having five columns i.e.

a snap shot from the database

I want to get the first entry of a selected date entry, get the information held in the columns, display it on screen and then have a next and previous button so I can move through the database, si this possible

 

[Colin Evans]

Another screen shot with column titles

 

[Erel]

Check Flexitable class.

 

[Colin Evans]

Hi Erel, brilliant, so I now have a list containing all the data from the table with the four columns I require, date, time, height and status, is it now possible to interrogate the list using say a datepicker so that it loads the list and then highlights the row using the date picked and shows the first date chosen in the list

 

[Erel]

I don't remember the exact details but I'm pretty sure that you can handle the cell click event and show whatever you like to show.

 

[klaus]

Directly no.
You need to go through the data in the Table.
All data are available with Table.Data
This is a List of String Arrays.

You can also get a cell content with Table.GetValue(col, row)

You can select a row with:

Private rc As RowCol
rc.Initialize(row, col)
Table.SelectRow(rc)

 

[Colin Evans]

Trying to understand the logic, if I've got the table how to I navigate to a specific date held in the table

 

[klaus]

Private Date = "07/12/2019" As String
Private col = 0 As Int
For row= 0 To Table1.Data.Size - 1
    If Table1.GetValue(col, row) = Date Then
        Table1.JumpToRowAndSelect(row, col)
        Exit
    End If
Next

 

[Colin Evans]

Hi Klaus, many thanks, you're a star
I got a syntax error with .Data.Size but changed it to just .Size and it worked perfectly
Thanks again

 

[Colin Evans]

Hi, ANother problem!, why can't I use the table and anotherdatepicker together, If I create an activity with just the datepicker it works okay but as soon as I add the table the datepicker (custom view) doesn't show on the screen nor can I show it

The code below just shows the text field from the layout plus the table

Sub Activity_Create(FirstTime As Boolean)
    Activity.LoadLayout("mainscr")
    Table1.Initialize(Me, "Table1", 4)
    Table1.AddToActivity(Activity, 0, 100dip, 100%x, 50%y)
    Table1.SetHeader(Array As String("Col0", "Col1", "Col2", "Col3"))
    For i = 0 To 5000
        Table1.AddRow(Array As String("Row: " & i, "ccc", "ddd", "eee"))
    Next
    Table1.SetColumnsWidths(Array As Int(100dip, 30dip, 130dip, 100%x - 260dip))
    AnotherDatePicker1.Show
End Sub

Whereas the following doesn't show the table but does show the datepicker field and shows it

Sub Activity_Create(FirstTime As Boolean)
    Activity.LoadLayout("mainscr")
    'Table1.Initialize(Me, "Table1", 4)
    'Table1.AddToActivity(Activity, 0, 100dip, 100%x, 50%y)
    'Table1.SetHeader(Array As String("Col0", "Col1", "Col2", "Col3"))
    'For i = 0 To 5000
     '   Table1.AddRow(Array As String("Row: " & i, "ccc", "ddd", "eee"))
    'Next
    'Table1.SetColumnsWidths(Array As Int(100dip, 30dip, 130dip, 100%x - 260dip))
    AnotherDatePicker1.Show
End Sub

There's obviously a conflict but unsure where to start

 

[klaus]

Can you post a small project showing the problem.
You can also add the Table in the Designer instead of adding it in the code.

 

[Colin Evans]

Hi Klaus, I will but how do you add a table in the designer,

 

[Colin Evans]

Just knocked this together but shows the problem, many thanks

 

[klaus]

Well, the behaviour is normal!
You call AnotherDatePicker1.Show to display the DatePicker.
Comment this line, and the DatePicker will not be shown.
Then, when you click on the DatePickerEditText the DatePicker is displayed.
This is implemeted in the attached project AnotherDatePicker1.zip.
When you select a row in the Table, the edit field is updated with the date of the selected row.
If you touch the DatePickerEditText, the DatePicker is shown and when you select a date it is updated in the Table.

You are using a very old version of the Table class, the latest version is 2.26!
In the project AnotherDatePicker2.zip I replaced the old Table class by the latest one and added it in the Designer.
It needs the libraries below:
SQL
JavaObject
Reflection
ScrollView2D

I modified in the ManifestEditor this line
<uses-sdk android:minSdkVersion="4" android:targetSdkVersion="14"/>
into this one:
<uses-sdk android:minSdkVersion="5" android:targetSdkVersion="26"/>

 

[Colin Evans]

Hi Klaus, many thanks really appreciate you taking the time to show me where I'm going wrong and How it should be done, thanks again

 

[Colin Evans]

Hi Klaus, when using the table class is it possible to load an image contained in the database (Blob) into one of the fields to achieve a list similar to the one below seen in another program

where you can then click on the row and load another layout containing the full information,

 

[klaus]

No, in the Table class all cells are Labels.
To achieve what you want I suggest you to use xCustomListView, it is now an internal library.

 

[Colin Evans]

Hi Klaus, two more questions and plea's for help, I've had a dabble with customlistView, editing an example on the forum, but I can't seem to get the items to follow one another correctly, too much space between each item, I've looked at the code and can't find out how to list each item to follow on from the next.

Once I've got that sorted the next big problem would be to read from an sql database and create the list, any pointers

Many thanks greatly appreciate all the advice and guidance