DataTable Jquery Server Side With Adobe Cold Fusion and SQL Server

DataTable Jquery Server Side With Adobe Cold Fusion and SQL Server

Adam.jaclouAdam.jaclou Posts: 2Questions: 0Answers: 0
edited March 2018 in Free community support

-- 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]

  1. 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\">&nbsp<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>
  1. 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>

Replies

  • Fr0sTFr0sT Posts: 1Questions: 0Answers: 0

    you are throwing an error because you are calling a.cfc instead of proses.cfc

  • Subhan_razaSubhan_raza Posts: 1Questions: 0Answers: 0

    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!

  • colincolin Posts: 4,686Questions: 0Answers: 822

    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

Sign In or Register to comment.