cfgrid cfquery update

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
LVL 3
bhinshawnc1Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

bhinshawnc1Author Commented:
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
Pravin AsarPrincipal Systems EngineerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bhinshawnc1Author Commented:
Helped me get going in the right direction, Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.