SQL Server AJAX Request - table not rendering rows

pcal90

Hi there,

I am trying to build a table to show stock levels for approximatley 29000 stock items in a warehouse and I can see the data returning in Chrome Developer Tools network response but not seeing it returned in the Datatable.

The HTML for the table is here -

<table id="myTable" class="table table-striped display dt-responsive">
            <h2 class="text-center mt-2">Internal Sales - Stock</h2>
            <thead>
                <tr class="text-center">
                    <th>ItemID</th>                 
                    <th>Item Code</th>
                    <th>Name</th>
                    <th>SageLongDesc</th>   
                    <th>StockStatus</th>
                    <th title="Allow On Sales Order">AllowOnSO</th>
                    <th>StockingStatus</th>
                    <th>BoxQty</th>
                    <th>MainWHTotalQty</th>
                    <th>MainWHFreeStock</th>
                    <th title="Quantity On Live Purchase Order">QtyOnLivePO</th>
                    <th title="Expected Next Delivery Date">ExpNDD</th>
                    <th>SupplierAC</th>
                    <th>SupplierStockCode</th>                                                  
                </tr>
            </thead>
</table>

The JavaScript is here -

$('#myTable').DataTable({
        dom: 'B<"clear">lfrtip',
        "buttons": [{               
               "extend": 'excel',
               "text": '<i class="fa fa-file-excel-o" style="color: green;">Export To Excel</i>',
               "titleAttr": 'Excel',                               
               "action": newExportAction
            }       
            ],
        "processing": true,
        "serverSide": true,
        "ajax": {
            "url": "includes/data.inc.php",
            "type": "GET"
        },
        "columns": [
            { "data": "ItemID", "searchable": true },
            { "data": "ItemCode", "searchable": true },
            { "data": "ItemName", "searchable": true },
            { "data": "SageLongDesc", "searchable": true },
            { "data": "StockStatus", "searchable": true },
            { "data": "AllowOnSalesOrder" },
            { "data": "StockingStatus" },
            { "data": "BoxQty" },
            { "data": "MainWarehousesTotalQty" },
            { "data": "MainWarehousesFreeStock" },
            { "data": "QtyOnLivePO" },
            { "data": "ExpectedNextDeliveryDate" },
            { "data": "SupplierAC" },
            { "data": "SupplierStockCode" }
        ],
        "order": [[ 0, "asc" ]],
        "paging": true,
        "lengthChange": true,
        "lengthMenu": [[50, 100, 250, 500, 1000], [50, 100, 250, 500, 1000]],
        "searching": true,
        "ordering": true,
        "info": true,
        "autoWidth": true,
        "responsive": true
    });

The includes/data.inc.php file is here (with added debugging statements in an attempt to figure out where I am going wrong) -

<?php

error_reporting(E_ALL);
ini_set('display_errors', '1');

$serverName = "SQLServer1";
$connectionInfo = array( "UID"=>"USER",
    "PWD"=>"PASSWORD",
    "Database"=>"TheCorrectOne",
    "CharacterSet" => "UTF-8");
$conn = sqlsrv_connect($serverName, $connectionInfo);
if( $conn === false) {
    die( print_r( sqlsrv_errors(), true));
}

// Set default values for start and length
$start = isset($_GET['start']) ? $_GET['start'] : 0;
$length = isset($_GET['length']) ? $_GET['length'] : 50;


// Set column names and ordering
$columns = array('ItemID', 'ItemCode', 'ItemName', 'SageLongDesc', 'StockStatus', 'AllowOnSalesOrder', 'StockingStatus', 'BoxQty', 'MainWarehousesTotalQty', 'MainWarehousesFreeStock', 'QtyOnLivePO', 'ExpectedNextDeliveryDate', 'SupplierAC', 'SupplierStockCode');
$orderBy = isset($_GET['order'][0]['column']) ? $columns[$_GET['order'][0]['column']] : 'ItemName';
$orderDir = isset($_GET['order'][0]['dir']) ? $_GET['order'][0]['dir'] : 'ASC';

// Debugging
echo "Debug: Order By: $orderBy, Order Dir: $orderDir, Start: $start, Length: $length<br>";

// Build the SQL query
$query = "SELECT [ItemID], [ItemCode], [ItemName], [SageLongDesc], [StockStatus], [AllowOnSalesOrder], [StockingStatus], [BoxQty], [MainWarehousesTotalQty], [MainWarehousesFreeStock], [QtyOnLivePO], [ExpectedNextDeliveryDate], [SupplierAC], [SupplierStockCode] FROM [StockTable] ORDER BY $orderBy $orderDir OFFSET $start ROWS FETCH NEXT $length ROWS ONLY";

// Debugging
echo "Debug: Query: $query<br>";

// execute the query
$result = sqlsrv_query($conn, $query);
if ($result === false) {
    die(print_r(sqlsrv_errors(), true));
}

// count the total number of rows in the table
$countQuery = "SELECT COUNT(*) AS count FROM StockTable";
$countResult = sqlsrv_query($conn, $countQuery);
$countRow = sqlsrv_fetch_array($countResult);
$totalCount = $countRow['count'];

// Get the search term entered by the user
$searchTerm = isset($_GET['search']['value']) ? $_GET['search']['value'] : '';

// Build the WHERE clause for the search term
$where = "";
if (!empty($searchTerm)) {
    $where = "WHERE ItemCode LIKE '%$searchTerm%' OR ItemName LIKE '%$searchTerm%' OR SageLongDesc LIKE '%$searchTerm%' OR StockStatus LIKE '%$searchTerm%'";
}

// Debugging
echo "Debug: Search Term: $searchTerm, Where: $where<br>";

// Build the SQL query with the WHERE clause
$query = "SELECT [ItemID], [ItemCode], [ItemName], [SageLongDesc], [StockStatus], [AllowOnSalesOrder], [StockingStatus], [BoxQty], [MainWarehousesTotalQty], [MainWarehousesFreeStock], [QtyOnLivePO], [ExpectedNextDeliveryDate], [SupplierAC], [SupplierStockCode] FROM [StockTable] $where ORDER BY $orderBy $orderDir OFFSET $start ROWS FETCH NEXT $length ROWS ONLY";

// Debugging
echo "Debug: Query with Where: $query<br>";

// Execute the query
$result = sqlsrv_query($conn, $query);

// Build the count query with the WHERE clause
$countQuery = "SELECT COUNT(*) AS count FROM StockTable $where";
$countResult = sqlsrv_query($conn, $countQuery);
$countRow = sqlsrv_fetch_array($countResult);
$totalCount = $countRow['count'];



// Build the JSON response
$response = array(
    "draw" => isset($_GET['draw']) ? intval($_GET['draw']) : 1,
    "recordsTotal" => $totalCount,
    "recordsFiltered" => $totalCount,
    "data" => array()
);


// Debugging
echo "Debug: Total Count: $totalCount<br>";

// Iterate over the results and add them to the response
while ($row = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)) {
    $response['data'][] = $row;
}

// send the JSON response
try {
    $encodedResponse = json_encode($response);
    if ($encodedResponse === false) {
        throw new Exception(json_last_error_msg());
    }
    echo $encodedResponse;
} catch (Exception $e) {
    echo 'JSON Encoding Error: ' . $e->getMessage();
}

// Close the database connection
sqlsrv_close($conn);

?>

In terms of errors, I am only seeing the TN1 error but here is a screenshot of the response from the Chrome Developer Tools

I have an identical table working with data sourced from a similar table from the same SQL database so I am unsure why this one is not rendering.

I am not using the SSP file as I have never managed to get passed the "target machine actively refused connection" message.

Any advice would be greatly appreciated!

colin

That's an odd layout in the screenshot. Is that from the network tab? For example, when I force a draw in this example, I'm seeing this kind of response:

Colin

pcal90

Hi Colin,

Below is the screenshot of the preview section of the network response.

I uploaded the debugger configuration data and the ref code was emavom - if that helps?

kthorngren

You mentioned you are getting the 1. Warning: Invalid JSON response error. Did you follow the troubleshooting steps at the link in the error?
https://datatables.net/manual/tech-notes/1

What does JSON Lint report about the JSON response? It seems like the Preview is showing a string but its hard to tell without seeing the beginning. Is there a single quote?

Kevin