Putting the JSON data into a grid(HTML table) using datatables

phavanagi

I have a web service, which gives a response in JSON data. It is written in .net web API using the MVC. I would like to use DataTables to put the JSON data into a grid(tabular form). I am not able to figure out how! Can you please help?

Thank you!

allan

Can you show us the JSON data that is being returned by the server please?

Allan

phavanagi

{
"PageNumber": 1,
"PageSize": 50,
"TotalNumberOfPages": 2665,
"TotalNumberOfRecords": 133216,
"PreviousPageUrl": null,
"NextPageUrl": "http://localhost:50001/api/queuedprice/2/50",
"Results": [
{
"Id": 1,
"Security": "IBM 8.375 19",
"SecurityId": 553,
"EffectiveDate": "2017-03-03T00:00:00",
"Price": 116.8309
}
}

This is the JSON data!

bindrid

Do you have dataSrc:"Results" option set as described https://datatables.net/reference/option/ajax.dataSrc

bindrid

Also note that if you are using .net web method, your JSON is most likely returned in an object called "d" so

{d:{
 "PageNumber": 1,
 "PageSize": 50,
 "TotalNumberOfPages": 2665,
 "TotalNumberOfRecords": 133216,
 "PreviousPageUrl": null,
 "NextPageUrl": "http://localhost:50001/api/queuedprice/2/50",
 "Results": [
 {
 "Id": 1,
 "Security": "IBM 8.375 19",
 "SecurityId": 553,
 "EffectiveDate": "2017-03-03T00:00:00",
 "Price": 116.8309
 }
 }
}

phavanagi

Thanks for your quick reply!

Isn't it the url that we specify in the ajax.url field enough? I mean the JSON data that the URL returns will be displayed as columns as specified in the column field. (Correct me if I am wrong regarding my understanding of the ajax.url). Here is the code that I am using for the datatable:

$(document).ready(function () {
$('#example').DataTable({
"processing": true,
ajax: {

            url: "http://localhost:50001/api/queuedprice/1/50",
            dataSrc: ' '

        },

        columns: [
        { "data": "PageNumber" },
        { "data": "PageSize" },
        { "data": "TotalNumberOfPages" },
        { "data": "TotalNumberOfRecords" },
        { "data": "PreviousPageUrl" },
        { "data": "NextPageUrl" },
        { "data": "Results.Id" },
        { "data": "Results.Security" },
        { "data": "Results.SecurityId" },
        { "data": "Results.EffectiveDate" },
        { "data": "Results.Price" }
        ]

    });

    });

I suppose the "url : http://localhost:50001/api/queuedprice/1/50" (this returns a JSON data) should be mapped to the columns?

Also, I am getting an error when I debug in chrome as
"Index:78 Uncaught TypeError: $(...).DataTable is not a function"

I have included these two cdns:

Anything more is required for the error? Please let me know.

Thank you!

tangerine

Isn't it the url that we specify in the ajax.url field enough?

No. DataTables needs to know where in your JSON the result set begins.
Re-read @Bindrid's post about dataSrc.

phavanagi

@tangerine : Isn't dataSrc just to convert into JSON format? Since my Data is already in JSON format, Shouldn't it be left blank?

Also, what is the ajax.url used for?

kthorngren

Datatables expects the row data to be returned in an object called data. You are returning it in an object called Results. That's ok because you can use the ajax.dataSrc to tell Datatables where to find the data. Take a look at the first example of the ajax.dataSrc doc.

Kevin

allan

Both Kevin's and @tangerine's answers are correct. The ajax.dataSrc option tells DataTables where to find the array of row information in the JSON data set. Consider a more complex data set which might include other data, not just the rows array - that's is why ajax.dataSrc exists.

Can you use the debugger so we can see your JSON data exactly as it is please. Or even better, link to a test case showing the issue.

Allan