Category Group Listbox

General
1

Hi, friends, someone can help me to group a related table, the first row is called category, variants and after a line break.

I want to add categories within the same column in the first row:

Regards.


  If Not IsConnected Then
    MsgBox("Connect to the database first.")
  End If
  
  Dim RS As RecordSet = mDB.SQLSelect("SELECT category.`name` AS cat, variants.`name` AS var  FROM variants JOIN category ON variants.id_category = category.id ")
  
  
  If mDB.Error Then
    
    MsgBox("DB Error: " + mDB.ErrorMessage)
    Return
  Else
  End If
  
  If RS <> Nil Then
    
    DataList.DeleteAllRows
    DataList.HasHeading = true
    DataList.ColumnCount = 2
    DataList.ColumnWidths = "50%, 50%"
    DataList.InitialValue =  "Category"  + chr(9) + "Variants"
    
    For i As Integer = 1 To RS.RecordCount
      
      Dim category as String = RS.Field("cat").StringValue
      Dim variants as String = RS.Field("var").StringValue
      
      DataList.AddRow
      
      DataList.Cell(DataList.LastIndex, 0) = category
      
      DataList.Cell(DataList.LastIndex, 1) = variants
      
      
      RS.MoveNext
    Next
    
    
    RS.Close
  End If

Actual result:

2

Have you considered a Hierarchical listbox and getting the variants in the Expanded event? You should also add an ORDER BY clause to your query to get a sorted list.

3

It is right, but as I can implement it with the code in MySQL, greetings.

4

You can download an example from https://dl.dropboxusercontent.com/u/18858366/HierarchicalList.xojo_binary_project.

Looks like this

5

You’re great, man!

6

Similar situation here, but with a twist.

I have a database with those 2 text fields, city and country. I can add sections to my Table using countries as headers, but I am stucked with the cities.

Do you know a way to add each city in the appropriate section ?

7

Do you have a single table with multiple countries, cities & addresses? You can use SELECT DISTINCT to get just the countries & cities and you could add both as folders to the Listbox using the same technique as in my example for Victor. You just have to determine whether the folder is a country or city and for that I would use CellTag(, 0). You’d also put the name of the country/city in the rowtag rather than the primary key field.

Give me an example of your data & I’ll put together a simple project for you to look at.

8

Thank you for your answer. Here what I got so far:

  1. A global iosSqlitedatabase property named ‘db’

  2. An app open event:

[code] db = New iOSSQLiteDatabase

If db.Connect Then
db.SQLExecute(“create table Villes (id integer primary key, nom Text, pays Text)”)

Dim sql,ville,pays As Text


sql = "insert into villes (nom, pays) values (?1, ?2)"

Try
  ville = "Paris"
  pays = "France"
  db.SQLExecute(sql, ville, pays)
  
Catch e As iOSSQLiteException
  Dim err As Text = e.Reason
End Try

Try
  ville = "Rome"
  pays = "Italie"
  db.SQLExecute(sql, ville, pays)
  
Catch e As iOSSQLiteException
  Dim err As Text = e.Reason
End Try

Try
  ville = "Pise"
  pays = "Italie"
  db.SQLExecute(sql, ville, pays)
  
Catch e As iOSSQLiteException
  Dim err As Text = e.Reason
End Try

Try
  ville = "Toulouse"
  pays = "France"
  db.SQLExecute(sql, ville, pays)
  
Catch e As iOSSQLiteException
  Dim err As Text = e.Reason
End Try

Try
  ville = "Paris"
  pays = "USA"
  db.SQLExecute(sql, ville, pays)
  
Catch e As iOSSQLiteException
  Dim err As Text = e.Reason
End Try

Try
  ville = "New York"
  pays = "USA"
  db.SQLExecute(sql, ville, pays)
  
Catch e As iOSSQLiteException
  Dim err As Text = e.Reason
End Try

End
[/code]

  1. A Table open event:

[code] Dim sql As Text
Dim rs As iOSSQLiteRecordSet

sql = “select distinct pays from Villes order by pays”

rs = db.SQLSelect(sql)

If rs <> Nil Then
While Not rs.eof
Me.AddSection(rs.Field(“pays”).TextValue)
rs.MoveNext
Wend
End

[/code]

With that, I get the sections correctly. Now, I ‘just’ need how to add rows…

9

Unfortunately I develop on Windows, so can’t help with iOS Tables. You should ask your question in the Targets->iOS channel.

10

No problem. Your tips helped me to find a solution after a good night sleep.

The new table open event:

[code] Dim sql,section As Text
Dim rs As iOSSQLiteRecordSet

sql = “select distinct pays from Villes order by pays”

rs = db.SQLSelect(sql)

If rs <> Nil Then
While Not rs.eof
Me.AddSection(rs.Field(“pays”).TextValue)
rs.MoveNext
Wend
End

for j as integer = 0 to me.SectionCount - 1
section = Table1.SectionTitle(j)
sql = “select id, nom from villes where pays = '” + section + “’ order by nom”
rs = db.SQLSelect(sql)
if rs <> nil then
while not rs.eof
dim cell As iOSTableCellData = me.CreateCell
cell.Text = rs.Field(“nom”).TextValue
cell.Tag = rs.Field(“id”).IntegerValue
me.AddRow(j,cell)
rs.MoveNext
wend
end
next
[/code]