Android Question Populating ListView from SQLite

Declan

Well-Known Member
Licensed User
Longtime User
I am populating a ListView from a SQLite table.
The ListView is a simple Diary application and has 24 items in the ListView.
(0 = Midnight ---- 23 = 11:00pm)
First I check if there are records in the table.
If there are NO records (Curs.RowCount = 0) I populate the ListView with text (MyDiaryTime) and a bitmap.
If there are records, I populate the ListView with the records from the table where eventdate = '"& MyDiaryDate &". The code should cycle through all the records and display the record at the correct position (MyEventTime = Curs.GetString("eventtime") which ==(i).
I am overlooking something, but can't find what I am doing incorrectly.
B4X:
Sub LoadHours(Date As Long)

DateTime.DateFormat = "dd/MM/yyyy"
    MyDiaryDate=DateTime.date(Date)
  
    lvHours.clear
        Dim Curs As Cursor
        Curs = SQL0.ExecQuery("SELECT * FROM diary WHERE eventdate = '"& MyDiaryDate &"'")' AND BookType= '"& MyBookType &"'")
            If Curs.RowCount = 0 Then
                For i=0 To 23
                    Dim MyAmPm As String
                    If i = 0 Then MyAmPm = "Midnight"
                    If i = 12 Then MyAmPm = "Noon"
                    If i >= 1 And i <= 11 Then MyAmPm = "am"
                    If i >= 13 And i <= 23 Then MyAmPm = "pm"
                    MyDiaryTime = i & ":00 " & MyAmPm
                    lvHours.TwoLinesAndBitmap.ItemHeight = 70dip
                    lvHours.TwoLinesAndBitmap.ImageView.Height = 65dip
                    lvHours.TwoLinesAndBitmap.ImageView.Width = 65dip
                    lvHours.TwoLinesAndBitmap.Label.TextColor=Colors.DarkGray
                    lvHours.TwoLinesAndBitmap.Label.TextSize = 15
                    lvHours.TwoLinesAndBitmap.Label.Left = (((lvHours.TwoLinesAndBitmap.ImageView.Left) + (lvHours.TwoLinesAndBitmap.ImageView.Width)) + 5dip)
                    lvHours.AddTwoLinesAndBitmap2(MyDiaryTime,  ("         " & "EventDetail") , LoadBitmap(File.DirAssets, i & ".png"),MyDiaryTime)
                Next
            Else
  
            For i=0 To Curs.RowCount-1
                Curs.Position=i
                Dim MyEventTime As Int
                Dim DiaryText As String
                MyEventTime = Curs.GetString("eventtime")
                DiaryText = Curs.GetString("eventdetail")
                For i=0 To 23
                    Dim MyDiaryTime As String
                    Dim MyAmPm As String
                    If i = 0 Then MyAmPm = "Midnight"
                    If i = 12 Then MyAmPm = "Noon"
                    If i >= 1 And i <= 11 Then MyAmPm = "am"
                    If i >= 13 And i <= 23 Then MyAmPm = "pm"
                    MyDiaryTime = i & ":00 " & MyAmPm
                    lvHours.TwoLinesAndBitmap.ItemHeight = 70dip
                    lvHours.TwoLinesAndBitmap.ImageView.Height = 65dip
                    lvHours.TwoLinesAndBitmap.ImageView.Width = 65dip
                    lvHours.TwoLinesAndBitmap.Label.TextColor=Colors.DarkGray
                    lvHours.TwoLinesAndBitmap.Label.TextSize = 15
                    lvHours.TwoLinesAndBitmap.Label.Left = (((lvHours.TwoLinesAndBitmap.ImageView.Left) + (lvHours.TwoLinesAndBitmap.ImageView.Width)) + 5dip)
                    If i = MyEventTime Then
                    lvHours.AddTwoLinesAndBitmap2(MyDiaryTime,  ("         " & DiaryText) , LoadBitmap(File.DirAssets, i & ".png"),MyDiaryTime)
                    Else
                    lvHours.AddTwoLinesAndBitmap2(MyDiaryTime,  ("") , LoadBitmap(File.DirAssets, i & ".png"),MyDiaryTime)
                    End If
                Next  
            Next
            Curs.Close  
        End If
End Sub
However, in the code below, I only get the first record from the table:
 

mangojack

Well-Known Member
Licensed User
Longtime User
Somewhere in this forum I criticized the (our) use of the variable "i" for loops: why it always?
F for Field can be better.
95% of snippets, example, tutorial I have read over the ages has used 'i' as loop counter, possibly stemming from some declaring i as Int/ Integer.
From this a habit was born, using i ( and j & k for nested loops).

I did try to break that habit years ago ... dimming and using 'count' , but it did not last long !

Loops can be doing more than just interrating through fields .. just do 'Something' so many times.

@Declan 's error would still have occurred had he used 'F' .
 
Upvote 0

Declan

Well-Known Member
Licensed User
Longtime User
Thanks, I have changed:
For i=0 To Curs.RowCount-1
To:
For count=0 To Curs.RowCount-1
The ListView is loaded with the records from the SQLite table using:
B4X:
                    If i = MyEventTime And DiaryText <> "" Then
                        lvHours.AddTwoLinesAndBitmap2(MyDiaryTime,  ("         " & DiaryText) , LoadBitmap(File.DirAssets, i & ".png"),MyDiaryTime)
                    End If
However, if there are 3 records for "eventdate" in the table "diary", I only get those 3 records.
I do not get the additional ListView items (those that do not contain records from the SQLite table):
B4X:
                    If i = MyEventTime And DiaryText = "" Then
                        lvHours.AddTwoLinesAndBitmap2(MyDiaryTime,  ("") , LoadBitmap(File.DirAssets, i & ".png"),MyDiaryTime)
                    End If
To further explain:
lvHours MUST always have 24 items (0 - 23).
If there are 3 records in the table, lvHours should have 24 items in total, with 3 items that have SQLite records on line 2 (" " & DiaryText) and the bitmap.
The remaining 21 items will not have any SQL records, but would have text on line 1 (MyDiaryTime and the bitmap.

These is something wrong with my Loops.
Complete Sub:
B4X:
Sub LoadHours(Date As Long)

DateTime.DateFormat = "dd/MM/yyyy"
    MyDiaryDate=DateTime.date(Date)
    lvHours.clear
        Dim Curs As Cursor
        Curs = SQL0.ExecQuery("SELECT * FROM diary WHERE eventdate = '"& MyDiaryDate &"' ORDER BY eventtime")
            If Curs.RowCount = 0 Then
                For i=0 To 23
                    Dim MyAmPm As String
                    If i = 0 Then MyAmPm = "Midnight"
                    If i = 12 Then MyAmPm = "Noon"
                    If i >= 1 And i <= 11 Then MyAmPm = "am"
                    If i >= 13 And i <= 23 Then MyAmPm = "pm"
                    MyDiaryTime = i & ":00 " & MyAmPm
                    lvHours.TwoLinesAndBitmap.ItemHeight = 70dip
                    lvHours.TwoLinesAndBitmap.ImageView.Height = 65dip
                    lvHours.TwoLinesAndBitmap.ImageView.Width = 65dip
                    lvHours.TwoLinesAndBitmap.Label.TextColor=Colors.DarkGray
                    lvHours.TwoLinesAndBitmap.Label.TextSize = 15
                    lvHours.TwoLinesAndBitmap.Label.Left = (((lvHours.TwoLinesAndBitmap.ImageView.Left) + (lvHours.TwoLinesAndBitmap.ImageView.Width)) + 5dip)
                    lvHours.AddTwoLinesAndBitmap2(MyDiaryTime,  ("         " & "EventDetail") , LoadBitmap(File.DirAssets, i & ".png"),MyDiaryTime)
                Next
            Else
   
            For count=0 To Curs.RowCount-1
                Curs.Position=count
                Dim MyEventTime As Int
                Dim DiaryText As String
                MyEventTime = Curs.GetString("eventtime")
                DiaryText = Curs.GetString("eventdetail")
                For i=0 To 23
                    Dim MyDiaryTime As String
                    Dim MyAmPm As String
                    If i = 0 Then MyAmPm = "Midnight"
                    If i = 12 Then MyAmPm = "Noon"
                    If i >= 1 And i <= 11 Then MyAmPm = "am"
                    If i >= 13 And i <= 23 Then MyAmPm = "pm"
                    MyDiaryTime = i & ":00 " & MyAmPm
                    lvHours.TwoLinesAndBitmap.ItemHeight = 70dip
                    lvHours.TwoLinesAndBitmap.ImageView.Height = 65dip
                    lvHours.TwoLinesAndBitmap.ImageView.Width = 65dip
                    lvHours.TwoLinesAndBitmap.Label.TextColor=Colors.DarkGray
                    lvHours.TwoLinesAndBitmap.Label.TextSize = 15
                    lvHours.TwoLinesAndBitmap.Label.Left = (((lvHours.TwoLinesAndBitmap.ImageView.Left) + (lvHours.TwoLinesAndBitmap.ImageView.Width)) + 5dip)
                    If i = MyEventTime And DiaryText <> "" Then
                        lvHours.AddTwoLinesAndBitmap2(MyDiaryTime,  ("         " & DiaryText) , LoadBitmap(File.DirAssets, i & ".png"),MyDiaryTime)
                    End If
                    If i = MyEventTime And DiaryText = "" Then
                        lvHours.AddTwoLinesAndBitmap2(MyDiaryTime,  ("") , LoadBitmap(File.DirAssets, i & ".png"),MyDiaryTime)
                    End If
                Next   
            Next
            Curs.Close   
        End If
End Sub
 
Upvote 0

LucaMs

Expert
Licensed User
Longtime User
95% of snippets, example, tutorial I have read over the ages has used 'i' as loop counter, possibly stemming from some declaring i as Int/ Integer.
From this a habit was born, using i ( and j & k for nested loops).

I did try to break that habit years ago ... dimming and using 'count' , but it did not last long !
Somewhere in this forum I criticized the (our) use of the variable "i" for loops: why it always?
"(our)" means: me too :) but, right for this reason, I will use, and suggest, to use different variable names.

@Declan 's error would still have occurred had he used 'F' .
I have not "studied" the first post, but if the right solution is:
You are using the same variable i multiple times in a nested loop.
use more specific names would help to avoid the error.

After all, why use something like:
B4X:
For Each Customer As clsCustomer in...
instead of:
B4X:
For Each i As clsCustomer in...
?
 
Upvote 0
Top