Solved

cfgrid cfquery update

Posted on 2012-03-13
3
516 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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

Hi, Even though I have created this Tutorial on My personal Blog, Some people might not able to find my website, So here i am posting it again Today, from the topic it is very clear that i will be showing you here the very basic usage of how we …
Recently while working on a project I got a very annoying cfdocument has no body error message. I had never seen this error before. So I checked the code. The code was pretty simple; it was Just showing me the cfdocumnt tag and inside that tag a …
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

628 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