Server-Side sorting : Incorrect syntax near 'OFFSET'. Invalid usage of the option NEXT in the FETCH.

sarooptrivedi

Reference : https://datatables.net/forums/discussion/comment/131847
Debugger code (debug.datatables.net):
Incorrect syntax near 'OFFSET'. Invalid usage of the option NEXT in the FETCH statement.:

      var table = $("#tblArea").DataTable({
          filter: true, // this is for disable filter (search box)
          orderMulti: false, // for disable multiple column ordering at once             
          "ajax": {
              "url": "/Area/LoadData/",
              "type": "POST",
              "ContentType": "application/json",
              headers: {
                  RequestVerificationToken: $("#RequestVerificationToken").val()
              },
              "datatype": "json",
              "dataSrc": function (result) {
                  totalrecord = result.recordsFiltered;

                  return result.data;
              },
              failure: function (response) {

                  alert(response.responseJSON);
              },
              error: function (response) {

                  alert(response.responseJSON);
              }
          },
          columns: [
              { data: 'AId', "name": "AId", "autoWidth": true },
              { data: 'Name', "name": "Name", "autoWidth": true },
              { data: 'Description', "name": "Description", "autoWidth": true },
              { data: 'Inactive', "name": "Inactive", "autoWidth": true },
              { data: 'FId', "name": "FId", "autoWidth": true },
              { data: 'Facility', "name": "Facility", "autoWidth": true },         
          ],       
          "order": [[0, 'asc']],
          "scrollY": "50vh",
          "scrollX": true,
          colReorder: false,
          search: true,
          processing: true,
          serverSide: true,
          select: 'single',
          stateSave: true,
          language: {
              "paginate": {
                  "first": "First",
                  "last": "Last",
                  "next": "Next",
                  "previous": "Previous"
              },
          },
          layout: {
              topStart: ['buttons', 'pageLength'],           
              topEnd: {             
                  search: {
                      placeholder: 'Type search here'
                  }
              },
              bottomEnd: {
                  paging: {
                      type: 'full_numbers',
                      boundaryNumbers: false
                  }
              }
          },
          buttons: [
              {

                  text: '<i class="fas fa-plus-square" style="Color:#184D14;Font-size:14px;"></i> Add',
                  titleAttr: 'Create',
                  action: function () {
                      OpenAddPopup();

                  }

              },
              {
                  extend: 'csv',
                  titleAttr: 'csv',
                  text: '<i class="fas fa-file-csv" style="color:#006400;Font-size:14px;"></i>CSV Export',
                  autoFilter: true,
                  title: 'Area Data export',
                  filename: function () {
                      var d = new Date();
                      var date = d.getFullYear().toString() + "-" + d.getMonth().toString() + "-" + d.getDate().toString();
                      var n = d.getHours().toString() + d.getMinutes().toString() + d.getSeconds().toString();

                      return 'Area ' + date.toString() + "-" + n.toString();
                  },
                  charset: 'utf-8',
                  bom: true,
                  exportOptions: {
                      columns: ':visible',
                  },
                  action: newexportaction
              }
              ,
          ],
          lengthMenu: [
              [25, 50, 100, -1],
              [25, 50, 100, 'All'],
          ],
  });

Description of problem:
With new datatables.net library I got the error while I sort the columns three time. This problem not encountered previously. I use the SQL server 2017 . Ajax call has POST...

allan

Thanks for letting me know about this. That's a bug I'm afraid. I'll look into it tomorrow, but as a workaround, disable the third "no sort" state when clicking on the table header by doing:

DataTable.defaults.column.orderSequence = ['asc', 'desc'];

Allan

sarooptrivedi

Hey Allan,

Thank you for quick response. Work around worked.

 columns: [
     { data: 'AId', "name": "AId", "autoWidth": true, orderSequence: ['desc', 'asc'] },
     { data: 'Name', "name": "Name", "autoWidth": true, orderSequence: ['desc', 'asc'] },
     { data: 'Description', "name": "Description", "autoWidth": true, orderSequence: ['desc', 'asc'] },
     { data: 'Inactive', "name": "Inactive", "autoWidth": true, orderSequence: ['desc', 'asc'] },
     { data: 'FId', "name": "FId", "autoWidth": true, orderSequence: ['desc', 'asc'] },
     { data: 'Facility', "name": "Facility", "autoWidth": true, orderSequence: ['desc', 'asc'] },         
 ],  

allan

I've committed changes to the .NET, Node.js and PHP libraries for Editor to address this - this is the one for .NET.

It only happens with SQL Server as far as I can tell, but paging without an ordering property makes no sense, so I think this is the correct thing to do for all.

Thanks again for flagging it up.

Allan