Android Question Loop through Database Table

[Declan]

I have the following code that populates a ListView.

Dim MyZone As String
Dim MySetPoint As String
Dim MyRecords As Int

    Dim m As Map
    m = DBUtils.ExecuteMap(SQL, "SELECT Id, Zone, SetPoint FROM Zones WHERE id = ?", _
        Array As String(Value))
      

  
        lblViewSelectedTool.Text = Value
        lblViewSelectedTool.Visible = True

        MyZone = m.Get("zone")'
        MySetPoint = m.Get("setpoint")
        'Msgbox(MyZone & " " & MySetPoint,"Gotcha")
  
    'DBUtils.ExecuteListView(SQL, "SELECT zone, setpoint FROM Zones WHERE id = ?", Array As String(Value), 0, lstViewZones,True)
  
    lstViewZones.AddTwoLines(MyZone, "Set Point: " & MySetPoint)

However, I am only getting the first record in the table.
How can I count the number of records in the table and loop through the records to populate the ListView with all records?
I cannot use DBUtils.ExecuteListView (as commented out) as I must insert the text "Set Point" as a prefix to MySetPoint.

 

[klaus]

The problem is that you add only one item!

In this case you could use directly a SQL query instead of DBUtils:

Dim MyZone As String
Dim MySetPoint As String

Dim Curs As Cursor
Curs = SQL.ExecQuery2("SELECT Id, Zone, SetPoint FROM Zones WHERE id = ?", _ArrayAsString(Value)

lblViewSelectedTool.Text = Value
lblViewSelectedTool.Visible = True

lstViewZones.Clear
Dim i as Int
For i = 0 to Curs.RowCount - 1
    Curs.Position = i
    MyZone = Curs.GetString("Zone")
    MySetPoint = Curs.GetStringt("SetPoint")
    lstViewZones.AddTwoLines(MyZone, "Set Point: " & MySetPoint)
Next
Curs.Close

 

[Declan]

@klaus
Many thanks - sorted

 

[Declan]

A problem cropped up:
It seems the above code is populating the ListView with data from other id fields in the zones table?

Either that, or the below code is writing to the Database Table incorrectly. I am attempting to write to a unique id field.

ub FillNewToolsZonesTable
    Dim NumZones As Int
    NumZones = txtNewToolZones.text
    Dim Table As List
    Table = DBUtils.ExecuteMemoryTable(SQL, "SELECT Id FROM Tools", Null, 0)
    'Table is a list of arrays. Each array holds a single item.
    Dim Cols() As String
    Dim ListOfMaps As List
    ListOfMaps.Initialize
    For zone = 1 To NumZones
        For tool = 0 To Table.Size - 1
            Dim m As Map
            m.Initialize
            Cols = Table.Get(tool)
            m.Put("Id", Cols(0))
            m.Put("zone", "ZONE " & zone)
            m.Put("setpoint", 0)
            ListOfMaps.Add(m)
        Next
    Next
    DBUtils.InsertMaps(SQL, "Zones", ListOfMaps)
End Sub

My limited knowledge tells me the problem could be with:
For tool = 0 To Table.Size - 1
................
Next

Where in the above code can I write to a specific Id
Something like "WHERE id = "MySpecificID"

 

[klaus]

Sorry, but I don't understand what exactly you want to do!

 

[Declan]

Sorry, but I don't understand what exactly you want to do!

Sorry, I have not made myself clear.
I am looking into using SQL instead of DBUtils to write the data to the tables

 

[klaus]

Do you want to add, insert, data or update data ?
You should have a look at chapter 5 SQLite Database in the B4A User's Guide.

 

[Declan]

Many thanks - I have this sorted with:

Dim Query As String

Dim SetPoint As Int
SetPoint = 0
Dim NumZones As Int
    NumZones = txtNewToolZones.text
Dim MyToolName As String
MyToolName=txtNewToolName.text
   
    For zone = 1 To NumZones
        Query = "INSERT INTO Zones VALUES (NULL, ?, ?, ?)"
        SQL.ExecNonQuery2(Query, Array As String(MyToolName, zone, SetPoint))
    Next
ToastMessageShow("New Tool: " & MyToolName & " Created", False)