How to display data on jQuery Datatable in ASP.NET Core MVC on a button click event

Question

I am new to jQuery Datatables using AJAX in ASP.NET Core 6 MVC. I have enclosed my code in the view as well as in the controller. I am trying to get data from the controller and display it on the view containing a DataTable on a buttonclick event.

View:

<div>
    <div style="padding-top: 20px; padding-bottom:20px">
            <button class="btn btn-lg btn-primary" onclick="javascript:runReport();">Run 
                 Report</button>
    </div>
    <div>
        <table class="table table-bordered table-striped" id="riskReport">
            <thead class="bg-dark text-white">
                <tr>
                    <th>CustomerNumber</th>
                    <th>CustomerStatus</th>
                    <th>StartYear</th>
                </tr>
            </thead>
            <tbody>

            </tbody>
        </table>
    </div>
</div>
@section Scripts {
    @{
                        <partial name="_ValidationScriptsPartial" />
                        <partial name="_DataTableCDN" />
    }
    <script>
        
        function runReport() {
            
            var reportData;
            
            var dataTableOptions = {
                "paging": true,
                "lengthChange": false,
                "searching": false,
                "ordering": true,
                "info": true,
                "autoWidth": false,
                "responsive": true,
                //"columnDefs": "",
                "ajax": {
                    url: "/Reports/ProcessReport",
                    type: "POST",
                    data: reportData,
                    "datatype": "json",
                    "error": function (e) {
                        alert(e)
                    },
                    
                    "dataSrc": function (d) {
                        alert(JSON.stringify(d))
                        return JSON.stringify(d);
                        //return d;
                    },
                    "columns":[
                        { "data": "CustomerNumber", "name": "CustomerNumber", "autoWidth": 
           true },
                        { "data": "CustomerStatus", "name": "CustomerStatus", "autoWidth": 
            true },
                        { "data": "StartYear", "name": "StartDate", "autoWidth": true }
                    ]
                    
                }
            }
            
            reportData = $('#reportForm').serializeArray();
            var myDataTable = $('#riskReport').DataTable(dataTableOptions);
        }
                
    </script>

Controller:

[HttpPost]
public async Task<string> ProcessReport(ReportsConfigurationVM reportsConfigurationVM)
{
    List<RiskByYearTest> myList = new List<RiskByYearTest>();

    RiskByYearTest r1 = new RiskByYearTest();
    r1.CustomerNumber = "4549601";
    r1.CustomerStatus = "Active";
    r1.StartYear = "2022";

    RiskByYearTest r2 = new RiskByYearTest();
    r2.CustomerNumber = "4549602";
    r2.CustomerStatus = "Closed";
    r2.StartYear = "2023";

    myList.Add(r1);
    myList.Add(r2);

    return JsonConvert.SerializeObject(myList);
}

The controller returns the following two rows of data.

[
    {
        "CustomerNumber": "4549601",
        "CustomerStatus": "Active",
        "StartYear": "2022"
    },   
    {
        "CustomerNumber": "4549602",
        "CustomerStatus": "Closed",
        "StartYear": "2023"
    }
]

However, I am getting this popup error message and the data is not displayed on the grid. The empty grid says, "No data available in table".

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

Answer 1

1. With ajax.dataSrc, you provide the value with '' indicates that your data is from a plain array.

2. The columns shouldn't be placed in the ajax object, but it is a root attribute and the same level as ajax.

The structure of the dataTableOptions is as follows:

var dataTableOptions = {
  paging: true,
  lengthChange: false,
  searching: false,
  ordering: true,
  info: true,
  autoWidth: false,
  responsive: true,
  //"columnDefs": "",
  ajax: {
    url: '/Reports/ProcessReport',
    type: "POST",
    data: reportData,
    datatype: 'json',
    error: function (e) {
      alert(e);
    },
    dataSrc: '',
  },
  columns: [
    {
      data: 'CustomerNumber',
      name: 'CustomerNumber',
      autoWidth: true,
    },
    {
      data: 'CustomerStatus',
      name: 'CustomerStatus',
      autoWidth: true,
    },
    { data: 'StartYear', name: 'StartDate', autoWidth: true },
  ],
};

Demo (with mocked request) @ StackBlitz