JQuery DataTables does not recognize data loaded

Question

For some reason, my JQuery DataTable is adding data to the table, but it still says "0 to 0 of 0 entries. Trying to use the search box does not actually search through my data, and sorting by my one column does not change the order. I don't get any errors in firebug, so I don't really know where to go from here. Thank you for looking.

Here is the javascript:

var oTable = $("#tblAddUsers").dataTable({
            "processing": true,
            "bSearching": true,
            "bSort": true,
            "bFilter": true,
            sAjaxSource: '@Url.Action("GetUserList", "ClassSchedule")',
            aoColumns: [
                {
                    bVisible: false
                },
                {
                    sWidth: "250px",
                    bSortable: true

                },
                {
                    mRender: function () { return '<button class="clAddUser">Add</button>'; },
                    sWidth: "200px"
                }
            ],

            fnRowCallback: function (nRow, aData, iDisplayIndex, iDisplayIndexFull) {

                $('button', nRow).on('click', function () { 
                    test(aData[0]);
                });
            }


        });

My Controller code:

public JsonResult GetUserList()
    {
        var addUserList = (from u in db.t_user
                           join m in db.t_user_membership on u.user_id equals m.user_id
                           where m.membership_date > DateTime.Today
                           select new { user_id = u.user_id, full_name = u.first_name + " " + u.last_name }).ToList();

        return Json(new { aaData = addUserList.Select(x => new string [] { x.user_id.ToString(), x.full_name }) }, JsonRequestBehavior.AllowGet);
    }

My GET response looks like this:

{"aaData":[["2","test Spouse"],["3","David Parker"]]}

Here is my HTML:

<div id="AddUserPopup" style="display:none" title="">
<span>Add Users</span>
<div style="width: 500px; height: 300px;" id="dialog">
        <table id="tblAddUsers">
            <thead>
                <tr>
                    <th></th>
                    <th>User Name</th>
                    <th></th>
                </tr>
            </thead>
            <tbody>

            </tbody>
        </table>

</div>

Here is a screenshot of what I get:

Answer 1

When using a server-side datasource, you need to handle the searching, sorting & paging in the server code.

Have a look at the parameters that get passed to & from the server here. You're not passing any of this information - for example you get Showing 0 of 0 entries because you're not returning iTotalRecords and iTotalDisplayRecords in the json data. The returned object should look something like this:

return Json(new
{
    param.sEcho,
     iTotalRecords = rowCount,
     iTotalDisplayRecords = rowCount,
    aaData = result,
 }, JsonRequestBehavior.AllowGet)

If you look at Firebug's Net panel, you can see all the parameters that are sent in the request when loading the datatable. You need to get these in your server code and use them in your query, e.g sSearch for searching, iDisplayStart and iDisplayLength for paging.

I'd do something like this:

public JsonResult GetUserList(jQueryDataTableParamModel p)
{
    var addUserList = from u in db.t_user
       join m in db.t_user_membership on u.user_id equals m.user_id
       where m.membership_date > DateTime.Today
       select new mymodel
       { 
           user_id = u.user_id, 
           full_name = u.first_name + " " + u.last_name 
       };
//paging
    var displayedItems = addUserList.Skip(p.iDisplayStart).Take(p.iDisplayLength);
    var rowCount = addUserList.Count();

//  project into json for datatable
    var result = from d in displayedItems
                         select new object[]
                         {
                            d.user_id,
                            d.full_name
                          };
    return Json(new
    {
        param.sEcho,
        iTotalRecords = rowCount,
        iTotalDisplayRecords = rowCount,
        aaData = result,
     }, JsonRequestBehavior.AllowGet);
}

The jQueryDataTableParamModel parameter comes from a good tutorial on using server-side datatables in MVC here