Solved

cfgrid cfquery update

Posted on 2012-03-13
3
514 Views
Last Modified: 2012-03-20
I display an inventory to the user for a location and allow them to edit Bin, Min, Max and Inventory Count.

<cfform name="GridForm" action="update_glc_inventory.cfm">
 
    <cfgrid name="modify_glc_inventory"
        height=425
        autowidth="yes"
        width=1020
        vspace=10
        selectmode="edit"
        query="search_query"
        insert="No"
        delete="No"
        format="html"
        selectonload = "no"
        striperows = "yes"
        selectcolor="CDE6F3">
         
        <cfgridcolumn name="ID"
            header="ID"
            width=40
            headeralign="center"
            headerbold="Yes"
            select="No"
            display="No">
           
        <cfgridcolumn name="PID_ID"
            header="PID"
            width=50
            headeralign="center"
            headerbold="Yes"
            select="No">
 
        <cfgridcolumn name="Description"
            header="Description"
            width=150
            headeralign="center"
            headerbold="Yes"
            select="No">      
           
          <cfgridcolumn name="MOQ"
            header="MOQ"
                 width=30
            headeralign="center"
            headerbold="Yes"
            select="No">
           
         <cfgridcolumn name="COST"
            header="Cost"
            width=50
            headeralign="center"
            headerbold="Yes"
            select="No">
           
         <cfgridcolumn name="UNIT"
            header="Unit"
            width=50
            headeralign="center"
            headerbold="Yes"
            select="No">      
           
        <cfgridcolumn name="BIN"
            header="BIN"
            width=50
            headeralign="center"
            headerbold="Yes"
            select="Yes">  
           
         <cfgridcolumn name="MIN"
            header="MIN"
            width=30
            headeralign="center"
            headerbold="Yes"
            select="Yes">  
           
        <cfgridcolumn name="MAX"
            header="MAX"
            width=30
            headeralign="center"
            headerbold="Yes"
            select="Yes">  
           
        <cfgridcolumn name="Inventory_Count"
            header="Inventory"
            width=50
            headeralign="center"
            headerbold="Yes"
            select="Yes">
           
        <cfgridcolumn name="Inventory_Date"
            header="Inventory Date"
            width=60
            headeralign="center"
            headerbold="Yes"
            select="No"
            type="date">
 
    </cfgrid>
    <cfinput name="submitit" type="Submit" value="Submit">
</cfform>      

I pass that to the update page update_glc_inventory.cfm


<html>
<head>
    <title>Catch submitted grid values</title>
</head>
<body>
 
<h3>Grid values for Form.modify_glc_inventory row updates</h3>
 
<cfif isdefined("Form.modify_glc_inventory.rowstatus.action")>
 
    <cfloop index = "counter" from = "1" to =
        #arraylen(Form.modify_glc_inventory.rowstatus.action)#>
 
        <cfoutput>
            The row action for #counter# is:
            #Form.modify_glc_inventory.rowstatus.action[counter]#
            <br>
            BIN = #Form.modify_glc_inventory.BIN[counter]#
            <br>
            MIN = #Form.modify_glc_inventory.MIN[counter]#
            <br>
            MAX = #Form.modify_glc_inventory.MAX[counter]#
            <br>
            Inventory_Count= #Form.modify_glc_inventory.Inventory_Count[counter]#
            <br>
            Inventory_Date= #dateformat(CreateODBCDateTime( Now()), "mm/dd/yyyy")#
            <br>
            Modify = '#getcurruser.uid#',
            <br>
            Modify_Date =  '#dateformat(CreateODBCDateTime( Now()), "mm/dd/yyyy")#'
            <br>
           
        </cfoutput>
 
        <cfif Form.modify_glc_inventory.rowstatus.action[counter] is "D">
            <cfquery name="DeleteExisting"  datasource="#dbname#" username="#dbuser#" password="#dbpw#">
              <!---  DELETE FROM GLC_Inventory don't delete just set inactive in case recover needed --->
                UPDATE GLC_Inventory
                SET  ACTIVE_STATUS = 2
                WHERE ID=<cfqueryparam
                            value="#Form.modify_glc_inventory.original.ID[counter]#"  
                            CFSQLType="CF_SQL_INTEGER"
            </cfquery>
 
        <cfelseif Form.modify_glc_inventory.rowstatus.action[counter] is "U">
            <cfquery name="UpdateExisting" datasource="#dbname#" username="#dbuser#" password="#dbpw#">
                UPDATE GLC_Inventory
                SET  
                      <!------>
                              <cfif Form.modify_glc_inventory.BIN[counter] neq "">
                              BIN=<cfqueryparam  
                            value="#Form.modify_glc_inventory.BIN[counter]#"  
                            CFSQLType="CF_SQL_VARCHAR" maxlength="25" >,
                     </cfif>
                                          
                    <cfif Form.modify_glc_inventory.MIN[counter] neq "">
                    MIN=<cfqueryparam  
                            value="#Form.modify_glc_inventory.MIN[counter]#"  
                            CFSQLType="CF_SQL_INTEGER" >,
                               </cfif>
                                 
                  <cfif Form.modify_glc_inventory.MAX[counter] neq ""> 
                    MAX=<cfqueryparam  
                            value="#Form.modify_glc_inventory.MAX[counter]#"  
                            CFSQLType="CF_SQL_INTEGER" >,
                  </cfif>
                           
                    Inventory_Count=<cfqueryparam  
                            value="#Form.modify_glc_inventory.Inventory_Count[counter]#"  
                            CFSQLType="CF_SQL_INTEGER" >,
                           
                      Inventory_Date='#dateformat(CreateODBCDateTime( Now()), "mm/dd/yyyy")#',
                   
                   <!--- <cfqueryparam  
                            value="#Form.modify_glc_inventory.Inventory_Date[counter]#"  
                            CFSQLType="CF_SQL_DATE" >, --->
                           
                     Modify = '#getcurruser.uid#',
                     Modify_Date =  '#dateformat(CreateODBCDateTime( Now()), "mm/dd/yyyy")#'            
                WHERE ID=<cfqueryparam
                            value="#Form.modify_glc_inventory.original.ID[counter]#"  
                            CFSQLType="CF_SQL_INTEGER"
            </cfquery>
 
        <cfelseif Form.modify_glc_inventory.rowstatus.action[counter] is "I">
            <cfquery name="InsertNew" datasource="#dbname#" username="#dbuser#" password="#dbpw#">
      <!---      Insert handled another way
        
            INSERT into GLC_Inventory (Status_ID, Status_Type)
                VALUES (<cfqueryparam  
                    value="#Form.Status_Types.Status_Type[counter]#"  
                            CFSQLType="CF_SQL_VARCHAR" >,
                        <cfqueryparam value="#Form.Status_Types.Status_ID[counter]#"  
                            CFSQLType="CF_SQL_INTEGER" >)  --->
            </cfquery>
 
        </cfif>
    </cfloop>
</cfif>       

When it runs I get an error becuase the columns are not in the right order:

The row action for 1 is: U
BIN = EA <-- this is from unit column
MIN = ACA21 <-- from bin
MAX = 5
Inventory_Count= 5
Inventory_Date= 03/13/2012
Modify = 'bhinshaw',
Modify_Date = '03/13/2012'

How should I code this so that the items are passed to the update in proper order?

Thanks
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
  • 2
3 Comments
 
LVL 3

Author Comment

by:bhinshawnc1
ID: 37721455
Update after much search and testing.

In update_glc_inventory.cfm I added <cfdump var="#form#"> and was able to see it's not skipping, the order of the array is all over the place

http://forums.adobe.com/message/2935249

Not only must the primary key be selectable, any column to the left of a column being edited must also be selectable. So in other words, if you have 6 columns and want column 5 to be editable, the prior four must also be editable.
0
 
LVL 29

Accepted Solution

by:
Pravin Asar earned 500 total points
ID: 37729575
Look my one of my accepted solution about the cfgrid update.


http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/Cold_Fusion_Markup_Language/Q_26937323.html


By  bind on change event, you can update the db, and minimize the grid update time.


The solution has entire code (front end+backend cfcs).
0
 
LVL 3

Author Closing Comment

by:bhinshawnc1
ID: 37745239
Helped me get going in the right direction, Thanks
0

Featured Post

Business Impact of IT Communications

What are the business impacts of how well businesses communicate during an IT incident? Targeting, speed, and transparency all matter. Find out more in this infographic.

Question has a verified solution.

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

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…
Today, I was working on some optimization and spam-stopping techniques when I encountered Ben Nadel's post to reduce spam feature using Math (http://www.bennadel.com/blog/197-How-I-Stop-Spammers-On-My-ColdFusion-Blog.htm). While this method is not o…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

734 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