Microsoft Sql Server & php Ajax warning: table id=DataTables_Table_0
Microsoft Sql Server & php Ajax warning: table id=DataTables_Table_0
hanifeoglu
Posts: 4Questions: 1Answers: 0
Hi all , i try 2 last 2 day implement to datatable to sqlsrv but I failed
just fisrt
big problem all sample code for mysql but i m need to mssql server sql server not used limit sql server 2012 after have but my sql server 2008 r2 i try to this sample code return to error if have best working on to for sql server sample code i want to try
have Server Side Processing sample code i get it connection error 
**DataTables warning: table id=DataTables_Table_0 - Ajax error. For more information about this error, please see http://datatables.net/tn/7**
# DataTable.php
<?php
class DataTable {
var $database = "test";
var $host = "127.0.0.1";
var $user = "usertest";
var $pass = "secret";
var $conn;
var $numRows;
var $baseTest = "test.dbo.";
public function __construct() {
$this->connect($this->host, $this->user, $this->pass, $this->database);
}
public function connect($_host, $_user, $_password, $_database) {
$this->conn = mssql_connect($_host, $_user, $_password) or die(mssql_get_last_message());
mssql_select_db($this->database, $this->conn) or die(mssql_get_last_message());
}
function query($sql) {
mssql_query('SET ANSI_WARNINGS ON', $this->conn) or die(mssql_get_last_message());
mssql_query('SET ANSI_NULLS ON', $this->conn) or die(mssql_get_last_message());
$resultQuery = mssql_query($sql, $this->conn) or die(mssql_get_last_message());
$results = array();
while ($row = mssql_fetch_array($resultQuery))
$results[] = $row;
return $results;
}
function numRows($sql) {
$resultQuery = mssql_query($sql, $this->conn) or die(mssql_get_last_message());
$result = mssql_num_rows($resultQuery);
return $result;
}
public function getData($params, $table, $columns) {
$where = $sqlTot = $sqlRec = '';
if( !empty($params['search']['value']) ) {
$where = " WHERE";
$i = 0;
foreach($columns as $c) {
if($i === 0) {
$where .= (count($columns) === 1) ? " (" . $c . " LIKE '%" . $params['search']['value'] . "%' "
: " ( " . $c . " LIKE '%" . $params['search']['value'] . "%' ";
} else {
$where .=" OR " . $c . " LIKE '%" . $params['search']['value'] . "%' ";
}
$i++;
}
$where .= " )";
}
$sql = "SELECT * FROM " . $this->baseTest . "[" . $tabla . "]";
//Total de registros en la tabla
$totalRecords = $this->numRows(utf8_decode($sql));
$sqlTot .= $sql;
$sqlRec = "SELECT TOP " . $params['length'] . " * FROM( SELECT *, ROW_NUMBER() over (ORDER BY "
. $columns[$params['order'][0]['column']] . " "
. $params['order'][0]['dir'] . " ) as ct FROM "
. $this->baseTest . "[" . $table . "]"
. " " . $where . " ) sub WHERE ct > " . $params['start'];
if(isset($where) && $where != '') {
$sqlTot .= $where;
}
//Registros a mostrar en la tabla
$dataRecords = $this->query(utf8_decode($sqlRec));
//Total registros después del filtro
$totalRecordsFiltered = $this->numRows(utf8_decode($sqlTot));
$data = array(
"draw" => intval($params['draw']),
"recordsTotal" => intval($totalRecords),
"recordsFiltered" => intval($totalRecordsFiltered),
"data" => $dataRecords
);
return $data;
}
}
## table.html
<html>
<head>
<title>DataTables con SQL Server 2008 R2</title>
<link href="https://cdn.datatables.net/1.10.13/css/jquery.dataTables.min.css" rel="stylesheet">
</head>
<body>
<table class="datatable">
<thead>
<tr>
<th>ID</th>
<th>NOMBRE</th>
</tr>
</thead>
<tbody>
<tr>
<td></td>
<td></td>
</tr>
</tbody>
</table>
<script src="https://code.jquery.com/jquery-2.2.4.min.js" integrity="sha256-BbhdlvQf/xTY9gja0Dq3HiwQF8LaCRTXxZKRutelT44=" crossorigin="anonymous"></script>
<script type="text/javascript" src="https://cdn.datatables.net/1.10.13/js/jquery.dataTables.min.js"></script>
<script type="text/javascript" src="table.js"></script>
</body>
</html>
## table.js
<?php
require_once('DataTable.php');
$dataTable = new DataTable();
$table = 'test';
$columns = array(
0 => 'id',
1 => 'nombre'
);
$data = $dataTable->getData($_POST, $table, $columns);
echo json_encode($data);
This discussion has been closed.
Answers
You need to follow the link in the error message, which provides detailed diagnostic steps to be followed.
Thank you @tangerine,
yes i m looking network in i add all screenshot
Query String ParametersQuery String Parametersdraw: 1columns[0][data]: 0columns[0][name]columns[0][searchable]: truecolumns[0][orderable]: truecolumns[0][search][value]columns[0][search][regex]: falsecolumns[1][data]: 1columns[1][name]columns[1][searchable]: truecolumns[1][orderable]: truecolumns[1][search][value]columns[1][search][regex]: falseorder[0][column]: 0order[0][dir]: ascstart: 0length: 10search[value]search[regex]: false_: 1543216722384As @tangerine said, following the diagnostic steps in that error message is a good place to start. If not, we're happy to take a look, but as per the forum rules, please link to a test case - a test case that replicates the issue will ensure you'll get a quick and accurate response. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.
Cheers,
Colin
@colin thank you for for warning me, i m see test case link but not see any where ms sql server side pagination short and (server side live) test not possible you recommend link in if i m not see plase send me link i want test it same my code where to problem when easy for me i m solved if i understanding where is problem here all sample code js or mysql my system is different maybe you not get t my point maybe if you like read again my question @tangerine said me check it browser developer menu under network error i m learn how to look network traffic when i m share it here, and other maybe i don't know how to test or make it debug and other topics have my problem i m find solutions just i want this is and here more people look at here maybe anyone person see same my problem maybe help me? and last my words is this a place I was asked to ask because I asked the question why is this place forums right? wise person or expert person helps a person in need of help because forums ecosystem this is
I keep looking for a solution to my problem.
Hi i m learn new good trick i used DataTables debugger
tool send to here my config files link here
https://debug.datatables.net/ocihos
maybe clear understanding my problem
I'm still not clear if you've followed those steps we suggested before. You're getting a 500 error back, so the suggestion was to check the server logs.