cfgrid update and insert issue help!!

Question

matt_stutts 0 Newbie Poster

14 Years Ago

I have a form that contains a cfgrid named idGrid (using CF9).
Contains four columns: Autonum (for now, the pk), CR_ID (fk), ID, and IDType.
These come from tblIDs in my db.

The grid is editable but not the Autonum or CR_ID fields (select="no").

I am using code straight from the Adobe help:
http://help.adobe.com/en_US/ColdFusion/9.0/Developing/WSc3ff6d0ea77859461172e0811cbec22c24-7acf.html

If I try to edit the existing records in this grid, it posts back values into the wrong columns! It puts the CR_ID into tblIDs.ID and the ID into tblIDs.IDType.

If I try to insert a new record and then post back the changes, I get an error:
Element IDGGRID.ROWSTATUS.ACTION is undefined in a Java object of type class [Ljava.lang.String; referenced as ''

I believe this is b/c fields in the new record are null. Autonum and CR_ID would likely be null but the user might also just not enter anything.
Major problem and I don't know how to get around it.

Please help!
---

<cfquery name="get_IDs" datasource="bridges">
SELECT tblIDs.Autonum, tblIDs.CR_ID, tblIDs.ID, tblIDs.IDType
FROM tblIDs
WHERE tblIDs.CR_ID = '#CFGRIDKEY#'
</cfquery>

<cfgrid
        name="idGrid"
        title="Related IDs"
        width="250" rowheaderalign="left"     
        query="get_IDs" 
        autowidth="false" griddataalign="left" gridlines="yes" colheaderalign="left"
        selectmode="edit" insert="yes" insertbutton="Insert New ID" enabled="yes" visible="yes" 
        format="html">
      <cfgridcolumn name="Autonum" display="no" select="no"/>
      <cfgridcolumn name="CR_ID" display="no" select="no"/>
      <cfgridcolumn name="ID" header="ID" />
      <cfgridcolumn name="IDType" header="ID Source" values="#r_idType#" valuesdisplay="#r_idType#" valuesdelimiter=","/>
    </cfgrid>

form posts to:

<cfloop index = "counter" from = "1" to = 
        #arraylen(Form.idGrid.rowstatus.action)#> 
        
         <cfoutput> 
            The row action for #counter# is: 
            #Form.idGrid.Autonum[counter]#
            #Form.idGrid.CR_ID[counter]# 
            #Form.idGrid.ID[counter]# 
            #Form.idGrid.IDType[counter]# 
            <br> 
        </cfoutput> 

[b]<!-- Here, if updating and not inserting, it'll give back results like: The row action for 1 is: 11093 11093 {666666666666JUNKME} onceagain.  It's showing the Autonum field twice!! -->[/b]
 
        <cfif Form.idGrid.rowstatus.action[counter] is "U"> 
            <cfquery name="UpdateIDTable" 
                datasource="bridges"> 
                UPDATE tblIDs 
                SET  
                    ID=<cfqueryparam  
                            value="#Form.idGrid.ID[counter]#"  
                            CFSQLType="CF_SQL_VARCHAR" >, 
                    IDType=<cfqueryparam  
                            value="#Form.idGrid.IDType[counter]#"  
                            CFSQLType="CF_SQL_VARCHAR" > 
                WHERE Autonum=<cfqueryparam 
                    value="#Form.idGrid.original.Autonum[counter]#"  
                    CFSQLType="CF_SQL_INTEGER"> 
            </cfquery> 
 
        <cfelseif Form.idGgrid.rowstatus.action[counter] is "I"> 
            <cfquery name="InsertNewID" 
                datasource="bridges"> 
                INSERT into tblIDs (CR_ID, ID, IDType) 
                VALUES (<cfqueryparam  
                    value="#Form.i_cr_id#"  [i](id taken from elsewhere)[/i]
                            CFSQLType="CF_SQL_VARCHAR" >, 
                        <cfqueryparam value="#Form.idGrid.ID[counter]#"  
                            CFSQLType="CF_SQL_VARCHAR" >,
                        <cfqueryparam value="#Form.idGrid.IDType[counter]#"  
                            CFSQLType="CF_SQL_VARCHAR" >  ) 
            </cfquery> 
 
        </cfif> 
    </cfloop> 
</cfif>

coldfusion

3 Contributors
9 Replies
380 Views
1 Year Discussion Span
Latest Post 13 Years Ago Latest Post by Sombra

arrgh 22 Posting Whiz

14 Years Ago

I haven't tested the code. But assuming "select" really is the problem, other options are to a) hide the column(display=false) or b) duplicate the value in your query and hide one version, and show the other. It doesn't matter if the visible version is editable, because you'll just ignore it in your action page.

Though I'm kinda wondering if "select" is really the issue ... it sounds odd.

arrgh 22 Posting Whiz

14 Years Ago

Oh, I was testing with CF8 which doesn't have this feature I think. I see there are several entries on it:
http://www.danvega.org/blog/index.cfm/2008/3/5/Hiding-Columns-In-cfrgids-Column-Context-Menu

It sounds like the menu lists hidden columns by default? I'm not sure that makes sense. But at least there's a workaround

arrgh 22 Posting Whiz

14 Years Ago

In the end, I found that if I moved the hidden, non-selectable field to the end of the grid, I don't get this issue. Kind of stupid and not a satisfying resolution but it's working... for now.

Yeah, that is a slightly lame. But sometimes you just have to do what you gotta do to make it work.

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

matt_stutts 0 Newbie Poster · Answer 1 · 2010-04-17T09:51:09+00:00

I have a cfgrid that is editable. Two colums are visible, one is the primary key and is not visible or selectable.

Upon making an edit to a row, submitting the form and doing a cfdump of the form, something very odd is apparent:

This is when the grid is first populated.
query
CFGRIDROWINDEX ID IDTYPE ID_ID
1 0000 WGN 11093

I change the value of ID to 0001 and leave everything else the same (ID_ID is the pk). Upon submitting the form and doing a quick cfdump before anything is updated to the db...

IDGRID.ID
array
1 11093
IDGRID.IDTYPE
array
1 0000
IDGRID.ID_ID
array
1 11093
IDGRID.ORIGINAL.ID
array
1 0001
IDGRID.ORIGINAL.IDTYPE
array
1 WGN
IDGRID.ORIGINAL.ID_ID
array
1 11093
IDGRID.ROWSTATUS.ACTION
array
1 U

The values are all messed up. grid.original.id actually has the updated value. And it mistakenly puts the orignal value into an updated value for idtype.

This ends up updating the wrong fields in my db!

What the heck?

I have a form that contains a cfgrid named idGrid (using CF9).
Contains four columns: Autonum (for now, the pk), CR_ID (fk), ID, and IDType.
These come from tblIDs in my db.

The grid is editable but not the Autonum or CR_ID fields (select="no").

I am using code straight from the Adobe help:
http://help.adobe.com/en_US/ColdFusion/9.0/Developing/WSc3ff6d0ea77859461172e0811cbec22c24-7acf.html

If I try to edit the existing records in this grid, it posts back values into the wrong columns! It puts the CR_ID into tblIDs.ID and the ID into tblIDs.IDType.

If I try to insert a new record and then post back the changes, I get an error:
Element IDGGRID.ROWSTATUS.ACTION is undefined in a Java object of type class [Ljava.lang.String; referenced as ''

I believe this is b/c fields in the new record are null. Autonum and CR_ID would likely be null but the user might also just not enter anything.
Major problem and I don't know how to get around it.

Please help!
---

<cfquery name="get_IDs" datasource="bridges">
SELECT tblIDs.Autonum, tblIDs.CR_ID, tblIDs.ID, tblIDs.IDType
FROM tblIDs
WHERE tblIDs.CR_ID = '#CFGRIDKEY#'
</cfquery>

<cfgrid
        name="idGrid"
        title="Related IDs"
        width="250" rowheaderalign="left"     
        query="get_IDs" 
        autowidth="false" griddataalign="left" gridlines="yes" colheaderalign="left"
        selectmode="edit" insert="yes" insertbutton="Insert New ID" enabled="yes" visible="yes" 
        format="html">
      <cfgridcolumn name="Autonum" display="no" select="no"/>
      <cfgridcolumn name="CR_ID" display="no" select="no"/>
      <cfgridcolumn name="ID" header="ID" />
      <cfgridcolumn name="IDType" header="ID Source" values="#r_idType#" valuesdisplay="#r_idType#" valuesdelimiter=","/>
    </cfgrid>

form posts to:

<cfloop index = "counter" from = "1" to = 
        #arraylen(Form.idGrid.rowstatus.action)#> 
        
         <cfoutput> 
            The row action for #counter# is: 
            #Form.idGrid.Autonum[counter]#
            #Form.idGrid.CR_ID[counter]# 
            #Form.idGrid.ID[counter]# 
            #Form.idGrid.IDType[counter]# 
            <br> 
        </cfoutput> 

[b]<!-- Here, if updating and not inserting, it'll give back results like: The row action for 1 is: 11093 11093 {666666666666JUNKME} onceagain.  It's showing the Autonum field twice!! -->[/b]
 
        <cfif Form.idGrid.rowstatus.action[counter] is "U"> 
            <cfquery name="UpdateIDTable" 
                datasource="bridges"> 
                UPDATE tblIDs 
                SET  
                    ID=<cfqueryparam  
                            value="#Form.idGrid.ID[counter]#"  
                            CFSQLType="CF_SQL_VARCHAR" >, 
                    IDType=<cfqueryparam  
                            value="#Form.idGrid.IDType[counter]#"  
                            CFSQLType="CF_SQL_VARCHAR" > 
                WHERE Autonum=<cfqueryparam 
                    value="#Form.idGrid.original.Autonum[counter]#"  
                    CFSQLType="CF_SQL_INTEGER"> 
            </cfquery> 
 
        <cfelseif Form.idGgrid.rowstatus.action[counter] is "I"> 
            <cfquery name="InsertNewID" 
                datasource="bridges"> 
                INSERT into tblIDs (CR_ID, ID, IDType) 
                VALUES (<cfqueryparam  
                    value="#Form.i_cr_id#"  [i](id taken from elsewhere)[/i]
                            CFSQLType="CF_SQL_VARCHAR" >, 
                        <cfqueryparam value="#Form.idGrid.ID[counter]#"  
                            CFSQLType="CF_SQL_VARCHAR" >,
                        <cfqueryparam value="#Form.idGrid.IDType[counter]#"  
                            CFSQLType="CF_SQL_VARCHAR" >  ) 
            </cfquery> 
 
        </cfif> 
    </cfloop> 
</cfif>

matt_stutts 0 Newbie Poster · Answer 2 · 2010-04-18T19:47:45+00:00

I managed to fix the insert issue.

But I've discovered something about the update.

If the column holding the hidden field (pk) is set to select="no", the array holding the original and changed values in the grid are completely jumbled.

But by setting it to 'yes' or not including that attribute, the problem goes away and the db is updated just fine.

I don't understand why, though. And I really don't want this column to be editable. All the examples I've seen suggest this should work fine with select='no'.

matt_stutts 0 Newbie Poster · Answer 3 · 2010-04-19T01:30:23+00:00

column is already hidden. this alone isn't enough to safeguard the user from making edits to it if select is changed to 'yes'. right clicking any cloumn, a user can make any field visible by turning it on. I'm not sure how to remove that capability in the grid. hmmm maybe there's a visible attribute of cfgridcolumn? not just 'display'? still, it's best to keep select=no, if possible.

it's the select attribute that's throwing it off somehow.
I'm not sure if option b would work either. it's the fact that the column, hidden or not, has select=no that causes the problem.

keep in mind, it doesn't actually get to the update sql that's causing the problem. a quick cfdump of the processing page prior to that update code firing shows that values like column and original.column are completely mixed up behind the scene.

matt_stutts 0 Newbie Poster · Answer 4 · 2010-04-19T02:08:18+00:00

<whoops.. this didn't work.... it's not-right click.. it's some other context menu that comes up.. user can left-click for it and make a non-visible column visible. gotta find the right attribute to stop this, if possible. unfortunately, the sort is under it too.>

I have a workaround. I'm not happy but it at least gets the job done.
I keep the column editable (rather, I don't use select=no) and instead used some code I found to use an event listener to keep the user from right-clicking the grid and making the key field visible. that way, they never get to it. But I'd still love to just use select=no, if possible.

http://www.coldfusionguy.com/ColdFusion/blog/index.cfm/2008/10/22/CFGrid-Disable-the-Right-Mouse-Click-Event

matt_stutts 0 Newbie Poster · Answer 5 · 2010-04-19T18:31:56+00:00

I guess, leave it to a new version to f*** things up. Yeah, you can bring up the context menu and turn on any previously hidden fields. I saw a couple of related posts on that site you linked and I made a few efforts to work some js to get at the ext.js behind the grid. In fact, that sort of code is already built into the ext.js to remove 'rowindex' field from the grid. But I couldn't seem to get it to work to remove my pk as well (from the context menu.)

In the end, I found that if I moved the hidden, non-selectable field to the end of the grid, I don't get this issue. Kind of stupid and not a satisfying resolution but it's working... for now.

Oh, I was testing with CF8 which doesn't have this feature I think. I see there are several entries on it:
http://www.danvega.org/blog/index.cfm/2008/3/5/Hiding-Columns-In-cfrgids-Column-Context-Menu
It sounds like the menu lists hidden columns by default? I'm not sure that makes sense. But at least there's a workaround

Sombra 0 Newbie Poster · Answer 6 · 2011-07-31T23:18:45+00:00

I had this same problem and solved it with a workaround:

1. Get the highest ID/Primary Key # that's in the table that the cfgrid is going to update

2. Update the table with the cfgrid

3. Go back and update the ID/Customer number/etc. of all records in the table that have a higher ID/Primary Key # than the one you got in step #1.

Hopefully Macromedia (i refuse to live in the present) will fix this issue and allow a default value, in the meantime the above works.

cfgrid update and insert issue *help!!*

cfgrid update and insert issue help!!