Sqlite data to JSON

General
1

What is the best and simplest way to create JSON from Sqlite data, I have 10 rows of data with 3 columns id, name, status. I have tried following code, but i get only last record as JSON:

[code]Dim wr As New JSONItem

While Not data.EOF
wr.Value(“id”) = data.Field(“id”).StringValue
wr.Value(“name”) = data.Field(“name”).StringValue
wr.Value(“status”) = data.Field(“status”).StringValue
data.MoveNext
Wend

wr.Compact = True
Dim s As String =wr.ToString
TextArea1.text = s[/code]

2

Using the new Xojo framework, something like this should work:

  Dim jsonArray() As Xojo.Core.Dictionary
  
  Dim dbRecord As Xojo.Core.Dictionary
  While Not data.EOF
    dbRecord = New Xojo.Core.Dictionary
    dbRecord.Value("id") = data.Field("id").StringValue
    dbRecord.Value("name") = data.Field("name").StringValue
    dbRecord.Value("status") = data.Field("status").StringValue
    data.MoveNext
    jsonArray.Append(dbRecord)
  Wend
  
  Dim json As Text = Xojo.Data.GenerateJSON(jsonArray)
3

Using the old framework this should help:

[code]
Dim jsonArray as new JSONItem
Dim js as JSONItem
While not data.EOF

      js = new JSONItem
      For i = 1  to data.FieldCount
        js.Value(data.IdxField(i).Name) = data.IdxField(i).Value
      Next
      
      jsonArray.Append js
      data.MoveNext
    Wend[/code]
4

Thank you Paul and Jrmie for quick reply. Booth solutions work.