Android Question Populating ListView from SQLite

[Declan]

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.

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:

 

[fixit30]

You are using the same variable i multiple times in a nested loop.

You should use a different variable than i in the following line.

For i=0 To 23

 

[LucaMs]

You are using the same variable i multiple times in a nested loop.

You should use a different variable than i in the following line.

For i=0 To 23

Somewhere in this forum I criticized the (our) use of the variable "i" for loops: why it always?

F for Field can be better.

 

[mangojack]

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' .

 

[Declan]

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:

                    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):

                    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:

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

 

[LucaMs]

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:

For Each Customer As clsCustomer in...

instead of:

For Each i As clsCustomer in...

?