Unable to bind jQuery DataTable in AJAX call with JSON data

Question

I'm trying to bind jQuery DataTable by calling mvc action method using AJAX call and trying to populate the table using JSON data but it gets stuck on loading. Am I missing something?

Also, how can I implement server side Paging, Searching and Sorting in jQuery DataTable.

function initDataTable(ClientId)
    {
        var assetListVM;
        $(function () {
            assetListVM = {
                dt: null,

                init: function () {
                    var ClientId = $('#ddlClient').val();
                    dt = $('#records-data-table').dataTable({
                        "serverSide": true,
                        "processing": true,
                        "ajax": {
                            "type": "POST",
                            "dataType": "json",
                            "url": "@Url.Action("GetProcessingData", "Processing")?clientId=" + ClientId,
                            "success": function (data) {
                                alert(data);
                                console.log(data);

                            },
                            "error": function(){
                                alert("Error");
                            }

                    },
                        "columns": [
                            { "title": "Co Num",
                            "data": "CompanyNo",
                                "searchable": true },
                            { "title": "Cycle Date",
                            "data": "CycleDate",
                                "searchable": true },
                            { "title": "Policy Number",
                            "data": "PolicyNo",
                                "searchable": true },
                            { "title": "Issue Date",
                            "data": "IssueDate",
                                "searchable": true },
                            { "title": "Transaction Date",
                            "data": "TransactionDate"
                            },
                            { "title": "Transaction Amt",
                            "data": "TransactionAmount"
                            },
                            { "title": "Item Code",
                            "data": "ItemCode"
                            },
                                { "title": "Account",
                                "data": "Account"
                                },
                                { "title": "Owner Name",
                                "data": "OwnerName"
                                }

                        ],
                        "lengthMenu": [[10, 25, 50, 100], [10, 25, 50, 100]],
                        });
        }
        }

    // initialize the datatables
    assetListVM.init();
    });
    }

Below is my action method:

public ActionResult GetProcessingData(int clientId)
    {
        ClientSummary ClientSUM = new ClientSummary();
        List<PolicyDownload> LstPData = new List<PolicyDownload>();
        DataTable dtProcessingData = new DataTable();
        try
        {
            using (DLpolicyDownload objDLpolicyDownload = new DLpolicyDownload())
            {
                dtProcessingData = objDLpolicyDownload.GetProcessingRecordDetails(clientId);
                if (dtProcessingData != null)
                {
                    foreach (DataRow dr in dtProcessingData.Rows)
                    {
                        PolicyDownload pData = new PolicyDownload();
                        pData.CompanyNo = Convert.ToInt32(dr["CO_NUM"]);
                        pData.CycleDate = Convert.ToString(dr["CYCLE_DATE"]);
                        pData.PolicyNo = Convert.ToString(dr["POLICY_NUMBER"]);
                        pData.IssueDate = Convert.ToString(dr["ISSUE_DATE"]);
                        pData.TransactionDate = Convert.ToString(dr["TRANSACTION_DATE"]);
                        pData.TransactionAmount = Convert.ToDouble(dr["TRANSACTION_AMOUNT"]);
                        pData.ItemCode = Convert.ToInt32(dr["ITEM_CODE"]);
                        pData.Account = Convert.ToString(dr["ACCOUNT"]);
                        pData.OwnerName = Convert.ToString(dr["OWNER_NAME"]);
                        LstPData.Add(pData);
                    }
                }
            }
        }
        catch (Exception ex)
        {
            if (logChoice)
            {
                log.Error(ex.Message, ex);
            }
        }
        var data = from s in LstPData
                   select s;
        return Json(new { data = data }, JsonRequestBehavior.AllowGet);
    }

Below is the JSON output of this action method:

{"data":[{"PolicyDownloadID":0,"ImportDate":null,"CycleDate":"10/23/2017","CompanyID":0,"CompanyNo":40,"PolicyNo":"L0000001","PlanCode":null,"InsuranceType":null,"IssueDate":"05/02/2011","TransactionDate":"08/03/2017","TransactionAmount":7545.59,"ItemCode":0,"Account":"SBEN","Mode":null,"ModeAmount":0,"PayerName":null,"OwnerName":"CERNY, RAYMOND C","InsuredName":null,"PayeeName":null,"JointOwner":null,"SBC":0,"SBCAmount":0,"IsActive":false,"CreatedOn":"\/Date(-62135575200000)\/"},{"PolicyDownloadID":0,"ImportDate":null,"CycleDate":"10/23/2017","CompanyID":0,"CompanyNo":6,"PolicyNo":"FGL01234","PlanCode":null,"InsuranceType":null,"IssueDate":"07/23/2010","TransactionDate":"08/02/2017","TransactionAmount":26999.62,"ItemCode":0,"Account":"SBEN","Mode":null,"ModeAmount":0,"PayerName":null,"OwnerName":"BURNHAM, STEVEN L","InsuredName":null,"PayeeName":null,"JointOwner":null,"SBC":0,"SBCAmount":0,"IsActive":false,"CreatedOn":"\/Date(-62135575200000)\/"}]}

Below is the screenshot where it stuck:

It is calling my action method but unable to bind the JSON data.

have a look in browser console does it fires any error which will help to resolve your issue fast. — Saineshwar Bageri - MVP
– Saineshwar Bageri - MVP, Commented Dec 3, 2017 at 6:30

Mayank Pandey · Accepted Answer · 2017-12-04 14:34:54Z

1

success function - Must not be overridden as it is used internally in DataTables. So remove success function and try once, hope it will help you.

answered Dec 4, 2017 at 14:34

Mayank Pandey

1851 silver badge13 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Aleksey 'Tkhenn' Senilov · Accepted Answer · 2017-12-03 08:01:29Z

0

DataTables requires json in certain format, look at Ajax tab in DataTables Documentation. Your answer in action don't corresponds to this format. Try to add wrapper class (real or anonymous) with "data" field, which will be your list of objects.

answered Dec 3, 2017 at 8:01

Aleksey 'Tkhenn' Senilov

262 bronze badges

Comments

Aamir Ali · Accepted Answer · 2017-12-03 11:25:03Z

0

Few things to take care while implementing server side datatable:

Your datatable in view must match number of columns coming with data.
The column names which you are mentioning in datatable should be same as in database table.

For implementing server side searching, add in a row textboxes below header row for each column. you need to bind each textbox keyup event for relevent column and pass it to server and rebind datatable

answered Dec 3, 2017 at 11:25

Aamir Ali

1913 silver badges11 bronze badges

Collectives™ on Stack Overflow

Unable to bind jQuery DataTable in AJAX call with JSON data

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related