Answers

• galcott Posts: 53Questions: 15Answers: 1

  May 2017

  Are you converting the SQL Server recordset into JSON format? Here's some sample code that I use, in VBScript. Not very elegant but it works. In the for loop you need a line for each field in the recordset. My recordset has 6 fields so it's array elements 0 through 5.

  ' Embedded double quotes have to be preceded by a backslash to avoid JSON errors.
  Set rs = CreateObject("ADODB.Recordset")
  rs.Open "select herb_code, herb_name, cost, inventory, low_inv, replace(notes, char(34), '\' + char(34)) from herbs", ConnString, 0
  s = "{""data"":["     'the JSON has to begin with this
  Arr = rs.getRows()
  For i = 0 to UBound(Arr,2)
    s = s & "[" & """" & Arr(0,i) & ""","
    s = s & """" & Arr(1,i) & ""","
    s = s & """" & Arr(2,i) & ""","
    s = s & """" & Arr(3,i) & ""","
    s = s & """" & Arr(4,i) & ""","
    s = s & """" & Arr(5,i) & """" & "],"
  Next
  s = left(s,len(s)-1)  'knock off trailing comma
  s = s & "]}"
  rs.Close
  Response.Write s
  
• allan Posts: 63,471Questions: 1Answers: 10,467 Site admin

  May 2017

  Did you follow the instructions in the tech note the error message links to? If so, what is the server returning given that its not valid JSON?

  Allan
• Andreina95 Posts: 6Questions: 1Answers: 0

  June 2017

  hi, i already past that problem but now i can't filter and use the pagination of the datatable, you have any idea ? how cant i resolve this problem...

  this is my code:

  <?php
  /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
  * Easy set variables
  */
  
  /* Array of database columns which should be read and sent back to DataTables. Use a space where
  * you want to insert a non-database field (for example a counter or static image)
  */
  // add your columns here!!!
  $aColumns = array( 'ID', 'Name', 'Lastname', 'Start_Date', 'Card_Access', 'Department','Status' );
  
  /* MSSQL Database infomation */
  $serverName ='';
  $connectionInfo = array("Database"=>"", "UID"=>"", "PWD"=>"", "CharacterSet"=>"UTF-8");
  $conn = sqlsrv_connect($serverName,$connectionInfo);
  
  if($conn)
    {
     //echo "Connection Established";
    }
     else{
      echo "Connection could not be Established";
      die ( 'Can not connect to server' );
  }
  
  /* Indexed column (used for fast and accurate table cardinality) */
  $sIndexColumn = "ID";
  
  /* DB table to use */
  $sTable = "";
  
  /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
  * If you just want to use the basic configuration for DataTables with PHP server-side, there is
  * no need to edit below this line
  */
  
  /*
  * Local functions
  */
  function fatal_error ( $sErrorMessage = '' )
    {
     header( $_SERVER['SERVER_PROTOCOL'] .' 500 Internal Server Error' );
     die( $sErrorMessage );
    }
  
    /* Ordering */
        $sOrder = "";
        if ( isset( $_GET['iSortCol_0'] ) ) {
            $sOrder = "ORDER BY  ";
            for ( $i=0 ; $i<intval( $_GET['iSortingCols'] ) ; $i++ ) {
                if ( $_GET[ 'bSortable_'.intval($_GET['iSortCol_'.$i]) ] == "true" ) {
                    $sOrder .= $aColumns[ intval( $_GET['iSortCol_'.$i] ) ]."
                        ".addslashes( $_GET['sSortDir_'.$i] ) .", ";
                }
            }
            $sOrder = substr_replace( $sOrder, "", -2 );
            if ( $sOrder == "ORDER BY" ) {
                $sOrder = "";
            }
        }
  /* escape function */
  function mssql_escape($data) {
    if(is_numeric($data))
       return $data;
  $unpacked = unpack('H*hex', $data);
  return '0x' . $unpacked['hex'];
  }
  
  /* Filtering */
  $sWhere = "";
     if ( isset($_POST['search']['value']) && $_POST['search']['value'] != "" ) {
          $sWhere = "WHERE (";
     for ( $i=0 ; $i<count($aColumns) ; $i++ ) {
          $sWhere .= $aColumns[$i]." LIKE '%".addslashes( $_POST['search']['value'] )."%' OR ";
          }
  $sWhere = substr_replace( $sWhere, "", -3 );
  $sWhere .= ')';
  }
  
  /* Individual column filtering */
      for ( $i=0 ; $i<count($aColumns) ; $i++ ) {
          if ( isset($_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != '' )  {
              if ( $sWhere == "" ) {
                  $sWhere = "WHERE ";
              } else {
                  $sWhere .= " AND ";
              }
              $sWhere .= $aColumns[$i]." LIKE '%".addslashes($_GET['sSearch_'.$i])."%' ";
          }
      }
  
  
      /* Paging */
  
          $top = (isset($_GET['iDisplayStart']))?((int)$_GET['iDisplayStart']):0 ;
          $limit = (isset($_GET['iDisplayLength']))?((int)$_GET['iDisplayLength'] ):10;
          $sQuery = "SELECT TOP $limit ".implode(",",$aColumns)."
              FROM $sTable
              $sWhere ".(($sWhere=="")?" WHERE ":" AND ")." $sIndexColumn NOT IN
              (
                  SELECT $sIndexColumn FROM
                  (
                      SELECT TOP $top ".implode(",",$aColumns)."
                      FROM $sTable
                      $sWhere
                      $sOrder
                  )
                  as [virtTable]
              )
              $sOrder";
              $rResult = sqlsrv_query($conn, $sQuery);
  
              if($rResult === false){
               die(sqlsrv_errors(SQLSRV_ERR_ERRORS));
              }
  
  /* Data set length after filtering */
  $sQueryCnt = "SELECT * FROM $sTable $sWhere";
  $rResultCnt = sqlsrv_query($conn, $sQueryCnt );
  $iFilteredTotal = sqlsrv_num_rows( $rResultCnt );
  
  /* Total data set length */
  $sQuery = "
    SELECT COUNT(id)
    FROM $sTable
    ";
  $rResultTotal = sqlsrv_query($conn, $sQuery );
  $aResultTotal = sqlsrv_fetch_array($rResultTotal, SQLSRV_FETCH_NUMERIC);
  
  $iTotal = $aResultTotal[0];
  
  
  /* Output */
  $draw = (isset($_POST['draw']))?((int)$_POST['draw']):1 ;
  $output = array(
    "draw" => $draw,
    "recordsTotal" => $iTotal,
    "recordsFiltered" => $iFilteredTotal,
    "data" => array()
  );
  
  while ( $aRow = sqlsrv_fetch_array( $rResult, SQLSRV_FETCH_ASSOC) )
  {
   $row = array();
   for ( $i=0 ; $i<count($aColumns) ; $i++ )
   {
   /* General output */
   $row[$aColumns[$i]] = $aRow[ $aColumns[$i] ];
   }
   $row['Start_Date'] = $row['Start_Date']->format('Y-m-d');
   $output['data'][] = array_values($row);
  }
  
  echo json_encode( $output );
  
  
  <?php
  >
  ?>
  
  
  
• allan Posts: 63,471Questions: 1Answers: 10,467 Site admin

  June 2017

  The script you linked to is a legacy one (which is noted at the top of the page). Have you placed DataTables' server-side processing into legacy mode? Details for that are available here.

  Allan
• Andreina95 Posts: 6Questions: 1Answers: 0

  June 2017

  in my code (javascript) is that's how I have it

  $(document).ready(function() {
         var table = $('#Userzktime').DataTable( {
             "bJQueryUI": true,
             "sPaginationType": "full_numbers",
             "lengthMenu": [[10, 25, 50, -1], [10, 25, 50, "All"]],
  
             "processing": true,
                 "serverSide": true,
              "ajax": "php/Datatable_User_zktime.php",
  
               "columnDefs": [ {
                     "className": "dt-center", "targets": "_all",
                     "targets": -1,
                      "data": null,
                      "defaultContent":"<button id='edit'  class='fa fa-trash-o li'> </i> <span class='le'></span></a></button>"
                 } ]
        } );
  
       $('#Userzktime tbody').on( 'click', '#edit', function () {
       var data = table.row( $(this).parents('tr') ).data();
            var id=data[1];
               $('#id').val(id);
               alert(id);
            //  $('#confirm-disa').modal('show');
          });
    });
  
• allan Posts: 63,471Questions: 1Answers: 10,467 Site admin

  June 2017

  It doesn't look like you've enabled the legacy server-side processing. In which case it is sending parameters that your server-side script isn't expecting.

  You need to either:

  1. Update the server-side script to use the new parameters
  2. Use legacy parameters - as detailed in the link I gave above.

  Allan
• Andreina95 Posts: 6Questions: 1Answers: 0

  June 2017

  i don't understand , i see the example i don't see any difference with my code
• tangerine Posts: 3,365Questions: 39Answers: 395

  June 2017

  i see the example i don't see any difference with my code

  That is exactly why Allan advised you to update your server-side script to use the new parameters.
  Otherwise you have to follow Allan's alternative advice to use legacy parameters in your own code.
• Andreina95 Posts: 6Questions: 1Answers: 0

  June 2017

  Sorry, I am new in this, i always work with mysql, so far I test the datatable with sql and I don't know exactly how it works with sql server

  With mysql never gave me these kind of problems
• allan Posts: 63,471Questions: 1Answers: 10,467 Site admin

  June 2017

  Add this line:

  $.fn.dataTable.ext.legacy.ajax = true;
  

  before you initialise DataTables.

  That will cause DataTables to send the legacy parameters to the server (required for that script since it uses the legacy parameters).

  Allan
• promis Posts: 4Questions: 2Answers: 0

  June 2017

  good