I am using jQuery DataTable to display data taken from a database using a stored procedure and a web service. I can start the SP or service using Fiddler, just fine, but when it comes to populating the DataTable, I get a documented error here . In my particular case, the message is:
"DataTables warning: table id = tblCashRecord - requested unknown parameter '0' for row 0, column 0"
What happens, then my DataTable shows the correct number of rows, but all cells are empty.
I am sure that the number of columns in the HTML table is the same as the number of columns that I click on using aoColumns (these are four), but I could be wrong. I know that there are many questions that are asked, but this one is the only one that I found useful, which may be relevant, and I also tried.
My HTML table:
<table id="tblCashRecord" class="table table-bordered">
<thead>
<tr>
<th>Kiosk Name</th>
<th>Service Type</th>
<th>Transaction Timestamp</th>
<th>Amount (RM)</th>
</tr>
</thead>
<tbody>
</tbody>
<tfoot>
<tr>
<th colspan="3" style="text-align: right">Total:</th>
<th><span id="totalAmount" style="margin-left: -8px;"></span></th>
</tr>
</tfoot>
</table>
My Javascript:
$.ajax({
type: "POST",
url: "../Services/Report.svc/GetCashPaymentRecord/?s=" + session + "&r=" + reference,
data: "{\"kioskID\":" + JSON.stringify(kioskID) + "," + "\"startDate\": " + JSON.stringify(startDate) + "," + "\"endDate\":" + JSON.stringify(endDate) + "}",
contentType: "application/json; charset=utf-8",
dataType: "json",
success: function (response) {
if (response.success == true) {
if (response.cashPayment != null && response.cashPayment.length > 0) {
cashList = response.cashPayment;
var data = { "aaData": [] };
$('#tblCashRecord').dataTable().fnClearTable();
$('#tblCashRecord').dataTable().fnDestroy();
$.each(response.cashPayment, function (item) {
data.aaData.push({
"id": item.id,
"kioskName": item.kioskName,
"total": item.total,
"transactionTimestamp": moment.utc(item.transactionTimestamp).format("DD-MM-YY HH:mm:ss"),
"serviceType": item.serviceType,
"paymentType": item.paymentType,
"paymentRecordID": item.paymentRecordID
});
});
table = $('#tblCashRecord').DataTable({
"paging": false,
"ordering": false,
"bAutoWidth": false,
"bSortable": false,
"bFilter": false,
"bInfo": false,
dom: 'Blfrtip',
"aaData": data.aaData,
"aaColumns": [
{ "mData": "kioskName" },
{ "mData": "serviceType" },
{ "mData": "transactionTimestamp" },
{ "mData": "total" }
]
});
$('#tblCashRecord tbody tr').each(function () {
var col = $(this).find('td:eq(3)').html();
total += parseFloat(col);
});
$('#totalAmount').html(total.toFixed(2));
$('.sorting_asc').removeClass('sorting_asc');
}
else {
$('#tblCashRecord').dataTable().fnClearTable();
$('#tblCashRecord').dataTable().fnDestroy();
$('#tblCashRecord').dataTable({
"paging": false,
"ordering": false,
"bAutoWidth": false,
"bSortable": false,
"bFilter": false,
"bInfo": false,
"oLanguage": {
"sEmptyTable": "No Record Found."
}
});
$('#totalAmount').html("");
$('.sorting_asc').removeClass('sorting_asc');
}
}
else {
$('#tblCashRecord').dataTable().fnClearTable();
$('#tblCashRecord').dataTable().fnDestroy();
$('#tblCashRecord').dataTable({
"paging": false,
"ordering": false,
"bAutoWidth": false,
"bSortable": false,
"bFilter": false,
"bInfo": false,
"oLanguage": {
"sEmptyTable": "Error: Could not load data."
}
});
$('#totalAmount').html("");
$('.sorting_asc').removeClass('sorting_asc');
}
}
});
My web service:
cashPaymentResponse IReport.GetCashPaymentRecord(string session, string reference, cashPaymentRequest request)
{
Guid sessionID, referenceID, kioskID;
Guid.TryParse(session, out sessionID);
Guid.TryParse(reference, out referenceID);
Guid.TryParse(request.kioskID, out kioskID);
if (sessionID == Guid.Empty)
{
return new cashPaymentResponse("Invalid Session.");
}
DateTime startDate, endDate;
try
{
startDate = new DateTime(Convert.ToInt32(request.startDate.Substring(6, 4)), Convert.ToInt32(request.startDate.Substring(3, 2)), Convert.ToInt32(request.startDate.Substring(0, 2)), 0, 0, 0);
endDate = new DateTime(Convert.ToInt32(request.endDate.Substring(6, 4)), Convert.ToInt32(request.endDate.Substring(3, 2)), Convert.ToInt32(request.endDate.Substring(0, 2)), 23, 59, 59);
}
catch (Exception ex)
{
return new cashPaymentResponse("No Date Selected.");
}
List<ReportCashPaymentRecord_Result> result;
try
{
using (MyDBEntities context = new MyDBEntities())
{
result = context.ReportCashPaymentRecord(sessionID, kioskID, startDate, endDate).ToList();
}
}
catch (Exception ex)
{
if (isDebug() == false)
{
return new cashPaymentResponse("Database connection failed.");
}
else
{
return new cashPaymentResponse(ex.Message);
}
}
if (result.Count > 0)
{
cashPaymentResponse response = new cashPaymentResponse();
cashPaymentItem item;
response.cashPayment = new List<cashPaymentItem>();
for (int i = 0; i < result.Count; i++)
{
item = new cashPaymentItem();
if (result[i].kioskName == "session")
{
return new cashPaymentResponse("Invalid Session.");
}
else
{
item.id = (Guid)result[i].cashID;
item.paymentRecordID = (Guid)result[i].paymentRecordID;
item.total = (decimal)result[i].total;
item.transactionTimestamp = JsonConvert.SerializeObject(new DateTime(result[i].transactiontimestamp.Value.Year, result[i].transactiontimestamp.Value.Month, result[i].transactiontimestamp.Value.Day, result[i].transactiontimestamp.Value.Hour, result[i].transactiontimestamp.Value.Minute, result[i].transactiontimestamp.Value.Second, 0, DateTimeKind.Utc)).Replace("\"", "");
item.kioskName = result[i].kioskName;
item.serviceType = (result[i].serviceType.ToString() == "0") ? "Assessment" : (result[i].serviceType.ToString() == "1") ? "Water Bill" : (result[i].serviceType.ToString() == "2") ? "Rental" : (result[i].serviceType.ToString() == "3") ? "Compound" : "None";
item.paymentType = (result[i].paymentType.ToString() == "1") ? "Cash" : (result[i].paymentType.ToString() == "3") ? "Credit Card" : (result[i].paymentType.ToString() == "2") ? "Cheque" : "None";
response.cashPayment.Add(item);
}
}
return response;
}
else
{
return new cashPaymentResponse();
}
}
My JSON Response (there are actually 8 entries, but I only included the first to reduce clutter):
{
"success": true,
"cashPayment": [
{
"id": "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx",
"kioskName": "00001",
"paymentRecordID": "yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy",
"paymentType": "Cash",
"serviceType": "Water Bill",
"total": 100,
"transactionTimestamp": "2016-01-21T10:15:21Z"
}
]
}
EDIT: I edited the GUIDs in the answer above to remove sensitive information.