Update JQUERY DATATABLE CELL In Database using Coldfusion and Ajax

plumberboy77

I am new to datatables and want to know the best way to integrate INSERT for each cell being changed on the DataTable.My question is how can I reference the data being dumbed in the data table and be able to update the database table with the changes the user made? I have ajax implemented to call to masterMaint.cfc and run the query there when the user hits the confirm button on the datatable after their edit. How can I make this call run successful and update the table with the values they have changed?

I currently have this as my HTML table:

<form name = "docTable" id="docTableID">
<div id ="div1" class="dataTables_wrapper">
<table id="MasterDocs" border="1" style="width: 750px;border-collapse: collapse">
<thead>
    
    <tr>
    <th>SITE</th>
    <th>SECTIONS</th>
    <th>TAB</th>
    <th>DOC_NUMBER</th>
    <th>DESCRIPTION</th>
    <th>REV_LEVEL</th>
    <th>REVISION_DATE</th>
    <th>REVIEW_DATE</th>
    <th>ADMIN</th>
    <th>ACTIVE</th>

</tr></thead>
<tbody>   
</tbody>
</table>
</div>
    </form>

Here is my cfc file which contains the INSERT query:

<cfcomponent output="false">
<cffunction name="updateDocuments" access="remote" output="false" returntype="Any">
<cfargument name="docID" type="string" required="yes">
    <cfquery datasource="#LIV02010_WEB#" name="updateDocs">
        UPDATE MDL_MASTER_LIST set 
                SITE = <cfqueryparam cfsqltype="cf_sql_varchar" value='#FORM.docTable.SITE#'>,
                SECTIONS = <cfqueryparam cfsqltype="cf_sql_varchar" value='#FORM.docTable.SECTIONS#'>,
                TAB = <cfqueryparam cfsqltype="cf_sql_varchar" value='#FORM.docTable.TAB#'>,
                DOC_NUMBER = <cfqueryparam cfsqltype="cf_sql_varchar" value='#FORM.docTable.DOC_NUMBER#'>,
                DESCRIPTION = <cfqueryparam cfsqltype="cf_sql_varchar" value='#FORM.docTable.DESCRIPTION#'>,
                ACTIVE = <cfqueryparam cfsqltype="cf_sql_varchar" value='#FORM.docTable.ACTIVE#'>,
            WHERE DOC_NUMBER = <cfqueryparam cfsqltype="cf_sql_varchar" value='#FORM.docTable.DOC_NUMBER#'>
    </cfquery>

</cffunction>
</cfcomponent>

Here is my AJAX call, where I am wanting to run updateDataBaseTable function when the onUpdate confirm button is hit:

<script type="text/javascript">
    
   function myCallbackFunction (updatedCell, updatedRow, oldValue) {
       alert("The new value for the cell is: " + updatedCell.data());
        // alert("The values for each cell in that row are: " + updatedRow.data());
    };

    var table;

    $(document).ready(function(){

        function updateDataBaseTable() {
        var updateDataBase = function(doc_number){
        $.ajaxSetup({cache:false});
        $.ajax({
            type:'post',
            url:"masterMaint.cfc?method=updateDocuments&docID="+doc_number,
            data:$('#docTableID').serialize(),
            success: function(data) {

                alert('OK');

            }
        });
    };

        };

       table = $('#MasterDocs').DataTable({
            "bJQueryUI": true,
            paging: false, // this allows you to pick if you want to filter by how many per page
            ordering: true, // this allows you to filter by col title
            info: false, // Will show "1 to n of n entries" Text at bottom
            processing: true,
            "sAjaxSource": "dataStore.cfm",
            "aoColumns": [
         { "mData": "SITE" },
         { "mData": "SECTIONS" },
         { "mData": "TAB" },
         { "mData": "DOC_NUMBER" },
         { "mData": "DESCRIPTION" },
         { "mData": "REV_LEVEL" },
         { "mData": "REVISION_DATE" },
         { "mData": "REVIEW_DATE" },
         { "mData": "ADMIN" },
         { "mData": "ACTIVE" }
         
        ]
        });
    

        
//this function allows you to select which column can be changed and what you want to do with it. 
        table.MakeCellsEditable({
        "onUpdate": updateDataBaseTable,
        "inputCss":'my-input-class',
        "columns": [0,1,2,3,9],
        "allowNulls": {
            "columns": [1],
            "errorClass": 'error'
        },
        "confirmationButton": { 
            "confirmCss": 'my-confirm-class',
            "cancelCss": 'my-cancel-class'
        },

And here is my cfm file which contains the query which populates the datatable.

<cfquery datasource="mydatastore" name="get_all_docs">
SELECT SITE,SECTIONS,TAB,DOC_NUMBER,DESCRIPTION,REV_LEVEL,REVISION_DATE,REVIEW_DATE,ADMIN,ACTIVE from MDL_MASTER_LIST
ORDER BY SITE,sections,TAB,DOC_NUMBER 
</cfquery>

<cfset data = [] />

<cfoutput query="get_all_docs">
    <cfset obj = {
        "SITE" = SITE,
        "SECTIONS" = SECTIONS,
        "TAB" = TAB,
        "DOC_NUMBER" = DOC_NUMBER,
        "DESCRIPTION" = DESCRIPTION,
        "REV_LEVEL" = REV_LEVEL,
        "REVISION_DATE" = REVISION_DATE,
        "REVIEW_DATE" = REVIEW_DATE,
        "ADMIN" = ADMIN,
        "ACTIVE" = ACTIVE
     } />
    <cfset arrayAppend(data, obj) />
</cfoutput>

<cfprocessingdirective suppresswhitespace="Yes">
    <cfoutput>
        {"aaData":
        #serializeJSON(data)#
        }
    </cfoutput>
</cfprocessingdirective>

<cfsetting enablecfoutputonly="No" showdebugoutput="No">

allan

For the ColdFusion part, I'm afraid you'd need to ask on StackOverflow or somewhere that ColdFusion experts hang out. It has been many years since I did any, and even then it was very basic.

I'm also not sure what table.MakeCellsEditable is - but you'd need to ask the author about what it sends to the server.

Our own editing solution for DataTables has its client / server communication full documented and I'd be happy to answer any questions you might have about it.

Regards,
Allan