DataTables problem rendering from Ajax Json from Razor Pages .Net Get

Question

I have been trying to follow this example from DataTables (https://datatables.net/examples/api/row_details.html) and just can't seem to get the table data to load. My ajax call is hitting the breakpoint in the .Net .cshtml.cs code-behind file and returning the json object. But the data table just shows 'Loading'. Would really appreciate if anyone has any clues as to what else I can try and I've attempted many different things based on other examples. Thanks!

Here is my OnGet method, which the ajax call calls (verified through breakpoint hits):

public JsonResult OnGetAllCustomers()
{
    List<Customer> lstCustomers = new List<Customer>();
    lstCustomers = objCustomerDAL.GetAllCustomers();
    var output = JsonConvert.SerializeObject(lstCustomers);
    return new JsonResult("\"data\":" + output);
}

Here is my table definition:

        <div class="row">
            <table id="CustomersTable" class="display table-striped table-bordered" style="width:100%">
                <thead>
                    <tr>
                        <th></th>
                        <th>Company Name</th>
                        <th>Contact Name</th>
                        <th>Contact Title</th>
                        <th>City</th>
                        <th>State</th>
                    </tr>
                </thead>
            </table>
        </div>

Here is the JsonResult in the Text Visualizer:

"data":[{"CustomerID":106,"CompanyName":"Clean Companyee","ContactName":"Mr Clean we","ContactTitle":"Washeree","Address":"111 Clean Steet","City":"Scrubee","State":"MT","ZipCode":"22222","Phone":"333-444-5555","Email":"clean@brush.com","DateCreated":"2024-01-30T11:29:15.997","UserIDCreated":4695,"DateLastModified":"2024-02-16T14:44:58.377","UserIDLastModified":4695},{"CustomerID":107,"CompanyName":"Bill's Paper Mill","ContactName":"Bill Sorenson","ContactTitle":"Owner","Address":"384 Running Brook Lane","City":"Dallas","State":"TX","ZipCode":"25415","Phone":"747-666-3333","Email":"Bill@Paper.com","DateCreated":"2024-01-18T14:42:10","UserIDCreated":4695,"DateLastModified":"2024-01-23T11:46:34.943","UserIDLastModified":4695},
{"CustomerID":108,"CompanyName":"The Addition Company","ContactName":"Joe Math","ContactTitle":"Mathemetician","Address":"333 Flower Lane","City":"Pittsburgh","State":"PA","ZipCode":"25412","Phone":"666-666-9999","Email":"joe@math.com","DateCreated":"2024-01-16T09:14:48.127","UserIDCreated":4695,"DateLastModified":"2024-01-16T09:14:48.127","UserIDLastModified":4695}, ... etc

And here is the JQuery, which is just like the example, but with the revised data columns and the Ajax using Get in .Net vs a text file.

<script src="~/lib/jquery/dist/jquery.min.js"></script>
<script src="https://code.jquery.com/jquery-3.7.1.js"></script>
<script src="https://cdn.datatables.net/2.0.0/js/dataTables.js"></script>

<script>
    // Formatting function for row details - modify as you need
    function format(d) {
        // `d` is the original data object for the row
        return (
            '<dl>' +
            '<dt>Phone: </dt>' +
            '<dd>' +
            d.phone +
            '</dd>' +
            '<dt>Email: </dt>' +
            '<dd>' +
            d.email +
            '</dd>' +
            '<dt>Extra info:</dt>' +
            '<dd>And any further details here (images etc)...</dd>' +
            '</dl>'
        );
    }

    let table = new DataTable('#CustomersTable', {
        ajax: {
            url: 'CustomersFlyout?handler=AllCustomers',
            dataSrc: 'data'
        },
        columns: [
            {
                className: 'dt-control',
                orderable: false,
                data: null,
                defaultContent: ''
            },
            { data: "companyname" },
            { data: "contactname" },
            { data: "contacttitle" },
            { data: "city" },
            { data: "state" }
        ],
        order: [[1, 'asc']]
    });

    table.on('click', 'td.dt-control', function (e) {
        let tr = e.target.closest('tr');
        let row = table.row(tr);

        if (row.child.isShown()) {
            // This row is already open - close it
            row.child.hide();
        }
        else {
            // Open this row
            row.child(format(row.data())).show();
        }
    });
</script>

I know the Json is being returned but cannot get the data to populate to the table.

Answer 1

Your Json result is not valid Json. In the documentation link you provided you can check the Ajax tab to see how it should look like. Basically you are missing the braces at the start and the end.

Instead of serializing and concatenating the Json manually, the following code should give you the correct result:

public JsonResult OnGetAllCustomers()
{
    List<Customer> lstCustomers = new List<Customer>();
    lstCustomers = objCustomerDAL.GetAllCustomers();
    
    return new JsonResult(new
    {
        data = lstCustomers
    });
}