How to load data into datatable using ajax in mvc

Question

I'm loading List of objects from database into datatable using ajax. When debugging, the my MVC action result seem to query the data alright but the datatable column displays null

I've tried to serialize the list before returning it in the MVC action but it didn't solve the problem

// Code from View

<table class="table table-striped" id="codetable">
<thead>
    <tr>
        <td>Student Number</td>
        <td>Student</td>
        <td>Faculty</td>
        <td>Department</td>
        <td>Program</td>
        <td>Code</td>
    </tr>
</thead>
<tbody>

</tbody>
</table>

<script>
    $(document).ready(function () {
        $("#codetable").DataTable({
            processing: true,
            serverSide: true,
            info: true,
            ajax: {
                url: '@Url.Action("GetVoters", "Index")',
                dataSrc: ""
            },

            Columns: [
                { "data": "StudentNumber" },
                { "data": "Name" },
                { "data": "Faculty" },
                { "data": "Department" },
                { "data": "Program" },
                { "data": "Code" }
            ]
        });
    });
</script>


//Code from Controller

public JsonResult GetVoters()
{
List<vt> stud = (from student in _context.Voters
                      select new vt
                      {
                          StudentNumber = student.StudentNumber,
                          Name = student.Name,
                          Faculty = student.Faculty,
                          Department = student.Department,
                          Program = student.Program,
                          Code = student.Code
                      }).Take(100).ToList();
        var js = new System.Web.Script.Serialization.JavaScriptSerializer();
        var result = js.Serialize(stud);
        return Json(result, JsonRequestBehavior.AllowGet);
    }

public class vt
{
    public string StudentNumber { get; set; }
    public string Name { get; set; }
    public string Faculty { get; set; }
    public string Department { get; set; }
    public string Program { get; set; }
    public string Code { get; set; }
}

I expect the table to display various columns in the list but is throws this error "DataTables warning: table id=codetable - Requested Unknown parameter '1' for row 0, column 1..." and displays results only in the first column(thus a character per row). The rest of the columns show null

Displayed Error

Complete your codes. Atction, ajax, datatable rendering – Siamak Ferdos Mar 30 '19 at 11:10 — Siamak Ferdos, Mar 30 '19 at 11:10

Rahul Sharma · Accepted Answer · 2019-03-30T19:55:35.773

UPDATE

I found a better way to use AJAX for your source data from the Controller. Please use this method for your DataTable grid with AJAX:

In order to show your data via AJAX in your DataTable plugin, make the following changes in your code:

Add a model called DataTable

public class DataTable
{
    public List<vt> data { get; set; }
}

Then in your Controller:

public JsonResult GetVoters()
{
 DataTable dataTable = new DataTable();
 List<vt> stud = (from student in _context.Voters
                      select new vt
                      {
                          StudentNumber = student.StudentNumber,
                          Name = student.Name,
                          Faculty = student.Faculty,
                          Department = student.Department,
                          Program = student.Program,
                          Code = student.Code
                      }).Take(100).ToList();
    //The magic happens here
    dataTable.data = stud;
    return Json(dataTable, JsonRequestBehavior.AllowGet);
  }

And finally in your View, use this script to populate your DataTable:

 <script type="text/javascript">
    $(document).ready(function () {

        //For filtering:
        $('#codetable thead tr').clone(true).appendTo('#codetable thead');
        $('#codetable thead tr:eq(1) th').each(function (i) {
            var title = $(this).text();
            $(this).html('<input type="text" placeholder="Search ' + title + '" />');

            $('input', this).on('keyup change', function () {
                if (table.column(i).search() !== this.value) {
                    table
                        .column(i)
                        .search(this.value)
                        .draw();
                }
            });
        });

        var table=$('#codetable').DataTable({
            "ajax": '@Url.Action("GetVoters", "Index")',
            "columns": [
                { "data": "StudentNumber" },
                { "data": "Name" },
                { "data": "Faculty" },
                { "data": "Department" },
                { "data": "Program" },
                { "data": "Code" }
            ]
        });
    });
</script>

And I almost forgot, change the structure of your HTML table also for your filtering purpose:

<table class="table table-striped" id="codetable">
        <thead>
            <tr>
                <th>Student Number</th>
                <th>Student</th>
                <th>Faculty</th>
                <th>Department</th>
                <th>Program</th>
                <th>Code</th>
            </tr>
        </thead>
        <tbody></tbody>
</table>

I have used DataTables with AJAX objects as datasource for your grid.

Cheers.

The data loads fine, Thanks but searching and paging doesn't work although I've set bFilter to true — Shagragada, Mar 30 '19 at 13:15
You would have to add those filters explicitly which also includes your search. Since you as posting to the server for your data, you would need to filter and serch from your list. — Rahul Sharma, Mar 30 '19 at 13:21
@Shagragada Please find updated answer with searching and filtering and a better way to initialize your DataTable via AJAX. — Rahul Sharma, Mar 30 '19 at 19:50

score 0 · Answer 2 · answered Mar 31 '19 at 20:06

It also worked when I read the data from an API instead of the Controller. And in this case DataTables retained its default filtering, sorting and pagination. When debugging, the format of the data returned by both the API and the Controller JsonResult seem the same. I really cannot explain why the API works but the controller doesn't.

//The API Code

public IEnumerable<vt> GetStudents()
    {
        return _context.Voters.Select(x=>new vt { StudentNumber = x.StudentNumber, Name = x.Name, Faculty = x.Faculty, Department = x.Department, Program = x.Program, Code = x.Code }).ToList();
    }



//The only change in the jquery script is the url which now points to the API

<script>
$(document).ready(function () {
    $("#codetable").DataTable({
        processing: true,
        serverSide: true,
        info: true,
        ajax: {
            url: "/api/Students",
            dataSrc: ""
        },

        Columns: [
            { "data": "StudentNumber" },
            { "data": "Name" },
            { "data": "Faculty" },
            { "data": "Department" },
            { "data": "Program" },
            { "data": "Code" }
        ]
    });
});

This link will provide you a good insight why the API works in this case: https://stackoverflow.com/questions/9494966/difference-between-apicontroller-and-controller-in-asp-net-mvc — Rahul Sharma, Apr 01 '19 at 07:19

How to load data into datatable using ajax in mvc

2 Answers2

Linked