JQuery Datatable JSON response error ColdFusion

Question

I have been working for a week in an attempt to help a friend with his JQuery datatable. The table shows volunteer applicants and there progress working thru an activity. The front end page contains the JS Datatable script and the ColdFusion page is used to query a MySQL database. When the page initially loads the following error shows in the debugging console.

 Uncaught TypeError: Cannot read property 'DT_RowId' of undefined
     at Object.rowIdFn (jquery.dataTables.min.js:19)
     at N (jquery.dataTables.min.js:16)
     at HTMLTableElement.<anonymous> (jquery.dataTables.min.js:92)
     at Function.each (jquery.js:374)
     at n.fn.init.each (jquery.js:139)
     at n.fn.init.m [as dataTable] (jquery.dataTables.min.js:82)
     at HTMLDocument.<anonymous> (index.cfm:897)
     at j (jquery.js:3099)
     at Object.fireWith [as resolveWith] (jquery.js:3211)
     at Function.ready (jquery.js:3417)

Then when the JS script refreshes and re-checks the database, then I get the following error.

 Uncaught TypeError: Cannot read property '0' of undefined
     at jquery.dataTables.min.js:19
     at Object.b.fnGetData (jquery.dataTables.min.js:12)
     at B (jquery.dataTables.min.js:17)
     at Ga (jquery.dataTables.min.js:14)
     at mb (jquery.dataTables.min.js:66)
     at T (jquery.dataTables.min.js:31)
     at Ub (jquery.dataTables.min.js:107)
     at r.<anonymous> (jquery.dataTables.min.js:108)
     at r.iterator (jquery.dataTables.min.js:99)
     at r.<anonymous> (jquery.dataTables.min.js:108)

Here is what the JS file looks like. I'm trying to use JQuery Datatable version 1.10.

$(document).ready(function() {
  var table = $('#displayData').dataTable({
    "processing": true,
    "stateSave": true,
    "serverSide": true,
    "ajax": {
      "url": "MyVolunteerCheck.cfm",
      "type": "POST"
    },

    "columns": [{
        "name": "EMPLOYEE_ID",
        "className": "hidden"
      },
      {
        "name": "EVER_NUM",
        "className": "hidden",
        "orderable": "true"
      },
      {
        "name": "LAST_NAME",
        "title": "LAST NAME",
        "orderable": "true"
      },
      {
        "name": "FIRST_NAME",
        "title": "FIRST NAME",
        "orderable": "true"
      },
      {
        "name": "SortOrderDate",
        "title": "APP DATE",
        "orderable": "true"
      },
      {
        "name": "DOCS_VER",
        "className": "hidden"
      },
      {
        "name": "DOCS_WAIT",
        "title": "APP STATUS",
        "orderable": "true"
      },
      {
        "name": "APP_STATUS",
        "title": "PROGRESS",
        "orderable": "true"
      },
      {
        "name": "LOCATION_NAME",
        "title": "LOCATION",
        "orderable": "true"
      },
      {
        "title": "OPTIONS",
        "orderable": "false"
      }
    ],

    "columnDefs": [

      { <cfset E = "+row[0]+" />
        "render": function(data, type, row) {
          return "<a class='btn btn-primary btn-xs' href='candidate/?select="#E#"'>view</a> <a class='btn btn-success btn-xs' href='candidate/?select=#e_id#" + "'>edit</a>";
        },
        "targets": -1
      },

      {

        "render": function(data, type, row) {
          var color = 'black';
          if (row[5] == 1) {
            color = 'green';
            ColorCheck = 'VALIDATED';
            IconChoice = ' fa fa-check-square-o';
          } else if (row[6] == 1) {
            color = 'orange';
            ColorCheck = 'WAITING';
            IconChoice = 'fa fa-spin fa-spinner';
          } else {
            color = 'red';
            ColorCheck = 'NON-VALID';
            IconChoice = 'fa fa-exclamation-triangle';
          }

          return '<span style="color:' + color + '"><i class="' + IconChoice + '"></i> ' + ColorCheck + '</span>';
        },
        "targets": -4


      },
      {

        "render": function(data, type, row) {
          var appstat = 'black';
          if (row[5] == 1) {
            appstat = 'green';
            TextStatus = 'FINISHED';
            IconChoice = 'fa fa-check';
          } else {
            appstat = 'black';
            var TextStatus = row[7];
            IconChoice = 'fa fa-chevron-right';
          }
          return '<span class="text-uppercase" style="color:' + appstat + '"><i class="' + IconChoice + '"></i> ' + TextStatus + '</span>';
        },
        "targets": -3
      },
      {
        "visible": false,
        "targets": [0]
      }
    ],
    "pagingType": "full_numbers",
    "order": [
      [2, 'asc']
    ],
    "language": {
      "lengthMenu": "Page length: _MENU_",
      "search": "Filter:",
      "zeroRecords": "No matching records found"
    },
    "data": function(sSource, aoData, fnCallback) {
      aoData.push();

      $.ajax({
        "dataType": 'json',
        "type": "POST",
        "url": sSource,
        "data": aoData,
        "success": fnCallback
      });
    }
  });
});
setInterval(function() {
  $('#displayData').DataTable().ajax.reload();
}, 30000);

On the coldfusion page, the system checks two tables and should return the information but it keeps erroring out. Include below is my CF Page.

<cfsilent>
<cfparam name="form.table" default="">
<cfparam name="form.columns" default="">
<cfparam name="form.editButtonText" default="">
<cfparam name="form.editButtonTarget" default="">
<cfparam name="form.search" default="">
<cfparam name="variables.fieldlist" default="">

<cfsetting showDebugOutput="true">
<cfsetting enablecfoutputonly="true">
<cfprocessingdirective suppresswhitespace="true">

<!--- this comes from the AJAX script in the template --->
<cfset variables.fieldlist=form.columns>
<cfset variables.count=0>

<!--- strip off the comma if it is the last element --->
<cfif right(variables.fieldlist,'1') EQ ",">
    <!--- last char is a comma --->
    <cfset variables.listLength = len(variables.fieldlist)>
    <cfset variables.fieldlist = left(variables.fieldlist, variables.listLength-1)>
</cfif>

<!--- get count of records --->
<cfquery name="qGetCount" datasource="MySQLDATABASE">
    SELECT COUNT(*) AS fullCount
    FROM VOLTABLE1
    WHERE ARCHIVE IS NULL
</cfquery>

<cfquery name="rResult" datasource="MySQLDATABASE">
    SELECT VOLTABLE1.EMPLOYEE_ID, VOLTABLE1.EVER_NUM, VOLTABLE1.LAST_NAME, VOLTABLE1.FIRST_NAME, VOLTABLE1.DOCS_VER, VOLTABLE1.DOCS_WAIT, VOLTABLE1.APP_STATUS, VOLTABLE1.LOCATION_ID, DATE_FORMAT(VOLTABLE1.EMPLOYEE_DATE,'%Y-%m-%d %r') AS SortOrderDate, VOLTABLE2.LOCATION_NAME
    FROM VOLTABLE1
    RIGHT JOIN
    VOLTABLE2
    ON VOLTABLE1.LOCATION_ID = VOLTABLE2.LOCATION_ID
    WHERE 1 = 1 AND VOLTABLE1.ARCHIVE IS NULL
    <cfif len(form.search)>
        AND (
        <cfloop from="1" to="#listLen(variables.fieldlist)#" index="variables.index">
            #listGetAt(variables.fieldlist, variables.index,',')# LIKE '%#form.search#%' <cfif variables.index LT listLen(variables.fieldlist)> OR </cfif>
        </cfloop>
        )
    </cfif>

    <cfif isdefined('form.iSortCol_0')>
        ORDER BY
        <cfloop from="0" to="#form.iSortingCols-1#" index="variables.i">
            #listGetAt(variables.fieldlist,form["iSortCol_#variables.i#"]+1)# #form["sSortDir_#variables.i#"]# 
            <cfif variables.i is not form.iSortingCols-1>, 
            </cfif>
        </cfloop>

    </cfif>
</cfquery>

<!--- strip off the table name from the values, otherwise it will break making the json --->
<cfset variables.fieldlist = ReplaceNoCase(variables.fieldlist,'VOLTABLE1.','','all')>
<cfset variables.fieldlist = ReplaceNoCase(variables.fieldlist,'VOLTABLE2.','','all')>

<!--- This is where I think the error is happening, I've read the JQuery Datatable documentation and I still am unable to resolve the issue.--->
<cfsavecontent variable="variables.sOutput">
    <cfoutput>{
        "draw": #form.draw#,
        "recordsTotal": #qGetCount.fullCount#,
        "recordsFiltered": #rResult.recordcount#,
        "data": [ 
        <cfloop query="rResult" startrow="#form.iDisplayStart+1#" endrow="#form.iDisplayStart+form.iDisplayLength#"><cfset variables.count=variables.count+1>
            [
            <cfloop list="#variables.fieldlist#" index="variables.i">
                <!--- custom translations --->
                <cfset outputResults = ReplaceNoCase(rResult[variables.i][rResult.currentRow],'"','', 'ALL' ) />
               "#outputResults#"
                <cfif variables.i is not listLast(variables.fieldlist)>, </cfif>
            </cfloop>
            ]

            <cfif rResult.recordcount LT form.iDisplayStart+form.iDisplayLength>
                <cfif variables.count is not rResult.recordcount AND rResult.recordcount NEQ rResult.currentRow>,</cfif>
            <cfelse>
                <cfif variables.count LT form.iDisplayLength>,</cfif>
            </cfif>

        </cfloop>
        ]}
    </cfoutput>
</cfsavecontent>
</cfprocessingdirective>
</cfsilent>
<cfoutput>#variables.sOutput#</cfoutput>

I know I am missing something, or my JSON call in incorrect, I've read thru Stack overflow and the JQuery DataTable site, and nothing really talks about the formating of the JSON stuff. Has anyone had this issue?

Answer 1

Even though I'm posting this as an answer, it's actually a formatted comment. With that said, it's hard to know what's going on without seeing the complete picture of your code. But here are some observations that I believe you should address. Start with one thing at a time.

1. Starting with your first error, you'll notice in the js stack trace, the line that gives the biggest clue is at HTMLDocument.<anonymous> (index.cfm:897). This line is the source of the error. Note that line number 897 is not the line in the source code of your index.cfm, but the line number of your rendered HTML output. This can be determined in the browser's developer tools.

2. In the second error, Uncaught TypeError: Cannot read property '0' of undefined the stack trace only makes references to jquery.dataTables.min.js so it's not possible for me to know what the source of the error is. This error comes about when trying to reach an undefined variable's properties or methods. Bottom line, datatables was unable to reference an object it was expecting.

3. I'm not sure what this is for: <cfset E = "+row[0]+"/>. You're setting E to a literal string and then attaching it the href attribute of an <a> tag? I'm not sure what you're going for so I don't have a suggestion other than it's probably not correct.

4. Your "data": function ( sSource, aoData, fnCallback ) {//stuff....}. I don't know where to begin because I have no idea where you sourced this code from. My points below are going with the presumption that this is intended to be your ajax callback to the server.

   • From what I can gather the data option is only used for client side and clearly you're coding this as a server side ajax call.
   • Your parameters sSource, aoData, fnCallback are all legacy 1.9 attributes and don't belong in your code.
   • To use ajax, under 1.10, you have to use the options serverSide: true and ajax: function ( data, callback, settings ) {//do stuff here.....}.

5. You're defining the columnDefs.render callback twice. This is not correct and you should only define it once.

So with all that said, I'm just curious how many records are your retrieving from your query? According to the docs datatables performs just fine using the default client-side setup if your data is under 50,000 rows.

By the way, I've personally used this and it's so mind bogglingly easy to setup it's practically effortless. So I'm just saying are you sure you need ajax? You might be spinning your wheels unnecessarily. Just food for thought.