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

 

[klaus]

I will have a look at it tomorrow afternoon, I have visits at home today til tomorrow.

 

[Colin Evans]

Thank you, appreciate your time and help

 

[Mahares]

too much space between each item,

The project you posted although a small sample and is not your official one has a few problems:
1. It gives you an error because this line:For i = 1 To 8 should be: For i = 1 To 5 because you have 5 images.
2. You are using xCustomeListView class 1.20 when you should use the latest which is 1.65 located:
https://www.b4x.com/android/forum/t...-list-based-on-xcustomlistview.86449/#content
3. To reduce the spacing between the items in your posted project you need to change this line:
Dim height As Int = 230dip with a smaller height like say: Dim height As Int = 90dip
4. If you take care of those 3 things, your project will run.
4. Since you are working with the powerhouse @klaus to help you, I will leave the rest to both of you to iron out.

 

[Colin Evans]

Sorry about the first error, I had to remove some images for the zip file to be small enough to upload, and yes the advice you've given me is excellent, just can't get my head around how I read through the database, similar to the spinner in DBUTils and populate the card list, I keep trying and getting nowhere

 

[klaus]

Do you have a small version of the database for testing?
We need to know the database structure to give a concrete advice.

 

[Colin Evans]

Trying to upload a small snapshot of the database but its states the file is too big (650KB)

Below is a screen capture of the database table, or if you can advise how to get it to you, I think its because of the images contained in the database, I'll try to make another with smaller images but for now here is the structure,

Basically I'd need to load into each cardlist item the image (blob) the Fish name, and the Location and Date, the rest would be display on clicking the item in the list

Very sorry to ask and bother you, I'm amazed at the B4A programming environment, being a late starter (61) into this its keeping me brain active

many thanks

 

[Colin Evans]

Hi Changed the image files to small icon images and the database came down in size, so hopefully will give you some idea, cheers, Colin

 

[klaus]

Here you are.

 

[udg]

Hi Klaus,
I know you live in the mountains, but I've to say that you have really strange fishes there..eheeh

 

[Colin Evans]

Hi Klaus, many many thanks, just what I was after and I can understand the code for future projects, thanks again

 

[Colin Evans]

Hi Klaus, just noticed you are Klaus Switzerland, which is very weird as my wife and I have just landed in Geneva for a few days visit from the UK, loving the wet snow!, thanks again

 

[Colin Evans]

Hi Klaus, Back again I'm afraid, could you please explain how I select one of the cards in the list so that I can get all the info from the database.

I amended your code to use my database and load the ID which is the unique identifier, and I've tried a couple of methods but can't get it to identify which card I've clicked so I can pick the ID number and then get the info from the database.

Private Sub FillCLV
    Private ResultSet1 As ResultSet
    Private Query As String
    Query = "SELECT ID, Fish, date, location, image FROM fishcaught"
    ResultSet1 = Starter.SQL1.ExecQuery(Query)
    Do While ResultSet1.NextRow
        Private Buffer() As Byte 'declare an empty byte array
        Buffer = ResultSet1.GetBlob2(4)
        Private InputStream1 As InputStream
        InputStream1.InitializeFromBytesArray(Buffer, 0, Buffer.Length)
        Private bmp As Bitmap
        bmp.Initialize2(InputStream1)
        InputStream1.Close
        CLV1.Add(CreateItem(CLV1.AsView.Width, ResultSet1.GetString2(0), ResultSet1.GetString2(1), ResultSet1.GetString2(2), ResultSet1.GetString2(3), bmp), "")
    Loop
End Sub

Private Sub CreateItem(Width As Int, ID As String, Fish As String, Date As String, Location As String, Image As Bitmap) As Panel
    Private p As B4XView = xui.CreatePanel("")
    Private Height As Int = 82dip
    p.SetLayoutAnimated(0, 0, 0, Width, Height)
    p.LoadLayout("Card1")
    lblID.Text = ID
    lblFish.Text = Fish
    lblDate.Text = Date
    lblLocation.Text = Location
    Image = Image.Resize(imvFish.Width, imvFish.Height, True)
    imvFish.SetBitmap(Image)
    Return p
End Sub

The above works with the addition of the ID number, but when I use the following it returns the last ID not the one shown in the selected item

Sub imvFish_click
    Activity.Title = lblID.Text
End Sub

and I've tried the fol;lowing but ddoesn't seem to do anything

Sub CLV1_click(Index As Int, Value As Object )
    If Value<> "" Then
        Activity.Title =  Value
    End If
End Sub

Apologies for coming across as a complete novice (which I am), really enjoyed our time in Geneva by the way

 

[Mahares]

and I've tried the fol;lowing but ddoesn't seem to do anything

You did not set a return value.
This line:

CLV1.Add(CreateItem(CLV1.AsView.Width, ResultSet1.GetString2(0), ResultSet1.GetString2(1), ResultSet1.GetString2(2), ResultSet1.GetString2(3), bmp), "")

should be like this:

CLV1.Add(CreateItem(CLV1.AsView.Width, ResultSet1.GetString2(0), ResultSet1.GetString2(1), ResultSet1.GetString2(2), ResultSet1.GetString2(3), bmp), ResultSet1.GetString2(0))

Your item click sub should be like this:

Sub CLV1_ItemClick(Index As Int, Value As Object )
    Activity.Title =  Value
    Dim MyID As String= Value     'MyID could have also been declared in Globals
    Log(MyID)  'you can then do what ever with the id here
End Sub

klaus will take over from here since you are addressing your posts to him. I just wanted to get you a jump start.

 

[Colin Evans]

Many many thanks just what I needed, really appreciate all the help the community offers

 

[klaus]

I am a bit estonished, in the project I posted in post#28 the return value was integrated, exactly the line Mahares suggested, why did you remove it?
In the same project, when you clicked on an item in the list, the fish name was diplayed in the Logs, why did you remove this?
I used the internal 'rowid' and you want to use the specific 'ID' column ?
If you replace 'rowid' by 'ID' in my project you get exactly the same result.

Where did you stay in Switzerland?
I don't know why, but I missed your post#31, I saw it only this morning.

 

[Colin Evans]

Hi Klaus, apologies if I inadvertently removed something, I wasn't aware I had, as I used your last example, I think I'm getting there very slowly, maybe one more question, now I've got the I'd, I obviously want to use it to display the full information held in that record, so can I transfer the value to another activity module and use it in my SQL to extract the data. We stayed in Geneva, just a four day city break, really enjoyed it, spent loads, ate loads, and walked miles and miles, would have been nice to buy you a pint if you were near by, thanks again for all you help

 

[klaus]

I obviously want to use it to display the full information held in that record, so can I transfer the value to another activity module and use it in my SQL to extract the data.

You must define a ProcessGlobal variable to hold the ID, then you can the use it in any Activity for SQL queries.
Attached a small project with an Edit Activity, I added onla Fish and Location, up to you to complete it.
You may probably need to use a ScrollView to hold all data.
You can add two buttons to add or update the database.

Did you have a look at the B4A User's Guide chapter SQLite Database, it includes example projects.

Geneva is about 2 hours driving from my home in Fully.

 

[Colin Evans]

Thank you, I'll try and read up before bothering you again, thanks again

 

[klaus]

'bothering' is not the case!
But reading guides is always interesting.

 

[Colin Evans]

Hi Klaus, I've been making good progress, I've added the module to add new records, get the GPS location and even a camera to take pictures of the fish caught, so I'm really getting to grips BUT one annoying thing as just cropped up.

When I add a new record if I don't enter an image from the camera I get an error when I run the program due to the image in the database record being Null, tried to find a way past this but getting nowhere, is there a way passed this when opening the program and entering the card list, cheers, Colin

Private Sub FillCLV
    Private ResultSet1 As ResultSet
    Private Query As String
    Query = "SELECT ID, Fish, date, location, image FROM fishcaught"
    ResultSet1 = Starter.SQL1.ExecQuery(Query)
    Do While ResultSet1.NextRow
        Private Buffer() As Byte 'declare an empty byte array
            Buffer = ResultSet1.GetBlob2(4)
            Private InputStream1 As InputStream
            InputStream1.InitializeFromBytesArray(Buffer, 0, Buffer.Length)
            Private bmp As Bitmap
            bmp.Initialize2(InputStream1)
            InputStream1.Close
        CLV1.Add(CreateItem(CLV1.AsView.Width, ResultSet1.GetString2(0), ResultSet1.GetString2(1), ResultSet1.GetString2(2), ResultSet1.GetString2(3), bmp), ResultSet1.GetString2(0))
    Loop
    ResultSet1.Close
End Sub

Private Sub CreateItem(Width As Int, ID As String, Fish As String, Date As String, Location As String, Image As Bitmap) As Panel
    Private p As B4XView = xui.CreatePanel("")
    Private Height As Int = 82dip
    p.SetLayoutAnimated(0, 0, 0, Width, Height)
    p.LoadLayout("Card1")
    lblID.Text = ID
    lblFish.Text = Fish
    lblDate.Text = Date
    lblLocation.Text = Location
    Image = Image.Resize(imvFish.Width, imvFish.Height, True)
    imvFish.SetBitmap(Image)
    Return p
End Sub