How to pass a json to DataTable

Question

I have Json object from a method that I would like to display to a datatable but can't pass it to jquery. It runs without errors but it is displaying raw json data instead of the page with datatable and displaying the api route in the address bar https://localhost:44382/api/StoreReport instead of the page address.

How can I force the page to reload showing the json data on my datatable? Here is my code looks like, I am using ASP.NET CORE Razor Pages.

StoreReport.cshtml

<div class="row">
    @using (Html.BeginForm("FetchReport", "StoreReport", FormMethod.Post, new { enctype = "multipart/form-data" }))
    {
        <div class="col-4 d-inline-block">
            <select name="PackageID" asp-for="Package.Id" asp-items="Model.StoreList" class="form-select">
                <option disabled selected>-- Select Store --</option>
            </select>
            <span asp-validation-for="StoreList" class="text-danger"></span>
        </div>
 
        <div class="col-4 d-inline-block">
            <input type="submit" class="btn btn-primary" value="Fetch Report">
        </div>
    }
</div>
<div class="row">
        <table id="DTStoreReport">
            <thead>
                <tr>
                    <th>Store Name</th>
                    <th>Package Name</th>
                    <th>Contact</th>
                </tr>
            </thead>
        </table>
    </div>
</div>

@section Scripts{
    <script src="~/js/storeReport.js"></script>
}

StoreReportController

[HttpPost]
public IActionResult FetchReport()
{
    int PackageID = (int)Convert.ToInt64(Request.Form["PackageID "]);
    if (PackageID != 0)
    {     
        GetLocationsReport();
    }

    return Json(new { data = LocationsReport });
}

storeReport.js

$(document).ready(function () {
    dataTable = $('#DTStoreReport').DataTable({
        "ajax": {
            "url": "/api/StoreReport",
            "type": "POST",
            "datatype": "json"
        },
        buttons": ["copy", "csv", "excel", "pdf", "print"],
        "columns": [
            { "data": "StoreName", "width": "15%" },
            { "data": "PackageName", "width": "15%" },
            { "data": "Contact", "width": "10%" },
        ],
        "width": "100%"
    });
}

Answer 1

I have Json object from a method that I would like to display to a datatable but can't pass it to jquery.

You are using html tag helper to submit your request using this approach your page state will end just after clicking the submit button thus, jquey table will not be populated because will wouldn't be initiated. Instead you should use onlick event to handle this scenario. Remember, mixing both razor syntax and jquery causing this issue.

You can do as following:

Razor Page HTML:

<div class="row">
    <div class="col-4 d-inline-block">
        <select name="Id" asp-for="Package.Id" asp-items="Model.StoreList" class="form-select">
            <option disabled selected>-- Select Store --</option>
        </select>
        <span asp-validation-for="StoreList" class="text-danger"></span>
    </div>

    <div class="col-4 d-inline-block">
        <input type="submit" class="btn btn-primary" value="Fetch Report" onclick="GetAjaxResponse()">
    </div>

</div>
<hr />
<table id="DTStoreReport" class="table table-striped table-bordered" style="width:100%">
    <thead class="thead-dark">
        <tr class="table-info">
            <th>Store Name</th>
            <th>Package Name</th>
            <th>Contact</th>

        </tr>
    </thead>
</table>

Script:

@section scripts {
    <script src="https://ajax.aspnetcdn.com/ajax/jQuery/jquery-3.2.1.min.js"></script>
    <script src="https://cdn.datatables.net/1.11.3/js/jquery.dataTables.min.js"></script>
    <script>
        function GetAjaxResponse() {
       
            dataTable = $('#DTStoreReport').DataTable({
                "ajax": {
                    "url": "/JqueryDatatable?handler=FetchReport",
                    "type": "POST",
                    "data": { "PackageID": $('#Package_Id').val() },
                    "datatype": "json"
                   
                },
                "buttons": ["copy", "csv", "excel", "pdf", "print"],
                "columns": [
                    { "data": "storeName", "width": "15%" },
                    { "data": "packageName", "width": "15%" },
                    { "data": "contact", "width": "10%" },
                ],
                "width": "100%"
            });
           
        }
    </script>
    }

Note: I am using Razor page metehod OnPostFetchReport thefore, I am using this URL pattern "/JqueryDatatable?handler=FetchReport" where JqueryDatatable is razor page name and FetchReport is a method. .If you are using API controller just use your Controller and Action URL.

Controller:

public IActionResult OnPostFetchReport(int PackageID)
    {
        var LocationsReport = new List<ClassLocations>();
       
        if (PackageID != 0)
        {
            LocationsReport = GetLocationsReport();
        }
       
        return new JsonResult(new { data = LocationsReport });
    }

Method which returns report:

public List<ClassLocations> GetLocationsReport()
        {
            var newLocations = new List<ClassLocations>()
                 {
                  new ClassLocations {Id=1,StoreName = "Store-1", PackageName = "Package-1",Contact = "Contact-1" },
                  new ClassLocations {Id=2,StoreName = "Store-2", PackageName = "Package-2",Contact = "Contact-2" },
                  new ClassLocations {Id=3,StoreName = "Store-3", PackageName = "Package-3",Contact = "Contact-3" },

                 };
            return newLocations;
        }

Output: