asked on

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#">

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")#',



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#">

</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

bhinshawnc1

ASKER

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.

ASKER CERTIFIED SOLUTION

Pravin Asar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bhinshawnc1

ASKER

Helped me get going in the right direction, Thanks