Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

cfgrid cfquery update

Posted on 2012-03-13
3
Medium Priority
?
518 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 2000 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

CFGRID Custom Functionality Series -  Part 1 Hi Guys, I was once asked how it is possible to to add a hyperlink in the cfgrid and open the window to show the data. Now this is quite simple, I have to use the EXT JS library for this and I achiev…
Sometimes databases have MILLIONS of records and we need a way to quickly query that table to return the results me need. Sure you could use CFQUERY but it takes too long when there are millions of records. That is why SOLR was invented. Please …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

715 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