Page not changing in datatable

greennn

I am trying to implement server side rendering using my datatable. When the page first loads it works just fine

let tblAssignedJobs = $('#tblAssignedJobs').DataTable({
    "oLanguage": {
        "sEmptyTable": "There are no jobs assigned to you at this moment"
    },
    serverSide:true,
    processing: true,
    paging     : true
    ,pageLength : 25
    ,"aoColumns": [{
        "mData":"StudentNumber"
      },{
        "mData": "JobType"
      },{
        "mData": "BranchName"
      }
      ,
      {
        "mData":"LocationNumber"
      }
      ,
      {
        "mData":"Address"
      },
      { "mData":"StudentNumber",
        "mRender": function(data, type, full) {

            return `<a href = '/editJobs?jobNumber=${data}'><button class='btn btn-primary'>Edit Job</button></a>`
        }  
      }
    ],
    ajax: {
        url: "assignedJobs",
        dataSrc: "data",
        type: "POST"
    },

    responsive: true
});

Here is my backend url:

router.post("/assignedJobs", middleware.isLoggedIn, async function(req, res) {
    try {
        rows = await pool
        console.log(req.body.dataSrc)
        // let result = await rows.request()
        //     .input('customerID', sql.Int, 12)
        //     .execute('usp_web_getAssignedJobInfoBasedOnCustomer')

        // console.log(result.recordset.length)    
    let offset 

    let result = await rows.query(`
    Select .....
    order by StdeuntNumber
    OFFSET ${req.body.start} ROWS
    FETCH NEXT ${req.body.length} ROWS ONLY;`) 

       myJSON = { 
        "draw": req.body.draw,
        "start":req.body.start||25,
        "length":req.body.length||25,
        "recordsTotal": 5000,
        "recordsFiltered": 100,
        "sEcho":25,
        "data": result.recordset
        }
        res.send(myJSON)

    } catch (err) {
        console.log(err)
    }
})

But as you can see

when I press on new page, the page does not change even though I get a post request on my backend. Also my page data does not change even when I am sending new data from the backend

kthorngren

Try removing dataSrc: "data", in line 33. This is not needed for server side processing as your rows data is expected to be in the data object per the Server Side Process docs.

I would use the browser's network inspector tool to see the XHR request and response. The SSP link above describes the expected response. Looks like you have some extra data.

Kevin

greennn

@kthorngren I removed the dataSrc:"data" and now I get a "processing" icon in the middle of my datatable when I try to change my page.

I also looked at the browsers XHR request and it looks something like this:

I do not believe that my data format is the problem. If that were the case my table would not have loaded when I load my page. When I change my page the same route fires but my page does not change.

Are we supposed to handle page change ourselves?

kthorngren

You posted the XHR response not request. Anyway you do have some extra (start, length, sEcho) parameters that Datatables does not specify in the response. Not that this will cause a problem, just pointing it out. The key is the draw parameter. In the docs I link to it specifies this is expected to be an integer. Datatables may convert this for you, not sure, but you are returning a string. The draw value you return is expected to match the value sent. Its a sequence number. For the above screenshot was the draw value sent 26?

When changing the page please post both the XHR request parameters and response. The request parameters can be found in the Headers tab at the bottom - if using Chrome.

Kevin

greennn

My bad I was trying to say response header. After your feedbakc I changed my server side code to

    myJSON = { 
        "draw": parseInt(req.body.draw),
        "start":req.body.start||25,
        "length":req.body.length||25,
        "recordsTotal": 5000,
        "recordsFiltered": 100,
        "sEcho":25,
        "data": result.recordset
        }

So that it is changing it to an integer. My request header when I change to page 2 looks like this:

And now after changing the repose draw to integer, the response looks like this:

However, I am still facing the same issue where I just get the processing icon

greennn

Just removed sEcho from the response and now its working! Not sure why I added it there in the first place. I must have found it somewhere. Thank you for pointing that out.

kthorngren

Not sure but I think sEcho is from the 1.9.x Datatables and is similar to the draw parameter. So it may have conflicted with the draw parameter.

Kevin