Json format issue

Json format issue

classic12classic12 Posts: 228Questions: 60Answers: 4

Hi Guys,

I am extracting some data from an SLQITE database as follows:

var newJson =[];

    btnFind.onclick=function()
        {
  sqlList=[];
  sqlList[0]=["SELECT * FROM studentData2 ", nameFound];
  Sql(DB, sqlList);
        }

I extract the data

function nameFound(transaction, results) {
  if(results.rows.length>0) {
     newJson=[];
     for (var k = 0; k < results.rows.length; k++) {

                    var row = results.rows.item(k);
if(k > 0) newJson += ',';
                    newJson += '{ "name":"' + row.name + '", "age":"' + row.age + '", "Field2":"' + row.Field2 + '", "Field3":"' + row.Field3 + '", "Field4":"' + row.Field4 + '", "Field5":"' + row.Field5 + '"}';
                 

                }

                jsonall = newJson + ']}';
                newJson = '['+newJson+']';
                alert(newJson); //shows me a correct JSON as string
               

   // NSB.MsgBox(results.rows.item(0).name  +  " is "  +  results.rows.item(0).age);
 }

I get the result.

[{ "name":"Ste", "age":"59", "Field2":"undefined", "Field3":"undefined", "Field4":"undefined", "Field5":"undefined"},{ "name":"Ste2", "age":"60", "Field2":"undefined", "Field3":"undefined", "Field4":"undefined", "Field5":"undefined"}]

I test this at https://jsonformatter.org/ and it shows as vaild Json.

If I use the following it works. ie copy the json data into
'''
var newJson = [{ "name":"Ste", "age":"59", "Field2":"undefined", "Field3":"undefined", "Field4":"undefined", "Field5":"undefined"},{ "name":"Ste2", "age":"60", "Field2":"undefined", "Field3":"undefined", "Field4":"undefined", "Field5":"undefined"}];

$("#dtContacts").dataTable().fnDestroy();
$('#dtContacts').empty();
tableContacts = $('#dtContacts').DataTable( {
'data':newJson,
'columns': [
{ 'data' : 'name' , title : 'Name'},
{ 'data': 'age' , title : 'Age'}
]
})

But if I try to use newJson directly I get errors 

DataTables warning: table id=dtContacts - Requested unknown parameter 'name' for row 0, column 0. For more information about this error, please see http://datatables.net/tn/4
```

Is there something I am not understanding about the format of newJson

Cheers

Steve Warby

This question has an accepted answers - jump to answer

Answers

  • kthorngrenkthorngren Posts: 20,141Questions: 26Answers: 4,736

    But if I try to use newJson directly I get errors

    Does this mean you are using the ajax option? If so then, by default, Datatables looks for the row data in the data object as described here:
    https://datatables.net/manual/ajax

    You would use the ajax.dataSrc option to change the default. The second example is what you want.

    Kevin

  • classic12classic12 Posts: 228Questions: 60Answers: 4

    No. Instead of

    `var newJson = [{ "name":"Ste", "age":"59", "Field2":"undefined", "Field3":"undefined", "Field4":"undefined", "Field5":"undefined"},{ "name":"Ste2", "age":"60", "Field2":"undefined", "Field3":"undefined", "Field4":"undefined", "Field5":"undefined"}];
    

    I dont add the above and use the newJson created earlier.

    so my code is just

    $("#dtContacts").dataTable().fnDestroy();
    $('#dtContacts').empty();
    tableContacts = $('#dtContacts').DataTable( {
    'data':newJson,
    'columns': [
    { 'data' : 'name' , title : 'Name'},
    { 'data': 'age' , title : 'Age'}
    ]
    })
    
  • kthorngrenkthorngren Posts: 20,141Questions: 26Answers: 4,736
    Answer ✓

    Oh, I see. Looks like your function nameFound is creating a string (newJson) and using that string to add the data to Datatables using 'data':newJson,. That won't work. Datatables expects a Javascript array when using data.

    What is the original data source of results? And what is its data structure? Trying to understand if you need to loop through it because it may be a structure the Datatables can just use.

    If you do need to loop through it then you will want newJson to be an array of objects. instead of this:

    newJson += '{ "name":"' + row.name + '", "age":"' + row.age + '", "Field2":"' + row.Field2 + '", "Field3":"' + row.Field3 + '", "Field4":"' + row.Field4 + '", "Field5":"' + row.Field5 + '"}';
    

    You will want something like this to build an array of row objects:

    newJson.push({ "name": row.name, "age": row.age , "Field2": row.Field2 , "Field3": row.Field3 , "Field4": row.Field4 , "Field5": row.Field5 });
    

    Kevin

  • classic12classic12 Posts: 228Questions: 60Answers: 4
    edited January 2019

    Hi kthorngren,

    thanks for the info I now have it working with your code above.

    Cheers

    Steve Warby

This discussion has been closed.