Solved

Check for condition in CFC before update

Posted on 2012-04-02
5
492 Views
Last Modified: 2012-04-03
I have a cfgrid bound to a cfc. The user can update the bin, min, max or inventory count for a row.

the bind statement:
onChange="cfc:functions.updateGLC({cfgridaction},{cfgridrow},{cfgridchanged}, '#getcurruser.uid#', '#show_glc_details.GLC_ID#')"

When the user updates the inventory count I need to update the inventory date, modify date and who modified the record

If the user updates just bin, min or max I need to update the modify date and who modified the record, leaving the inventory date as is so we can tell when the last inventory took place

How can I determine which event occurs and modify the sql statement accordingly?

the cfc I use:

<cffunction name="updateGLC" access="remote" output="false">
               
        <cfargument name="cfgridaction">
        <cfargument name="cfgridrow">
        <cfargument name="cfgridchanged">        
        <cfargument name="getcurruser"> <!--- pass in modified by user id --->
       <cfargument name="glctype" required="no" type="string" default="" >
     
        <cfset strctKey=StructKeyArray(cfgridchanged) />
        <cfset strctVal=StructFindKey(cfgridchanged, strctKey[1]) />
        <cfset strctVal=strctVal[1] />  

     <cfif cfgridaction is "D">
         
   <!--- delete handled another way --->
         
     <cfelseif cfgridaction is "U">
     
            <cfquery name="update" datasource="#request.dbname#" username="#request.dbuser#" password="#request.dbpw#">
          UPDATE GLC_Inventory
         
                SET #strctKey[1]# = '#strctVal.value#',
         
          Inventory_Date = '#dateformat(CreateODBCDateTime( Now()), "mm/dd/yyyy")#',
          Modify = '#getcurruser#',
          Modify_Date =  '#dateformat(CreateODBCDateTime( Now()), "mm/dd/yyyy")#'
          WHERE ID = #cfgridrow.ID#
        </cfquery>
       
     <cfelseif cfgridaction is "I">
     <!--- insert handled another way --->
     </cfif>
        <cfreturn />
    </cffunction>
0
Comment
Question by:bhinshawnc1
  • 3
  • 2
5 Comments
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
How can I determine which event occurs and modify the sql statement accordingly?

the cfgridchanged structure should contain columns that were changed. So if it contains the item count, you know you need to update the inventory date. Also add cfqueryparam to avoid sql injection risks.

             UPDATE GLC_Inventory
             SET         Inventory_Date = '#dateformat(CreateODBCDateTime( Now()), "mm/dd/yyyy")#',
                           Modify = '#getcurruser#',
                           Modify_Date =  #CreateODBCDate( Now()), "mm/dd/yyyy")#'
                          , ... other columns
                          <cfif structKeyExists(arguments.cfgridchanged , "yourItemCountColumn")>
                                 , ItemCountColumn = #val(arguments.cfgridchanged[ yourItemCountColumn ])#
                                 , Inventory_Date = '#dateformat(CreateODBCDateTime( Now()), "mm/dd/yyyy")#'
                         </cfif>
             WHERE .....
         
Be sure to use cfqueryparam to protect against sql injection. Also, if your Inventory/ModifyDate columns are type date/time, don't use strings.  Use date objects.
0
 
LVL 3

Author Comment

by:bhinshawnc1
Comment Utility
yourItemCountColumn - is this the column number or column name ?
0
 
LVL 52

Accepted Solution

by:
_agx_ earned 500 total points
Comment Utility
It is the name of that column in your cfgrid.

Just noticed a few typos, the example should be more like

             UPDATE GLC_Inventory
             SET         Modify = '#getcurruser#',
                           Modify_Date =  #CreateODBCDate( Now()), "mm/dd/yyyy")#'
                          , ... other columns

                          <!--- if the item count changed, update it and the inventory date --->
                          <cfif structKeyExists(arguments.cfgridchanged , "yourItemCountColumn")>
                                 , ItemCountColumn = #val(arguments.cfgridchanged[ "yourItemCountColumn" ])#
                                 , Inventory_Date = '#dateformat(CreateODBCDateTime( Now()), "mm/dd/yyyy")#'
                         </cfif>
             WHERE .....
0
 
LVL 3

Author Closing Comment

by:bhinshawnc1
Comment Utility
Thanks for the assistance. The final implementation looked like this:

 <cfif structKeyExists(arguments.cfgridchanged , "Inventory_Count")>
            Inventory_Date = '#dateformat(CreateODBCDateTime( Now()), "mm/dd/yyyy")#',
          </cfif>
0
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
Is Inventory_date a date/time column? If so better to use:

              Inventory_Date =  #createODBCDate(now())#

.. and don't forget cfqueryparam.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

This article  is about submitting  form through  ColdFusion.Ajax.submitForm to the action page and send a response back in JSON format which later can be decoded using ColdFusion.JSON.decode. By this way you can avoid the usual page refresh for subm…
Sometimes databases have MILLIONS of records and we need a way to quickly query that table to return the results me need. Sure you could use CFQUERY but it takes too long when there are millions of records. That is why SOLR was invented. Please …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now