jQuery datatable not populating

Question

I have a jQuery data table, getting data through web service API. I can see the data returned is in correct format but the table is not displaying the data.

I have a "Submit" button that on click fetches the data and redraws the table. I put a breakpoint in "success" function and can see the json data.

Javascript on top of the aspx page and data table definition (I needed this js functions because the Search text box on top the datatable kept disappearing on page reloads)

<script>
    $(function () {
        bindDataTable(); // bind data table on first page load
        // bind data table on every UpdatePanel refresh
        Sys.WebForms.PageRequestManager.getInstance().add_endRequest(bindDataTable);
    });

    function bindDataTable() {
        var FacCertDT = $('#tblFacCert').DataTable({
            'bDestroy': true,
            "bStateSave": true,
            dom: 'lfrtip',
            "fnStateSave": function (oSettings, oData) {
                localStorage.setItem('tblFacCert', JSON.stringify(oData));
            },
            "fnStateLoad": function (oSettings) {
                return JSON.parse(localStorage.getItem('tblFacCert'));
            }
        });
    }
</script>

<div runat="server" id="divFacCert" ClientIDMode="Static" style="padding:15px">
    <div class="table-responsive">
        <table id="tblFacCert" class="table table-striped table-bordered table-hover">
            <thead>
                <tr class="info">
                    <th>Area</th>
                    <th>District</th>
                    <th>MPOO </th>
                    <th>Facility</th>
                    <th>Type</th>
                    <th>Sub-Type</th>
                    <th>Response Due Date</th>
                    <th>Completed?</th>
                    <th>Completed By</th>
                    <th>Completed On</th>
                </tr>
            </thead>
        </table>
    </div>
</div>

Javascript at the bottom of the page:

<script>
    Table = $("#tblFacCert").DataTable({
        data: [],
        "aoColumns": [
            {
                "mData": "Area"
            }, {
                "mData": "District"
            }, {
                "mData": "MPOO"
            }, {
                "mData": "FacilityName"
            }, {
                "mData": "FacilityType"
            }, {
                "mData": "FacilitySubType"
            }, {
                "mData": "ResponseDueDate"
            }, {
                "mData": "Completed"
            }, {
                "mData": "UserName"
            }, {
                "mData": "ResponseDate"
            }
        ],
        "pageLength": 15,
        "deferRender": true,
        rowCallback: function (row, data) { },
        filter: false,
        info: false,
        ordering: false,
        processing: true,
        retrieve: true
    });

    $("#btnSubmit").on("click", function (event) {
        $.ajax({
            type: "POST",
            dataType: "json",
            contentType: "application/json; charset=utf-8",
            url: "../services/easg.asmx/GetComplianceReportData",
            cache: false,
            contentType: "application/json; charset=utf-8",
            data: "{ 'startDate': '" + $("#tbStartDate").val() + "', 'certID': '" + $('#ddlCertificate').val() + "'}"
        }).done(function (result) {debugger
            Table.clear().draw();
            Table.rows.add(result).draw();
            }).fail(function (jqXHR, textStatus, errorThrown) {
                alert(textStatus + ' - ' + errorThrown);
        });
    })
</script>

This is what the web service API looks like:

namespace FacCert
{
    /// <summary>
    /// Summary description for easg
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]

    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
    [System.Web.Script.Services.ScriptService]
    public class easg : System.Web.Services.WebService
    {

        [WebMethod]
        [ScriptMethod(UseHttpGet = false, ResponseFormat = ResponseFormat.Json)]

        public string GetComplianceReportData(string startDate, string certID)
        {
            DateTime dtStartDate = Convert.ToDateTime(startDate);
            int iCertItemID = int.Parse(certID);
            DataTable dt = FacCompliance.GetFacCompliances(dtStartDate, iCertItemID);
            string JSONresult;
            JSONresult = JsonConvert.SerializeObject(dt);
            return JSONresult;
        }
    }
}

This is the data when I examine it in "on("click")":

[
    {
        "Area":"CAPITAL METRO (K)",
        "District":"NORTHERN VIRGINIA",
        "MPOO":"Plant",
        "FacilityName":"DULLES VA P&DC",
        "FacilityType":"Network Operations",
        "FacilitySubType":"P&DC",
        "ResponseDueDate":"2017-12-20T00:00:00",
        "ResponseDate":"2017-12-30T17:57:35.353",
        "UserACEID":"XXXXXX",
        "UserName":"John Doe",
        "Completed":"Yes",
        "AreaID":11,
        "DistrictID":58,
        "FacID":2261,
        "FacComplianceID":1
    },{
        "Area":"CAPITAL METRO (K)",
        "District":"NORTHERN VIRGINIA",
        "MPOO":"Plant",
        "FacilityName":"NORTHERN VA P&DC",
        "FacilityType":"Network Operations",
        "FacilitySubType":"P&DC",
        "ResponseDueDate":"2017-12-20T00:00:00",
        "ResponseDate":"2017-12-30T18:23:10.86",
        "UserACEID":"XXXXXX",
        "UserName":"John Doe",
        "Completed":"Yes",
        "AreaID":11,
        "DistrictID":58,
        "FacID":2262,
        "FacComplianceID":4
    }
]

I have searched and tried all suggestions but nothing seems to fix the problem. I am hoping someone here can see where I am messing up.

There are a few columns returned by API that I am not including in the data table (e.g. AreaID, DistrictID, ...).

update:

if I replace the variable "result" in Table.rows.add(result).draw(); with actual json array that I have listed in the post, then it displays the data. I got the json data by putting a breakpoint and examining the value of "result", so i am not sure why it works when I use the actual json data and not when I pass it "result".

Answer 1

Replace this line in your Ajax done method:

Table.rows.add(JSON.parse(result.d)).draw();

Edit If you are using update panel you can bind your js method with that then your change reflect. put you your button submit code in functuon and set your method name as parameter. i hope it's work

<script type="text/javascript">
  Sys.WebForms.PageRequestManager.getInstance().add_endRequest(YourMethod);
    </script>

Answer 2

After much time spent on this I figured out what the issue was. When I passed the actual json data to Table.rows.add() data was displayed. So I knew there was something wrong with the "result" parameter I was passing. When I looked at the reponse that was returned in Chrome, I noticed it was adding XML tags to it:

<string xmlns="http://tempuri.org/">
[
    {
        "Area":"CAPITAL METRO (K)",
        "District":"NORTHERN VIRGINIA",
        ....
    }
]
</string>

That's why the raw data would display but not when I passed "result" parameter to it.

The fix was to change the button click function like this:

$("#btnSubmit").on("click", function (event) {
    $.ajax({
        type: "POST",
        dataType: "json",
        contentType: "application/json; charset=utf-8",
        url: "../services/easg.asmx/GetComplianceReportData",
        cache: false,
        data: "{ 'startDate': '" + $("#tbStartDate").val() + "', 'certID': '" + $('#ddlCertificate').val() + "'}"
    }).done(function (result) {
        Table.clear().draw();
        jResult = JSON.parse(result.d); <--- This is the fix
        Table.rows.add(jResult).draw();
    }).fail(function (jqXHR, textStatus, errorThrown) {
        alert(textStatus + ' - ' + errorThrown);
    });
})