Datatables Invalid JSON Response Error

Innocentm · June 2019

I am trying to display JSON data from an API using Jquery, I have tried everything but I keep on getting Invalid JSON Error

Here is my JSON Response

"TotalResults": 3,
  "ReturnedResults": 3,
  "Results": [
    {
      "Description": "LED Panel Light 1500x300 56W 4000K 110lm/W 3 year warranty",
      "Category": {
        "Description": "Panel Lights",
        "ID": 16721,
        "Modified": "2019-05-20T16:46:31.69",
        "Created": "2019-05-20T16:46:31.69"
      },
      "Code": "A334",
      "Active": true,
      "PriceInclusive": 448.5,
      "PriceExclusive": 390,
      "Physical": true,
      "TaxTypeIdSales": 111509,
      "TaxTypeIdPurchases": 111509,
      "LastCost": 230,
      "AverageCost": 230,
      "QuantityOnHand": 2970,
      "HasActivity": true,
      "Unit": "",
      "TextUserField1": "",
      "TextUserField2": "",
      "TextUserField3": "",
      "YesNoUserField1": false,
      "YesNoUserField2": false,
      "YesNoUserField3": false,
      "Modified": "2019-06-04T10:01:06.097",
      "Created": "2019-05-29T17:21:50.797",
      "ItemReportingGroupPurchasesName": "Purchases",
      "ItemReportingGroupSalesName": "Sales",
      "ID": 37598
    },
    {
      "Description": "LED Panel Light 600x600 38W 4300K 110lm/W 5 year warranty White",
      "Category": {
        "Description": "Panel Lights",
        "ID": 16721,
        "Modified": "2019-05-20T16:46:31.69",
        "Created": "2019-05-20T16:46:31.69"
      },
      "Code": "A334.1",
      "Active": true,
      "PriceInclusive": 575,
      "PriceExclusive": 500,
      "Physical": true,
      "TaxTypeIdSales": 111509,
      "TaxTypeIdPurchases": 111509,
      "LastCost": 300,
      "AverageCost": 300,
      "QuantityOnHand": 300,
      "HasActivity": false,
      "Unit": "",
      "TextUserField1": "",
      "TextUserField2": "",
      "TextUserField3": "",
      "YesNoUserField1": false,
      "YesNoUserField2": false,
      "YesNoUserField3": false,
      "Modified": "2019-05-20T16:46:57.48",
      "Created": "2019-05-20T16:46:57.443",
      "ItemReportingGroupPurchasesName": "Purchases",
      "ItemReportingGroupSalesName": "Sales",
      "ID": 37425
    },
    {
      "Description": "LED Panel Light 1500x300 56W 4000K 110lm/W 5 year warranty\t",
      "Category": {
        "Description": "Panel Lights",
        "ID": 16721,
        "Modified": "2019-05-20T16:46:31.69",
        "Created": "2019-05-20T16:46:31.69"
      },
      "Code": "A3343.2",
      "Active": true,
      "PriceInclusive": 667,
      "PriceExclusive": 580,
      "Physical": true,
      "TaxTypeIdSales": 111509,
      "TaxTypeIdPurchases": 111509,
      "LastCost": 320,
      "AverageCost": 320,
      "QuantityOnHand": 2000,
      "HasActivity": false,
      "Unit": "",
      "TextUserField1": "",
      "TextUserField2": "",
      "TextUserField3": "",
      "YesNoUserField1": false,
      "YesNoUserField2": false,
      "YesNoUserField3": false,
      "Modified": "2019-05-20T16:47:44.443",
      "Created": "2019-05-20T16:47:44.433",
      "ItemReportingGroupPurchasesName": "Purchases",
      "ItemReportingGroupSalesName": "Sales",
      "ID": 37426
    }
  ]
}

Below is my Datatables setup in the View:

<div class="box-body table-responsive">
          <table id="products-table" class="table table-bordered table-hover dataTable">
            <thead>
              <tr >
                <th >Product Code</th>
                <th >Description</th>
                <th>PriceExclusive</th>
                <th>Quantity</th>
                <th >Updated Date</th>
              </tr>
            </thead>
          </table>
  </div>
  <!-- /.box-body -->

</div>

</section>

    <script>
      $(document).ready(function(){
        $('#products-table').DataTable({
        ajax:
          '{{url('admin/sageproducts')}}'',
        columns: [

            {data: "Code"},
            {data: "Description"},
            {data: "PriceExclusive"},
            {data: "QuantityOnHand"},
            {data: "Modified"}
            ],
                dom: 'Brtip',
                buttons: [
                    {
                        extend: 'print',
                          title: 'Sage Items',
                        autoPrint: true,
                        exportOptions: {
                    columns: [ 0, 1, 2,3,4]
                }
                    },
                    {
                    extend: 'excel',
                    title: 'Sage Items',
                        exportOptions: {
                    columns: [ 0, 1, 2,3,4]
                }
                    }
                ]
    });

    });
    </script>

Any assistance will be much appreciated!

colin · June 2019

Hi @Innocentm ,

By default, DataTables expects the row information to be within a data object of the Ajax response. Since yours is in Results, you need to set ajax.dataSrc to reflect that.

Cheers,

Colin

Innocentm · June 2019

Hi Colin,

Thank you for your prompt response, I have changed the dataSrc however am still getting the same error: is there anything am doing wrong perhaps?

$(document).ready(function(){
        $('#products-table').DataTable({
        ajax:{
          url:"{{url('admin/sageproducts')}}",
          dataSrc:"Results"
        },
        columns: [

            {data: "Code"},
            {data: "Description"},
            {data: "PriceExclusive"},
            {data: "QuantityOnHand"},
            {data: "Modified"}
            ],
                dom: 'Brtip',
                buttons: [
                    {
                        extend: 'print',
                          title: 'Sage Items',
                        autoPrint: true,
                        exportOptions: {
                    columns: [ 0, 1, 2,3,4]
                }
                    },
                    {
                    extend: 'excel',
                    title: 'Sage Items',
                        exportOptions: {
                    columns: [ 0, 1, 2,3,4]
                }
                    }
                ]
    });

    });

kthorngren · June 2019

Did you follow the instructions provided in the link of the Warning: Invalid JSON response error?
https://datatables.net/manual/tech-notes/1

Let us know what you find.

Kevin

colin · June 2019

Hi @Innocentm ,

I took the data and created an example, it's working here, so the table config is good. One thing I did notice, which may just be a cut&paste error, is that your data didn't have a leading '{' at the start.

If that's not the problem, could you link to your page please, as it does look like it should be behaving,

Cheers,

Colin

Innocentm · June 2019

@kthorngren

Followed all instructions in the link, I can see the response is valid JSON as posted earlier on but the error still persists.

Am not sure what am doing wrong

Innocentm · June 2019

@colin

I saw the leading '{' it was just a copy/paste error.

Unfortunately my page is on a local/development server and I won't be able to link to it.

A friend of mine was suggesting I convert my JSON data to associate array, I did but still the same error.

If I convert my data into an associate array that looks like below: is there anything I need to change in my view to display the array data with datatables?

Thank you

kthorngren · June 2019

As Colin mentioned, assuming a copy / paste error, the above JSON data works if a leading { is added. He asked for a link to your page so we can take a look. If that is not possible then try gathering debug info for Allan or Colin to look at.
https://datatables.net/manual/tech-notes/10#DataTables-debugger

Maybe they can determine the issue from that.

Kevin

colin · June 2019

Hi @Innocentm ,

Another good option, along with Kevin's debugger suggestion, is to look at the network tab of the developer's tool in the browser, and see what the actual response is to the Ajax request. It's possible perhaps that the server is truncating it, but viewing that response tab will guarantee we're seeing the same as DataTables is receiving.

Cheers,

Colin

Innocentm · July 2019

I managed to solve the problem using the Yajra Datatables package, my returned data looks like below:

{
  "draw": 0,
  "recordsTotal": 3,
  "recordsFiltered": 3,
  "data": [
    [
      3
    ],
    [
      3
    ],
    [
      {
        "Description": "LED Panel Light 1500x300 56W 4000K 110lm/W 3 year warranty",
        "Category": {
          "Description": "Panel Lights",
          "ID": "16721",
          "Modified": "2019-05-20T16:46:31.69",
          "Created": "2019-05-20T16:46:31.69"
        },
        "Code": "A334",
        "Active": "1",
        "PriceInclusive": "448.5",
        "PriceExclusive": "390",
        "Physical": "1",
        "TaxTypeIdSales": "111509",
        "TaxTypeIdPurchases": "111509",
        "LastCost": "230",
        "AverageCost": "230",
        "QuantityOnHand": "2970",
        "HasActivity": "1",
        "Unit": "",
        "TextUserField1": "",
        "TextUserField2": "",
        "TextUserField3": "",
        "YesNoUserField1": false,
        "YesNoUserField2": false,
        "YesNoUserField3": false,
        "Modified": "2019-06-04T10:01:06.097",
        "Created": "2019-05-29T17:21:50.797",
        "ItemReportingGroupPurchasesName": "Purchases",
        "ItemReportingGroupSalesName": "Sales",
        "ID": "37598"
      },
      {
        "Description": "LED Panel Light 600x600 38W 4300K 110lm/W 5 year warranty White",
        "Category": {
          "Description": "Panel Lights",
          "ID": "16721",
          "Modified": "2019-05-20T16:46:31.69",
          "Created": "2019-05-20T16:46:31.69"
        },
        "Code": "A334.1",
        "Active": "1",
        "PriceInclusive": "575",
        "PriceExclusive": "500",
        "Physical": "1",
        "TaxTypeIdSales": "111509",
        "TaxTypeIdPurchases": "111509",
        "LastCost": "300",
        "AverageCost": "300",
        "QuantityOnHand": "300",
        "HasActivity": false,
        "Unit": "",
        "TextUserField1": "",
        "TextUserField2": "",
        "TextUserField3": "",
        "YesNoUserField1": false,
        "YesNoUserField2": false,
        "YesNoUserField3": false,
        "Modified": "2019-05-20T16:46:57.48",
        "Created": "2019-05-20T16:46:57.443",
        "ItemReportingGroupPurchasesName": "Purchases",
        "ItemReportingGroupSalesName": "Sales",
        "ID": "37425"
      },
      {
        "Description": "LED Panel Light 1500x300 56W 4000K 110lm/W 5 year warranty",
        "Category": {
          "Description": "Panel Lights",
          "ID": "16721",
          "Modified": "2019-05-20T16:46:31.69",
          "Created": "2019-05-20T16:46:31.69"
        },
        "Code": "A3343.2",
        "Active": "1",
        "PriceInclusive": "667",
        "PriceExclusive": "580",
        "Physical": "1",
        "TaxTypeIdSales": "111509",
        "TaxTypeIdPurchases": "111509",
        "LastCost": "320",
        "AverageCost": "320",
        "QuantityOnHand": "2000",
        "HasActivity": false,
        "Unit": "",
        "TextUserField1": "",
        "TextUserField2": "",
        "TextUserField3": "",
        "YesNoUserField1": false,
        "YesNoUserField2": false,
        "YesNoUserField3": false,
        "Modified": "2019-06-11T09:02:04.267",
        "Created": "2019-05-20T16:47:44.433",
        "ItemReportingGroupPurchasesName": "Purchases",
        "ItemReportingGroupSalesName": "Sales",
        "ID": "37426"
      }
    ]
  ],
  "input": [
    
  ]
}

My view is setup as below:

$(document).ready(function(){
        $('#products-table').DataTable({
        ajax:{
          url:'{{url('admin/sageproducts')}}',
          dataSrc:"data",
        },
        columns: [
            {data: 'Code', name: 'Code'},
            {data: 'Description', name: 'Description'},
            {data: 'PriceExclusive', name: 'PriceExclusive'},
            {data: 'QuantityOnHand', name: 'QuantityOnHand'},
            {data: 'Modified', name: 'Modified'}
            ],
                dom: 'Brtip',
                buttons: [
                    {
                        extend: 'print',
                        title: 'Sage Items',
                        autoPrint: true,
                        exportOptions: {
                    columns: [ 0, 1, 2,3,4]
                }
                    },
                    {
                    extend: 'excel',
                    title: 'Sage Items',
                        exportOptions: {
                    columns: [ 0, 1, 2,3,4]
                }
                    }
                ]
    });

    });

However now am getting the "Requested unkown parameter" on each key am thinking maybe my

dataSrc:"data",

is incorrect. Any help will be much appreciated, my apologies for taking such a long time to come back on the same issue

Innocentm · July 2019

@colin Your first answer was right, I noticed I was missing a comma after dataSrc:"Results" now It's working with my original JSON data. Thanks a million

Datatables Invalid JSON Response Error

Datatables Invalid JSON Response Error

Answers

Howdy, Stranger!

Categories

DataTables

Datatables Invalid JSON Response Error

Datatables Invalid JSON Response Error

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables