Datatable Not loading more than 300 items json call

nidhincee@gmail.com

Hi All,
I am using datatable.net for loading data in sharepoint webpart. But it is not loading more than 300 items .
_
Javascript function_

 function ReloadTable() {
        var from = jqhdr('#txtDateFrom').val();
        var to = jqhdr('#txtDateTo').val();
        var reqType = jqhdr('#DropDownList1').val();
        if (reqType == "1") reqType = "Issue";
        else if (reqType == "2") reqType = "Service"; else reqType = "All";
        var rpthd = "(" + reqType + ") ";
        if(from !="" && to !="")
          rpthd += " from " + from + "  to " + to ;
        jqhdr.ajax({
            url: "/_layouts/15/Pages/WebMethods.aspx/RequestDetails",
            type: "POST",
            dataType: "json",
            data: JSON.stringify({ 'siteUrl': siteUrlHelpDeskReport, 'listName': listHelpDeskReport, 'dateFrom': jqhdr('#txtDateFrom').val(), 'dateTo': jqhdr('#txtDateTo').val(), 'reqType': jqhdr('#DropDownList1').val() }),
            contentType: "application/json; charset=utf-8",
            async: false,
            success: function (json) {
                var dataSet = JSON.parse(json.d).data;

               var table= jqhdr('#extable').DataTable({
                    destroy: true,
                    data: dataSet,
                    dom: 'Bfrtip',
                    buttons: [
                        
                        {
                            extend: 'copyHtml5',
                            footer: 'true',
                            text: 'copy',
                            title: 'Request Report' + rpthd,
                            exportOptions: {
                                columns: [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12,13,14]
                            }
                        },
                        {
                            extend: 'csvHtml5',
                            footer: 'true',
                            text: 'csv',
                            title: 'Report' + rpthd,
                            exportOptions: {
                                columns: [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12]
                            }
                        },
                        {
                            extend: 'excelHtml5',
                            footer: 'true',
                            text: 'Excel',
                            title: 'Report' + rpthd,
                            exportOptions: {
                                columns: [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12]
                            }
                        },
                        {
                            extend: 'pdf',
                            footer: 'true',
                            text: 'pdf',
                            orientation: 'landscape',
                            pageSize: 'LEGAL',
                            title: 'Report' + rpthd,
                            exportOptions: {
                                columns: [ 1, 2, 3,4,5,6,7,8,9,10,11,12]
                            }
                        },
                        
                    ],
                    deferRender: false,
                    "columns": [
                    {
                        "data": "ID", "title": "ID", "visible": false
                       
                    },
                    {
                        "data": "RequestNo", "title": "Request No", "fnCreatedCell": function (nTd, sData, oData, iRow, iCol) {
                            jqhdr(nTd).html("<a href='http://"+window.location.host+"/EN/HelpDesk/Lists/Service%20Requests/DispForm.aspx?ID=" + oData.ID + "'>" + oData.RequestNo + "</a>");
                        }
                    },
                    { "data": "RequestedBy", "title": "Requested By" },
                   { "data": "Title", "title": "Title" },
                    { "data": "AssignedTo", "title": "Assigned To" },
                    { "data": "RequestedService", "title": "Requested Service" },
                   { "data": "RequestStatus", "title": "Request Status" },
                   { "data": "RequestDate", "title": "Request Date" },
                   { "data": "KPI", "title": "KPI" },
                   { "data": "StartDate", "title": "Start Date" },
                   { "data": "ExpectedEndDate", "title": "Expected End Date" },
                    { "data": "EndDate", "title": "End Date" },
                    { "data": "Conformity", "title": "Conformity" },
                    { "data": "TCDays", "title": "TCDays" },
                    { "data": "Solution", "title": "Solution" }
                    ]
                  
               });

               // // Filter event handler
               //jqhdr(table.table().container()).on('keyup', 'tfoot input', function () {
               //    table
               //        .column(jqhdr(this).data('index'))
               //        .search(this.value)
               //        .draw();
               //});
            }
        });


    }

Please help me . I am new in datatable.net

nidhincee@gmail.com

.net code

public static string RequestDetails(string siteUrl, string listName, string dateFrom, string dateTo, string reqType)
{
try
{
DataTable servReqsts = DALHelper.GetDataTable(siteUrl, listName, "", 0, false, false, false, string.Empty, false);
List<BACAServiceRequests> lstserviceReq = new List<BACAServiceRequests>();
WebMethods webMethods = new WebMethods();
lstserviceReq = webMethods.ConvertDataTable<BACAServiceRequests>(servReqsts).ToList().OrderByDescending(s => s.ID).ToList();

            DateTime dtfrom = new DateTime();
            DateTime dtto = new DateTime();
            if (!string.IsNullOrEmpty(dateFrom))
            {
                DateTime dt1 = DateTime.ParseExact(dateFrom, "dd/MM/yyyy", null);
                dateFrom = dt1.ToString("dd/MM/yyyy");
            }
            // thisDate1.ToString("MMMM dd, yyyy")
            if (!string.IsNullOrEmpty(dateFrom))
                dtfrom = DateTime.ParseExact(dateFrom, "dd/MM/yyyy", null);

            if (!string.IsNullOrEmpty(dateTo))
            {
                DateTime dt2 = DateTime.ParseExact(dateTo, "dd/MM/yyyy", null);
                dateTo = dt2.ToString("dd/MM/yyyy");
            }
            // thisDate1.ToString("MMMM dd, yyyy")
            if (!string.IsNullOrEmpty(dateTo))
                dtto = DateTime.ParseExact(dateTo, "dd/MM/yyyy", null);


            if (!(dtfrom == new DateTime()) && !(dtto == new DateTime()))
            {
                lstserviceReq = lstserviceReq.Where(c => c.Requested_x0020_Date.Date != null ? (c.Requested_x0020_Date.Date >= dtfrom && c.Requested_x0020_Date.Date <= dtto) : c.Requested_x0020_Date != null).ToList();
            }
            if (reqType == "1") reqType = "Issue";
            else if (reqType == "2") reqType = "Service"; else reqType = "";
            if (!string.IsNullOrEmpty(reqType))
            {
                lstserviceReq = lstserviceReq.Where(c => c.Request != null ? c.Request.ToLower().Contains(reqType.ToLower()) : c.Request != null).ToList();
            }

            var jsonData2 = new
            {
                data = lstserviceReq.Select(item => new
                {
                    ID = item.ID.ToString() != "" ? item.ID.ToString() : "",
                    RequestNo = item.Request_x0020_No.ToString() != "" ? item.Request_x0020_No.ToString() : "",
                    RequestedBy = item.Requested_x0020_By.ToString() != "" ? item.Requested_x0020_By.ToString() : "",
                    Title = item.Service_x0020_Title.ToString() != "" ? item.Service_x0020_Title.ToString() : "",
                    AssignedTo = item.Request_x0020_Assigned_x0020_To != "" ? item.Request_x0020_Assigned_x0020_To.ToString() : "",
                    RequestedService = item.Service_x0020_Category.ToString() != "" ? item.Service_x0020_Category.ToString() + "-" + item.Requested_x0020_Service.ToString() : "",
                    RequestStatus = item.Task_x0020_Status.ToString() != "" ? item.Task_x0020_Status.ToString() : "",
                    RequestDate = item.Requested_x0020_Date.ToString() != "" ? item.Requested_x0020_Date.ToString("dd/MM/yyyy") : "",
                    KPI = item.KPI.ToString() != "" && item.KPI != 999999 ? item.KPI.ToString() : "",
                    StartDate = item.Start_x0020_Date != null && item.Start_x0020_Date.ToString() != "" && item.Start_x0020_Date.ToString("dd/MM/yyyy") != "01/01/0001" ? item.Start_x0020_Date.ToString("dd/MM/yyyy hh:mm:ss tt", CultureInfo.InvariantCulture) : "",
                    ExpectedEndDate = item.Expected_x0020_End_x0020_Date != null ? item.Expected_x0020_End_x0020_Date.ToString("dd/MM/yyyy") : "",
                    EndDate = (item.Task_x0020_Status == "Completed" ? item.End_x0020_Date.ToString("dd/MM/yyyy hh:mm:ss tt", CultureInfo.InvariantCulture) : ""),
                    Conformity = (item.Task_x0020_Status == "Completed" ? (IsExceededKPI(item.End_x0020_Date, item.Expected_x0020_End_x0020_Date)).ToString() : "N/A"),
                    TCDays = (item.Task_x0020_Status == "Completed" ? (ExceededKPIDays(item.End_x0020_Date, item.Expected_x0020_End_x0020_Date)).ToString() : ""),
                    Solution = item.Notes.ToString() != "" ? item.Notes.ToString() : "",
                })
            };

            return Newtonsoft.Json.JsonConvert.SerializeObject(jsonData2);
        }
        catch (Exception ex)
        {
            return Newtonsoft.Json.JsonConvert.SerializeObject(ex);
        }
        //return list as Json    
        //  return Newtonsoft.Json.JsonConvert.SerializeObject(ObjEmp);
    }

colin

Hi @nidhincee@gmail.com ,

That's a lot of code here. Are you getting any errors on the DataTables end? Is it the backend not create the 300 rows? Have you debugged to see where the problems could lie? What have you tried to far?

Cheers,

Colin