I'm using a jQuery DataTable to display data taken from a database using a stored procedure and web service. I can run the SP or service using Fiddler just fine, but when it comes to populating the DataTable I get the error documented here. In my specific case the message is:
"DataTables warning: table id=tblCashRecord - Requested unknown parameter '0' for row 0, column 0"
What happens then is that my DataTable shows the correct number of rows, but all of the cells are empty.
I'm pretty sure the number of columns in the HTML table are the same as the number of columns I'm pushing to using aoColumns (it's four), but I could be wrong. I know there are plenty of the same question being asked, but this is the only one I found useful that might be relevant, and I've also tried this without success.
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, //remove later in SP
"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 included only the first one 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 have edited the GUIDs in the response above to remove sensitive information.
