2 Layer of JSON data

General
1

I’m trying to make JSON strings.
Each person would have several kids, and finally I can use root.toString object.
With below code, I got JSONException error.
In case I use below code instead, I don’t see any data for Kids.
'person.Value(“Kids”) = kids

Can you find anything wrong in below code?

      Dim root as new JSONItem
      Dim finalString As String
      Dim colorArray() As String
      Dim colorArrayCount As Integer = 0
      colorArray = Array("#FF0F00","#0D8ECF")
      
      While Not rs.EOF
        
        Dim Person as new JSONItem

        Person.Value("KKK") =     rs.Field("KKK").StringValue
        Person.Value("Ball") =     Val(rs.Field("Ball").StringValue)
        Person.Value("color") =     colorArray(colorArrayCount)
        
        While ( rs.Field("KKK").StringValue = rsEachQ.Field("KKK").StringValue )
          
          Dim Kids as new JSONItem
          
          Kids.Value("date") = rsEachQ.Field("GATHER_TIME").StringValue
          Kids.Value("Ball") = rsEachQ.Field("Ball").StringValue
          
          Person.append Kids
          'person.Value("Kids") = kids
          
          rsEachQ.MoveNext
          
  
        Wend
        
        
        root.append Person
        
        rs.MoveNext
        
      Wend
      
      TextArea2.text = root.toString
2

You cannot mix calls to the Value with calls to Append. The former makes JSONItem work as a Dictionary while the latter makes it work as an Array. Person.Value(“Kids”) = kids is the right way to do it. But the structure seems wrong. It looks like each person can only have one Kids but you might be adding many. I think what you may want is closer to this:

      Dim root as new JSONItem
      Dim finalString As String
      Dim colorArray() As String
      Dim colorArrayCount As Integer = 0
      colorArray = Array("#FF0F00","#0D8ECF")
      
      While Not rs.EOF
        
        Dim Person as new JSONItem

        Person.Value("KKK") =     rs.Field("KKK").StringValue
        Person.Value("Ball") =     Val(rs.Field("Ball").StringValue)
        Person.Value("color") =     colorArray(colorArrayCount)
        
        dim KidsArray as new JSONItem( "[]" )
        While ( rs.Field("KKK").StringValue = rsEachQ.Field("KKK").StringValue )
          
          Dim Kids as new JSONItem
          
          Kids.Value("date") = rsEachQ.Field("GATHER_TIME").StringValue
          Kids.Value("Ball") = rsEachQ.Field("Ball").StringValue
          
          kidsArray.append Kids
          
          rsEachQ.MoveNext
          
        Wend
        
        Person.Value("Kids") = KidsArray
        root.append Person
        
        rs.MoveNext
        
      Wend
      
      TextArea2.text = root.toString

Also, you don’t need the colorArrayCount variable and, more, shouldn’t use such a thing. Use colorArray.Ubound instead.