How to generate multiple JSON data

General
1

Hi,

I’m building JSON data to pass it to Chart JavaScript. With one item it works well.

        Dim Person as new JSONItem
        Person.Value("x") = 10
        Person.Value("y") = 14
        Person.Value("value") = "PRINCE01"
        
        Dim finalString As String
        finalString = "[" + Person.Tostring() + "]"
        
        MsgBox " Person.Tostring " + Person.Tostring()
        htmlChart.ExecuteJavaScript("showChart(" + finalString + ");")

I need to get lots of database rows and put it into JSON items.
How can I make several items of JSON data pair like {x:10, y:14, value:PRINCE01},{x:30, y:54, value:OWNER} ?
I thought of array structure but I’m not sure how I can employ it in this code.

2

First of all, I suggest not using manual assembly by just concatenation strings. If you want to create an array, you’ll need to create a level of structure above the items you want to group.

[code]dim root as new JSONItem

Dim Person as new JSONItem
Person.Value(“x”) = 10
Person.Value(“y”) = 14
Person.Value(“value”) = “PRINCE01”

Root.append Person

Dim Person2 as new JSONItem
Person2.Value(“x”) = 10
Person2.Value(“y”) = 14
Person2.Value(“value”) = “PRINCE01”

Root.append Person2

Finalstring = root.toString
[/code]

3

If I just append the Person1/Person2/Person3, I think I can see the final string.
However, the rows of the database are not fixed so I can’t define the number of Person before.

I agree with your thought as for manual assembly.
I’m looking for the way I can generate the JSON string more automatically.

Thank you.

4

Just loop through the database records and append one Person each time through the loop. It doesn’t matter how many there are.

5

In the loop, is there any way to change the variable name like Person1, Person2 and so on?
It looks very silly question but I don’t know…

6

Oh…Array of the Variable…
Dim Person() as new JSONItem

I will try that.

Thank you.

7

No, the master JSON item (root in Greg’s example) IS an array. You just append to it as you go.

8

It should be like below?

Dim root as new JSONItem

while Not EOF

For i = 0 To rs.listcount .........

Person.Value("x") =     rs.IdxField(1).StringValue
Person.Value("y") =     rs.IdxField(2).StringValue
Person.Value("value") = rs.IdxField(3).StringValue

Root.append Person

Wend

Dim finalString As String
finalString = "[" + root.toString + "]"
9

It works very well. Thank you.

      Dim Person as new JSONItem
      Dim root as new JSONItem
      Dim finalString As String
      
      While Not rs.EOF
        
        
        Person.Value("x") =     rs.IdxField(1).StringValue
        Person.Value("y") =     rs.IdxField(2).StringValue
        Person.Value("value") = rs.IdxField(3).StringValue
        
        root.append Person
        
        rs.MoveNext
        
      Wend
      
      finalString = root.toString
10

In case you didn’t know, you can also refer to those fields by name…

x = rs.field("field name").stringvalue
11

Oh. Thank you.

I found one problem in the code example above.
I thought the Person would be replaced with the new value, but it doesn’t seem to be.
Dim Person as new JSONItem

This code should be inside of While loop. Otherwise, the final result comprises all of the same values.

12

Yes. They’re objects, so when you change one, you end up changing them all.