Dynamic datatable problem. Please help!

joed171

Hello all,

I recently gave datatables a shot at handling my coldfusion queries. I got the code to work after some fixes to the exampl you have on this site. The problem I am having now is that my code is returning a bad json formatting when I add a date field from coldfusion. I will post the back end code below. as well as the json error. I can see what the error is I just can figure out how to handle it in the output section of the coldfusion page. Also all my values are getting turned into links I would only like the client id to link to somewhere else. Thanks in advance for any help you may provide.

cfdata5_back.cfm

[code]

<!--- table name --->


<!--- list of database columns which should be read and sent back to DataTables --->


<!--- Indexed column --->



<!--- ColdFusion Datasource for the MySQL connection --->


<!---
If you just want to use the basic configuration for DataTables with ColdFusion server-side, there is no need to edit below this line

Note: there is additional configuration below for the "version" column in query and output
--->

<!---
ColdFusion Specific Note: I handle Paging, Filtering and Ordering a bit different than some of the other server side versions
--->

<!---
Paging
--->
<!---
ColdFusion Specific Note: I am handling paging in the cfoutput statement instead of limit.
--->



<!---
Filtering
NOTE: this does not match the built-in DataTables filtering which does it
word by word on any field. It's possible to do here, but concerned about efficiency
on very large tables, and MySQL's regex functionality is very limited
--->

<!--- ColdFusion Specific Note: I am handling this in the actual query call, because i want the statement parameterized to avoid possible sql injection --->


<!--- Ordering --->


<!--- SQL queriesGet data to display --->

<!--- Data set after filtering --->

SELECT #listColumns#
FROM #sTableName#

WHERE OR #thisColumn# LIKE <!--- special case --->


ORDER BY , #listGetAt(listColumns,(url["iSortCol_"&thisS]+1))# #url["sSortDir_"&thisS]#



<!--- Total data set length --->

SELECT COUNT(#sIndexColumn#) as total
FROM #sTableName#


<!--- Output --->

{"sEcho": #val(url.sEcho)#,
"iTotalRecords": #qCount.total#,
"iTotalDisplayRecords": #qFiltered.recordCount#,
"aaData": [

,
[,"-""#jsStringFormat(version)#""#jsStringFormat('' & qFiltered[thisColumn][qFiltered.currentRow] & '')#"
]
] }

[/code]

The json formatting error

Parse error on line 9:
...Roman", "{ts \'1965-05-05 00
----------------------^
Expecting 'STRING', 'NUMBER', 'NULL', 'TRUE', 'FALSE', '{', '['

{
"sEcho": 1,
"iTotalRecords": 46742,
"iTotalDisplayRecords": 46742,
"aaData": [
[
"26860",
"joe",
"smith",
"{ts \'1965-05-05 00:00:00\'}"
],
[
"26861",
"john",
"smith",
"{ts \'1964-08-17 00:00:00\'}"
]

]
}

joed171

Hello everyone,

I made a ton of progress on this. I will report my code now with a few formatting questions. I think I have all of the coldfusion stuff taken care of (except one small thing). Here are a few formatting question stuff...

1. From the look of things this script sets the default sort based on the first column and asc by default. Where would I change to desc. Im assuming you add a property to the client side.

2. How do I mask the dates in the output. Currently its handling the datetime yyy-mm-dd 00:00:00:0. I would like mm/dd/yyyy with no time. I tried to add a coldfusion dateformat() function but caused formatting problems. Im probably doing the javascript stuff wrong.

3. Pretty important how do i add custom where cluase stuff to the initial query of data. I know this isnt a coldfusion forum but in general I cant see where to tell the query to say something like where year = 2013.

I think if we figure these questions out this coldfusion solution will be ready to really use. I appreciate any help you guys provide. See below for the working (so far) scripts...

client page

[code]







DataTables example





$(document).ready(function() {
$('#dyntable').dataTable( {
"bProcessing": true,
"bServerSide": true,
"sPaginationType": "full_numbers",
"sAjaxSource": "cfdata5_back.cfm",

} );
} );








#currentYear# Activity



















Client ID
FIRST NAME
LAST NAME
DOB
Specimen
Test Date
Result Date
Test Type
Result
Voucher
Case
Referral
Location







Loading data from server






[/code]

backend

[code]


<!--- table name --->


<!--- list of database columns which should be read and sent back to DataTables --->


<!--- Indexed column --->


<!--- ColdFusion Datasource for the MySQL connection --->


<!---
If you just want to use the basic configuration for DataTables with ColdFusion server-side, there is no need to edit below this line

Note: there is additional configuration below for the "version" column in query and output
--->

<!---
ColdFusion Specific Note: I handle Paging, Filtering and Ordering a bit different than some of the other server side versions
--->

<!---
Paging
--->
<!---
ColdFusion Specific Note: I am handling paging in the cfoutput statement instead of limit.
--->



<!---
Filtering
NOTE: this does not match the built-in DataTables filtering which does it
word by word on any field. It's possible to do here, but concerned about efficiency
on very large tables, and MySQL's regex functionality is very limited
--->

<!--- ColdFusion Specific Note: I am handling this in the actual query call, because i want the statement parameterized to avoid possible sql injection --->


<!--- Ordering --->


<!--- SQL queriesGet data to display --->

<!--- Data set after filtering --->

SELECT #listColumns#
FROM #sTableName#

WHERE OR #thisColumn# LIKE <!--- special case --->


ORDER BY , #listGetAt(listColumns,(url["iSortCol_"&thisS]+1))# #url["sSortDir_"&thisS]#



<!--- Total data set length --->

SELECT COUNT(#sIndexColumn#) as total
FROM #sTableName#


<!--- Output --->

{"sEcho": #val(url.sEcho)#,
"iTotalRecords": #qCount.total#,
"iTotalDisplayRecords": #qFiltered.recordCount#,
"aaData": [

,
[,"-""#jsStringFormat(version)#""#jsStringFormat('' & qFiltered[thisColumn][qFiltered.currentRow] & '')#""#jsStringFormat('' & qFiltered[thisColumn][qFiltered.currentRow] & '')#""#jsStringFormat(qFiltered[thisColumn][qFiltered.currentRow])#"
]
] }


[/code]