Showing 0 to 0 of 0 entries (filtered from NaN total Entries) server side processing issue

Ramirez77

I'm having an issue with a web page that I creating using datatables. I set up the datatable and got the data to appear correctly and going to the next page works but the information at the bottom shows NaN instead of numbers and there is no last page number just NaN. My datatable script is as follows, I probably have a lot of unnecessary script in there right now since I've been trying to debug this issue and been trying everything to get it to work.

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

                processing: true,
                serverSide: true,
                ajax: {
                    url: "CaseOverviewTest.aspx/GetDataTableData",
                    type: "POST",
                    contentType: "application/json; charset=utf-8",
                    dataType: "json",
                    data: function (data) {
                        console.log("Sending AJAX request with data:", data);
                        return JSON.stringify({
                            draw: data.draw,
                            start: data.start,
                            length: data.length,
                            searchValue: data.search.value,
                            sortColumn: data.order[0].column,
                            sortDirection: data.order[0].dir,
                            data: data
                        });
                    },
                    dataSrc: function (response) {
                        var responseData = JSON.parse(response.d);
                        console.log("Received AJAX response:", responseData);
                        
                        dataTable.totalRecords = response.recordsTotal;
                        dataTable.filteredRecords = response.recordsFiltered;

                        return responseData.data;
                    },
                    error: function (xhr, error, description) {
                        console.error(description);
                    }
                },
                searching: true,
                paging: true,
                ordering: true,
                columns: [
                    { data: "ProjectID", visible: false },
                    { data: "CaseID", searchable: true },
                    { data: "SessionID" },
                    { data: "Interviewer" },
                    { data: "ReviewCode" },
                    {
                        data: "ReviewDate",
                        render: function (data, type, row) {
                            // Assuming 'ReviewDate' is a datetime column
                            if (type === 'display' || type === 'filter') {
                                // Check if 'ReviewDate' is null
                                if (data !== null) {
                                    // Format the datetime for display (YYYY-MM-DD HH:mm:ss)
                                    return new Date(data).toLocaleString('en-US', {
                                        year: 'numeric',
                                        month: '2-digit',
                                        day: '2-digit',
                                        hour: '2-digit',
                                        minute: '2-digit',
                                        second: '2-digit'
                                    });
                                } else {
                                    // Return an empty string for null values
                                    return '';
                                }
                            }
                            return data;
                        }
                    },
                    { data: "AssignedTo" },
                    {
                        data: "InterviewDate",
                        render: function (data, type, row) {
                            // Assuming 'InterviewDate' is a datetime column
                            if (type === 'display' || type === 'filter') {
                                // Check if 'InterviewDate' is null
                                if (data !== null) {
                                    // Format the datetime for display (YYYY-MM-DD HH:mm:ss)
                                    return new Date(data).toLocaleString('en-US', {
                                        year: 'numeric',
                                        month: '2-digit',
                                        day: '2-digit',
                                        hour: '2-digit',
                                        minute: '2-digit',
                                        second: '2-digit'
                                    });
                                } else {
                                    // Return an empty string for null values
                                    return '';
                                }
                            }
                            return data;
                        }
                    },
                    { data: "StatusCode" },
                    { data: "ClientCase" },
                    { data: "CaseLocked" },
                    {
                        data: null,
                        render: function (data, type, row) {
                            var Links = "<a href=\"/SurveyMonitoring/CaseReview.aspx?ProjectID="+row.ProjectID+"&CaseID="+row.CaseID+"&SessionID="+row.SessionID+"&Interviewer="+row.Interviewer+"\">Review</a><a href=\"/SurveyMonitoring/CaseChangeLog.aspx?CaseID="+row.CaseID+"&SessionID="+row.SessionID+"\">History</a>"
                            return Links
                        }
                    }
                ],
                info: true
            });
            console.log(dataTable);
        });
    </script>

My backend C# code that is getting the data is as follows:

[WebMethod]
    public static string GetDataTableData(int draw, int start, int length, string searchValue, string sortColumn, string sortDirection)
    {
        string currProj = HttpContext.Current.Session["CurrentProject"].ToString();

        DataTable dt = CaseManager.GetCasesTest(currProj, Int32.Parse(sortColumn), sortDirection, start, length, searchValue);

        int TotalCount = CaseManager.GetCaseCount(currProj);

        int FilteredCount = CaseManager.GetFilteredCaseCount(currProj, searchValue);

        // Convert DataTable to List of objects
        List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>();
        foreach (DataRow dr in dt.Rows)
        {
            Dictionary<string, object> row = new Dictionary<string, object>();
            foreach (DataColumn col in dt.Columns)
            {
                row.Add(col.ColumnName, dr[col]);
            }
            rows.Add(row);
        }

        // Prepare the response object
        var data = new
        {
            draw = draw,
            recordsTotal = TotalCount,
            recordsFiltered = FilteredCount,
            data = rows
        };

        // Serialize the response object to JSON
        string json = JsonConvert.SerializeObject(data, Formatting.Indented);

        return json;
    }

I checked the json I am returning frequently to see if it was the issue, here is the head of the json data that is being returned to the datatable.

{
  "draw": 1,
  "recordsTotal": 9188,
  "recordsFiltered": 9188,
  "data": [
    { 
    normal data here
    }
  ]
}

I can't provide a webpage to test out since it as PII on it but here is a screenshot of the issue that I am trying to resolve.

Any help with this issue would be greatly appreciated.

kthorngren

I checked the json I am returning

Where did you check this? Did you use the browser's network inspector tool?

I believe I've seen in the past when the response looks like this:

var responseData = JSON.parse(response.d);

the resulting responseData might also be a JSON string. SO basically the server script is JSON encapsulating the data then, when its packaged in the d object, it is JSON encapsulated again. You might need to do something like this:

var responseData = JSON.parse(response.d);
responseData = JSON.parse(responseData);

Or some other combination to get the JSON data parsed properly.

Kevin

allan

                    dataSrc: function (response) {
                        var responseData = JSON.parse(response.d);
                        console.log("Received AJAX response:", responseData);
                         
                        dataTable.totalRecords = response.recordsTotal;
                        dataTable.filteredRecords = response.recordsFiltered;
 
                        return responseData.data;
                    },

is the issue. DataTables isn't seeing recordsTotal and friends because that doesn't exist on the Ajax response object.

With v1.x you need to specify ajax as a function, make the Ajax call and then pass the parsed JSON back to the callback function.

However, it is now possible to handle this with ajax.dataSrc in DataTables 2 - you could do:

dataSrc: {
  data: (response) => JSON.parse(response.d).data,
  draw: (response) => JSON.parse(response.d).draw,
  recordsTotal: (response) => JSON.parse(response.d).recordsTotal,
  recordsFiltered: (response) => JSON.parse(response.d).recordsFiltered
}

It seems rather complicated though. I've no idea why Microsoft decided to wrap perfectly good JSON in another object and make it a string, but you are far from the first to run into this issue. I'm very tempted to handle d as a string automatically in DataTables...

Allan

allan

First change for DataTables 2.1 . Probably a while from release, but it will make it out one day!

Allan