Solved

cfgrid cfquery update

Posted on 2012-03-13
3
507 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
  • 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 28

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

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…
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…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

746 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

8 Experts available now in Live!

Get 1:1 Help Now