SQL Server AJAX Request - table not rendering rows

SQL Server AJAX Request - table not rendering rows

pcal90pcal90 Posts: 10Questions: 2Answers: 0

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);


<?php
>
?>



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!

Replies

  • colincolin Posts: 15,226Questions: 1Answers: 2,593

    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

  • pcal90pcal90 Posts: 10Questions: 2Answers: 0

    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?

  • kthorngrenkthorngren Posts: 20,793Questions: 26Answers: 4,860

    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

Sign In or Register to comment.