DataTable Jquery Server Side With Adobe Cold Fusion and SQL Server
DataTable Jquery Server Side With Adobe Cold Fusion and SQL Server
Adam.jaclou
Posts: 2Questions: 0Answers: 0
-- AUTHOR ADAM JACLOU,
-- FOUNDER AND SENIOR PROGRAMMING ON OCTAPUSH JS https://github.com/octapush
-- FACEBOOK https://www.facebook.com/adam.lery.7
1. Step 1 create DB in your database Sql Server
2. Step 2 create Table In SQL server
https://www.facebook.com/adam.lery.7
CREATE TABLE [dbo].[TAccount](
[Account_Id] [int] IDENTITY(1,1) NOT NULL,
[Account_Name] varchar NULL,
[Account_Address1] varchar NULL
CONSTRAINT [PK_TAccount] PRIMARY KEY CLUSTERED
(
[Account_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
- Create page html and js in adobe cold fusion, I Have Created with name index.cfm
<!DOCTYPE html>
<html>
<head>
<title></title>
<link rel="stylesheet" type="text/css" href="misc/global/plugins/bootstrap/css/bootstrap.min.css">
<link rel="stylesheet" type="text/css" href="misc/global/plugins/datatables/css/dataTables.bootstrap.min.css">
<script type="text/javascript" src="misc/global/plugins/jquery.min.js"></script>
</head>
<body>
<div class="container">
<div class="row">
<div class="col-md-12">
<table class="table table-striped table-bordered table-hovered" id="displayData">
</table>
</div>
</div>
</div>
<script type="text/javascript">
$(function(){
var globalVars = {
tableConfigs: null,
tableId:$('table#displayData')
}
var main = {
register: function(){
main.UI.register.apply();
main.EVENTS.register.apply();
},
UI: {
register: function(){
main.UI.buildTable.apply();
},
buildTable: function(){
globalVars.tableConfigs = globalVars.tableId.dataTable( {
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": 'proses.cfc?method=dataTable',
columns:
[ { title:'ID',name:"Account_Id" },
{ title:'Account Name',name:"Account_Name" },
{ title:'Account Address',name:"Account_Address1" },
{
title: "Action",
orderable: false,
data: null,
class: "dt-head-center",
defaultContent: [
"<center>",
"<div class=\"btn-group\">",
"<button title=\"Select Detail Data\" type=\"button\" data-tag=\"pilih\" class=\"btn btn-info btn-sm\"> <i class='glyphicon glyphicon-ok'></i></button>",
"</div>",
"</center>"
].join(""),
width: "150px"
}
]
});
}
},
ROUTINES: {
register: function(){
main.ROUTINES.getSelectedRow.apply();
},
getSelectedRow: function(obj){
return {
index : $(obj).closest('tr').index(),
data: globalVars.tableId.dataTable().fnGetData($(obj).closest('tr').index())
}
}
},
EVENTS: {
register: function(){
main.EVENTS.eventButtonRow.apply();
},
eventButtonRow: function(){
globalVars.tableId
.on('preXhr.dt', function(e, setting, data) {
console.log(data);
})
.on('xhr.dt', function(e, setting, data) {
})
.on('draw.dt', function() {
main.EVENTS.gridBtnTable.apply();
});
},
gridBtnTable: function(){
var oBtn = $('button[data-tag="pilih"]');
oBtn.unbind().bind('click',function(){
var that = $(this).attr('data-tag');
if(that=='pilih'){
var data = main.ROUTINES.getSelectedRow($(this));
console.log(data); //GET POSITION ID DATA AND ALL DATA
console.log(data.data[1]); //SEPCIFIK DATA YOU CHICE
}
});
}
}
} //END MAIN
main.register.apply();
});
</script>
<script type="text/javascript" src="misc/global/plugins/bootstrap/js/bootstrap.min.js"></script>
<script type="text/javascript" src="misc/global/plugins/datatables/js/jquery.dataTables.min.js"></script>
<script type="text/javascript" src="misc/global/plugins/datatables/js/dataTables.bootstrap.min.js"></script>
</body>
</html>
- and for proccessing data i have created file cfc with name proses.cfc
<cfcomponent>
<cffunction name="dataTable" access="remote" format="json">
<cfset sTableName = "TAccount" />
<cfset listColumns = "Account_Id,Account_Name,Account_Address1" />
<cfset sIndexColumn = "Account_Id" />
<cfset coldfusionDatasource = "dbcserpdev1608"/>
<cfparam name="url.sEcho" default="1" type="integer" />
<cfparam name="url.iDisplayStart" default="0" type="integer" />
<cfparam name="url.iDisplayLength" default="10" type="integer" />
<cfparam name="url.sSearch" default="" type="string" />
<cfparam name="url.iSortingCols" default="0" type="integer" />
<!--- Data set after filtering --->
<cfquery datasource="#coldfusionDatasource#" name="qFiltered">
SELECT #listColumns#
FROM #sTableName#
<cfif len(trim(url.sSearch))>
WHERE <cfloop list="#listColumns#" index="thisColumn"><cfif thisColumn neq listFirst(listColumns)> OR </cfif>#thisColumn# LIKE <cfif thisColumn is "version"><!--- special case ---><cfqueryparam cfsqltype="CF_SQL_FLOAT" value="#val(url.sSearch)#" /><cfelse><cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="%#trim(url.sSearch)#%" /></cfif></cfloop>
</cfif>
<cfif url.iSortingCols gt 0>
ORDER BY <cfloop from="0" to="#url.iSortingCols-1#" index="thisS"><cfif thisS is not 0>, </cfif>#listGetAt(listColumns,(url["iSortCol_"&thisS]+1))# <cfif listFindNoCase("asc,desc",url["sSortDir_"&thisS]) gt 0>#url["sSortDir_"&thisS]#</cfif> </cfloop>
</cfif>
</cfquery>
<!--- Total data set length --->
<cfquery datasource="#coldfusionDatasource#" name="qCount">
SELECT COUNT(#sIndexColumn#) as total
FROM #sTableName#
</cfquery>
<!---
Output
--->
<cfcontent reset="Yes" />
{"sEcho": <cfoutput>#val(url.sEcho)#</cfoutput>,
"iTotalRecords": <cfoutput>#qCount.total#</cfoutput>,
"iTotalDisplayRecords": <cfoutput>#qFiltered.recordCount#</cfoutput>,
"aaData": [
<cfoutput query="qFiltered" startrow="#val(url.iDisplayStart+1)#" maxrows="#val(url.iDisplayLength)#">
<cfif currentRow gt (url.iDisplayStart+1)>,</cfif>
[<cfloop list="#listColumns#" index="thisColumn"><cfif thisColumn neq listFirst(listColumns)>,</cfif><cfif thisColumn is "version"><cfif version eq 0>"-"<cfelse>"#replacenocase(jsStringFormat(version),"\'","'","all")#"</cfif><cfelse>"#replacenocase(jsStringFormat(qFiltered[thisColumn][qFiltered.currentRow]),"\'","'","all")#"</cfif></cfloop>]
</cfoutput> ] }
</cffunction>
</cfcomponent>
This discussion has been closed.
Replies
Table
http://pastebin.com/e7gt1DHf
index.cfm
http://pastebin.com/dqUg8aXb
proses.cfc
http://pastebin.com/ckrJeC5s
you are throwing an error because you are calling a.cfc instead of proses.cfc
How to add error check in JS. Like "**DataTables warning: table id=displayData - Invalid JSON response. For more information about this error, please see http://datatables.net/tn/1**"
The above error occurred when session is expired and user want to click on data table result.
So i want to reload the page when this error occurred.
Please help me to fix this issue.
Thanks!
Hi @Subhan_raza ,
Is this related to the OP? If not, please post in a new thread, and either way as a first step, please follow the diagnostic and investigative steps listed in the URL within the error message.
Cheers,
Colin