Solved

Check for condition in CFC before update

Posted on 2012-04-02
5
502 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 52

Expert Comment

by:_agx_
ID: 37798677
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
ID: 37798894
yourItemCountColumn - is this the column number or column name ?
0
 
LVL 52

Accepted Solution

by:
_agx_ earned 500 total points
ID: 37798929
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
ID: 37802424
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_
ID: 37802671
Is Inventory_date a date/time column? If so better to use:

              Inventory_Date =  #createODBCDate(now())#

.. and don't forget cfqueryparam.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

PROBLEM:  How to open a cfwindow or run a function on double click of a cfgrid row. One of my clients wanted to be able to double click on a row item to get more detailed information about a transaction and to be able to modify the line items i…
Hi. There are several upload tutorials using jquery and coldfusion. I found a very interesting one here Upload Your Files using Jquery & ColdFusion and Preview them (http://www.randhawaworld.com/) . I did keep the main js functions but made sever…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

739 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