Json format issue

classic12

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

kthorngren

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

classic12

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'}
]
})

kthorngren

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

classic12

Hi kthorngren,

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

Cheers

Steve Warby