DataTables
Advanced interaction
features for your tables.
Editor
Comprehensive editing
library for DataTables.
Manual
Download
≡ Show site navigation

Datatables Invalid JSON Response Error

Datatables Invalid JSON Response Error

InnocentmInnocentm Posts: 11Questions: 3Answers: 0
edited June 2019 in Free community support

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!

This question has an accepted answers - jump to answer

Answers

  • colincolin Posts: 15,240Questions: 1Answers: 2,599
    edited 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

  • InnocentmInnocentm Posts: 11Questions: 3Answers: 0

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

    });
  • kthorngrenkthorngren Posts: 21,470Questions: 26Answers: 4,977

    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

  • colincolin Posts: 15,240Questions: 1Answers: 2,599

    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

  • InnocentmInnocentm Posts: 11Questions: 3Answers: 0

    @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

  • InnocentmInnocentm Posts: 11Questions: 3Answers: 0

    @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

    Array
(
    [TotalResults] => 3
    [ReturnedResults] => 3
    [Results] => Array
        (
            [0] => Array
                (
                    [Description] => LED Panel Light 1500x300 56W 4000K 110lm/W 3 year warranty
                    [Category] => Array
                        (
                            [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] => 
                    [YesNoUserField2] => 
                    [YesNoUserField3] => 
                    [Modified] => 2019-06-04T10:01:06.097
                    [Created] => 2019-05-29T17:21:50.797
                    [ItemReportingGroupPurchasesName] => Purchases
                    [ItemReportingGroupSalesName] => Sales
                    [ID] => 37598
                )

            [1] => Array
                (
                    [Description] => LED Panel Light 600x600 38W 4300K 110lm/W 5 year warranty White
                    [Category] => Array
                        (
                            [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] => 
                    [Unit] => 
                    [TextUserField1] => 
                    [TextUserField2] => 
                    [TextUserField3] => 
                    [YesNoUserField1] => 
                    [YesNoUserField2] => 
                    [YesNoUserField3] => 
                    [Modified] => 2019-05-20T16:46:57.48
                    [Created] => 2019-05-20T16:46:57.443
                    [ItemReportingGroupPurchasesName] => Purchases
                    [ItemReportingGroupSalesName] => Sales
                    [ID] => 37425
                )

            [2] => Array
                (
                    [Description] => LED Panel Light 1500x300 56W 4000K 110lm/W 5 year warranty 
                    [Category] => Array
                        (
                            [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] => 
                    [Unit] => 
                    [TextUserField1] => 
                    [TextUserField2] => 
                    [TextUserField3] => 
                    [YesNoUserField1] => 
                    [YesNoUserField2] => 
                    [YesNoUserField3] => 
                    [Modified] => 2019-05-20T16:47:44.443
                    [Created] => 2019-05-20T16:47:44.433
                    [ItemReportingGroupPurchasesName] => Purchases
                    [ItemReportingGroupSalesName] => Sales
                    [ID] => 37426
                )

        )

)
  • kthorngrenkthorngren Posts: 21,470Questions: 26Answers: 4,977

    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

  • colincolin Posts: 15,240Questions: 1Answers: 2,599
    Answer ✓

    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

  • InnocentmInnocentm Posts: 11Questions: 3Answers: 0

    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

  • InnocentmInnocentm Posts: 11Questions: 3Answers: 0

    @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

This discussion has been closed.

DataTables

DataTables designed and created by SpryMedia Ltd.
© 2007-2023 MIT licensed. Privacy policy. Supporters.
SpryMedia Ltd is registered in Scotland, company no. SC456502.