Getting serialised JSON data from Controller to Datatables table

Question

I am struggling to get serialised JSON data from my controller to display in a Datatable. I previously was able to display data when I was sending it as a 2D array. Now I would like to be able to use my object and match it with the columns (as seen in the second JavaScript). I attempted to follow this solution, but I am unable to see what is wrong after looking at the Datatables docs.

Updated See below what I currently have. I am trying to have the data come through with named parameters as I wish to have parent rows with a list of child items. These items are visible on selection as seen in this DataTables link. Though I am still unable to get the table working with named parameters via JSON data.

Data:

        for (int i = 0; i < 26; i++)
        {
            list.Add(new MembershipTransactionHistoryModel
            {
                TransactionDate = "01 May 2014",
                StoreName = "Store Name",
                CardNumber = "23423566",
                TransactionType = "Purchase",
                TransactionValue = "$134.25",
                PointsEarned = "100",
                PointsUsed = "23",
                PointsBalance = "40000"
            });
        }

        //return new JavaScriptSerializer().Serialize(list);
        return from l in list
                         select new[] {
                            l.TransactionDate,
                            l.StoreName, 
                            l.CardNumber,
                            l.TransactionType,
                            l.TransactionValue,
                            l.PointsEarned,
                            l.PointsUsed,
                            l.PointsBalance 
                         };

JavaScript:

var grid = $('#transactionHistoryTable').dataTable({
    "bProcessing": true,
    "sAjaxSource": 'MembershipTransactionHistoryData',
    "fnServerParams": function (aoData) {
        aoData.push({ "name": "membershipId", "value": 7 })
    },
    "aoColumnDefs": [
        {
            "sTitle": "",
            "class": 'details-control',
            "orderable": false,
            "mData": null,
            "aTargets": [0],
            "defaultContent": ''
        },
        { "sTitle": "Transaction Date", "mData" : "TransactionDate", "aTargets": [1]},
        { "sTitle": "Store Name", "mData": "StoreName", "aTargets": [2] },
        { "sTitle": "Card Number", "mData": "CardNumber", "aTargets": [3] },
        { "sTitle": "Type", "mData": "Type", "aTargets": [4] },
        { "sTitle": "Value", "mData": "Value", "aTargets": [5] },
        { "sTitle": "Points Earned", "mData": "PointsEarned", "aTargets": [6] },
        { "sTitle": "Points Used", "mData": "PointsUsed", "aTargets": [7] },
        { "sTitle": "Points Balance", "mData": "PointsBalance", "aTargets": [8] }
    ],
    "paginate": true,
    "scrollY": maxHeight,
    "scrollCollapse": false,
    "sort": true
});

HTML:

    <table id="transactionHistoryTable" class="display" cellspacing="0">
        <thead>
            <tr>
            </tr>
        </thead>

        <tbody>
        </tbody>
    </table>

Resulting Error:

DataTables warning: table id=transactionHistoryTable - Requested unknown parameter 'TransactionDate' for row 0. For more information about this error, please see http://datatables.net/tn/4

Yes your JSON is incorrect. If you have any other extra columns in your table, for that as well you need to pass the data (at least empty). I'm seeing there is an empty column <th></th> for that you need to return data from server call. that's why the error of undefined. — Rajeshkumar Kandhasamy
– Rajeshkumar Kandhasamy, Commented Nov 14, 2014 at 6:09
@RJK I have removed the <th> elements entirely as I am now defining my columns as per the aoColumnDefs JavaScript. I will update my question to be more succinct and display what I currently have. — Nashibukasan
– Nashibukasan, Commented Nov 16, 2014 at 22:25

Rajeshkumar Kandhasamy · Accepted Answer · 2014-11-13 06:02:02Z

0

You have to return it as String Array [] not just a List.

Result should be in Array, it is must: new string[].

Note: After the For loop add below code:

var results = from l in list
                 select new[] { 
                            l.TransactionDate,
                            l.StoreName , 
                            l.CardNumber,
                            l.TransactionType,
                            l.TransactionValue,
                            l.PointsEarned,
                            l.PointsUsed,
                            l.PointsBalance 
                     };

return Json(results, JsonRequestBehavior.AllowGet); // Return the Result as a string array.

No need to serialize pro-grammatically. It will be serialized automatically.

Data Table expects your results should be in a array. where as you are passing it as a list of class objects.

Take a look at this DataTables - Server side processing Article.

answered Nov 13, 2014 at 6:02

Rajeshkumar Kandhasamy

6,4997 gold badges61 silver badges84 bronze badges

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

4 Comments

Nashibukasan Over a year ago

Appreciate the response. I have tried this change but have not been successful. I will add an edit to my original post to document my results.

Nashibukasan Over a year ago

I should also mention that my intention is to not only send back the data to display in the table (I have that working on numerous other pages), but some extra data to be displayed in a child node. Similar to the DataTables [example here][2] except the expanded data is not in the parent row. I assumed this means I need named variables in my JSON response.

Rajeshkumar Kandhasamy Over a year ago

Maybe this link helps to define the Extra data in a child Node. Check the sample data under the Ajax Tab from the given link.

Nashibukasan Over a year ago

That link is what I was looking at originally. I am unable to get the named parameters through at all though. See my updated questions for a more succinct explanation of where I am at with my issue.

Nashibukasan · Accepted Answer · 2014-12-01 04:17:41Z

The way I managed to do this in the end, was to avoid JavaScript entirely and just structure my HTML accordingly via my view's Model.

Eg.

            <tbody>
                @foreach(var item in Model.GetData())
                {
                    <tr>
                        <td class="details-control"></td>
                        <td>@item.TransactionId</td>
                        <td>@item.TransactionDate</td>
                        <td>@item.StoreName</td>
                        <td>@item.CardNumber</td>
                        <td>@item.TransactionType</td>
                        <td>@item.TransactionValue</td>
                        <td>@item.PointsEarned</td>
                        <td>@item.PointsUsed</td>
                        <td>@item.PointsBalance</td>
                    </tr>
                }
             </tbody>

I then used the documentation from DataTables to construct my sub-item table and performed a post to get that data.

My success method in the ajax post was as below:

        success: function (data) {
            var html = '<table cellpadding="5" cellspacing="0" border="0" style="padding-left:50px;">';

            for (var i = 0; i < data.length; i++) {
                html += '<tr>' +
                            '<td>APN:</td>' +
                            '<td>' + data[i].ProductAPN + '</td>' +
                            '<td>Desc:</td>' +
                            '<td>' + data[i].ProductDescription + '</td>' +
                            '<td>Qty:</td>' +
                            '<td>' + data[i].Quantity + '</td>' +
                            '<td>Amount:</td>' +
                            '<td>' + data[i].Amount + '</td>' +
                            '<td>Points:</td>' +
                            '<td>' + data[i].PointsEarned + '</td>' +
                        '</tr>' 
            }

            html += '</table>';

            // Open this row
            row.child(html).show();
            tr.addClass('shown');
        }

Collectives™ on Stack Overflow

Getting serialised JSON data from Controller to Datatables table

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related