Solved

cfwindow-cfgrid-cfgridupdate  problem

Posted on 2010-08-12
21
1,176 Views
Last Modified: 2012-05-10
Hello experts.
I m using cfwindow and cfgrid with cfgridupdate.
If i submit the form without changing any field i have an error:The cfgridupdate tag cannot find the grid named Kategorie.
If i make any change and submit the form I get an error:
 Primary key not found for the Kategorie table.
The table you are attempting to update, Kategorie does not have a primary key, which is a field or set of fields that uniquely identify each record..Any help?
(i'm using cf8 and mysql)

<a href="#" onclick="ColdFusion.navigate('includes/body.cfm?action=categories', 'getfilespage'); ColdFusion.Window.show('getfilespage');">Categories</a><br>



<cfwindow center="true"
          name="getfilespage"
          minHeight="150" 
          minWidth="150" 
          title="Body" 
          initshow="false"
          modal="true"/>


the body.cfm
<cfif url.action EQ 'categories'>
<cfif IsDefined("form.gridEntered") is True>
   <cfgridupdate grid = "Kategorie" dataSource = "#request.dsn#" Keyonly="true" tableName = "Kategorie">
 </cfif>

<cfquery name="q_Kategorie" datasource="#request.dsn#">
SELECT Kat_Text,Kat_ID,langid,mobiles_sub_id
FROM <!---dbo.--->Kategorie
ORDER BY Kat_ID ASC 
</cfquery>
 <cfform>
   <cfgrid name="Kategorie" format="html" query="q_Kategorie" pagesize="7" insert = "Yes"  delete = "Yes" sort="yes"
 selectmode="edit">
      <cfgridcolumn name="Kat_ID" header="ID" width="30"/>
      <cfgridcolumn name="Kat_Text" header="Text" width="120"/>
      <cfgridcolumn name="mobiles_sub_id" header="Mobile" width="40"/>
      <cfgridcolumn name="LangID" header="LangID" width="40"/>
   </cfgrid>
   <cfinput type="submit" name="gridEntered">

</cfform>
</cfif>

Open in new window

0
Comment
Question by:Panos
  • 14
  • 7
21 Comments
 
LVL 52

Accepted Solution

by:
_agx_ earned 500 total points
ID: 33423978
>>> If i submit the form without changing any field i have an error
>>> <cfif IsDefined("form.gridEntered") is True>

    Dump the FORM scope just before that line. What are the results?

>>> Primary key not found for the Kategorie table

    I assume "Kat_ID" is the primary key in your table?  It sounds like you didn't
    define that column as the PRIMARY KEY when you created the table in MySQL
    http://dev.mysql.com/doc/refman/5.1/en/create-table.html
0
 
LVL 2

Author Comment

by:Panos
ID: 33424543
Hi agx.
I dump the form but i get the coldfusion errormessage.
using cfdebug i get this error:error:http: Error retrieving markup for element getfilespage_body : Internal Server Error

0
 
LVL 52

Expert Comment

by:_agx_
ID: 33424550
No, I mean just dump the scope normally, using CFDUMP

<cfdump var="#FORM#">
<cfif IsDefined("form.gridEntered") is True>
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 2

Author Comment

by:Panos
ID: 33424635
I did exactly this.
when the cfwincow opens it shows struct [empty] and when i hit the submit button i get the error:The cfgridupdate tag cannot find the grid named Kategorie.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 33424668
One of us is misunderstanding something ;-)  

In order to see what values are sent when the form is submitted, you need to place cfdump code BEFORE the cfif/cfgridupdate code.  Otherwise, an error happens and the results are never shown.

<cfif url.action EQ 'categories'>
    <CFDUMP VAR="#FORM#">             <!--- MUST be placed before the grid update --->

    <cfif IsDefined("form.gridEntered") is True>
           <cfgridupdate grid = "Kategorie" dataSource = "#request.dsn#" Keyonly="true" tableName = "Kategorie">
     </cfif>

</cfif>


0
 
LVL 2

Author Comment

by:Panos
ID: 33424740
Here is my code:
<cfif url.action EQ 'categories'>
<cfdump var="#FORM#">
  <cfif IsDefined("form.gridEntered") is True>
   <cfgridupdate grid = "Kategorie" dataSource = "#request.dsn#" Keyonly="true" tableName = "Kategorie">
  </cfif>
   <cfquery name="q_Kategorie" datasource="#request.dsn#">
   SELECT Kat_Text,Kat_ID,langid,mobiles_sub_id
   FROM <!---dbo.--->Kategorie
   ORDER BY Kat_ID ASC
   </cfquery>
  <cfform>
   <cfgrid name="Kategorie" format="html" query="q_Kategorie" pagesize="7" insert = "Yes"  delete = "Yes" sort="yes"
    rowheaderbold="yes" colHeaderBold = "Yes"  selectmode="edit" pictureBar="yes" hspace="0" vspace="0" autowidth="yes">
      <cfgridcolumn name="Kat_ID" header="ID" width="30"/>
      <cfgridcolumn name="Kat_Text" header="Text" width="120"/>
      <cfgridcolumn name="mobiles_sub_id" header="Mobile" width="40"/>
      <cfgridcolumn name="LangID" header="LangID" width="40"/>
   </cfgrid>
   <cfinput type="submit" name="gridEntered">
  </cfform>
</cfif>
0
 
LVL 2

Author Comment

by:Panos
ID: 33424896
The other mistery i have is that when i add in the query the column with the primary key(table_id)
i get the error:[Macromedia][SQLServer JDBC Driver][SQLServer]Invalid column name 'table_id'.
0
 
LVL 2

Author Comment

by:Panos
ID: 33424921
part of mysql table-settings
screen.gif
0
 
LVL 52

Expert Comment

by:_agx_
ID: 33425027
>> Here is my code:

   Oh, I misunderstood then.  You're saying when you do that the FORM is totally empty?
   Try the exact same code, but without the <cfwindow>.  I'm wondering if cfwindow is messing
   things up.  



0
 
LVL 2

Author Comment

by:Panos
ID: 33425047
agx i get the same errors.
Maybe i did something wrong in the mysql table.
0
 
LVL 2

Author Comment

by:Panos
ID: 33425062
when imake a change and submit the form:
 Primary key not found for the Kategorie table.
The table you are attempting to update, Kategorie does not have a primary key, which is a field or set of fields that uniquely identify each record.
0
 
LVL 2

Author Comment

by:Panos
ID: 33425149
Here is  my new error:
 Primary key(s) table_id not found.
The cfgridupdate tag cannot update or delete the row because it cannot find the primary key(s) table_id in the cfgrid form fields for table Kategorie in datasource....
0
 
LVL 2

Author Comment

by:Panos
ID: 33425188
Agx.
Finally a part is working.I add the table_id in the cfgrid but i need it as a hidden field.
<cfform>
   <cfgrid name="Kategorie" format="html" query="q_Kategorie" pagesize="7" insert = "Yes"  delete = "Yes" sort="yes"
    rowheaderbold="yes" colHeaderBold = "Yes"  selectmode="edit" pictureBar="yes" hspace="0" vspace="0" autowidth="yes">
      <cfgridcolumn name="Kat_ID" header="ID" width="30"/>
      <cfgridcolumn name="Kat_Text" header="Text" width="120"/>
      <cfgridcolumn name="mobiles_sub_id" header="Mobile" width="40"/>
      <cfgridcolumn name="LangID" header="LangID" width="40"/>
      <cfgridcolumn name="table_id" header="Table_id" width="40"/>
   </cfgrid>
   <cfinput type="submit" name="gridEntered_kat">
  </cfform>

Now if i make changes and submit the form i have no errors.
If i submit the form without making a change i get the error:
The cfgridupdate tag cannot find the grid named Kategorie.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 33425194
Ok, but is the "table_id" actually defined as the PRIMARY KEY in your table?  If not, that sounds like the problem.  Can script out the mySQL table and post the definition? ie  the CREATE TABLE ...(  statement.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 33425217
Sorry, our posts overlapped. Disregard my last response.
0
 
LVL 2

Author Comment

by:Panos
ID: 33425223
CREATE TABLE `kategorie` (
  `table_id` int(11) NOT NULL AUTO_INCREMENT,
  `Kat_ID` int(11) DEFAULT '0',
  `Kat_Text` varchar(50) DEFAULT '',
  `LangID` int(11) DEFAULT '0',
  `Mobiles_sub_ID` int(11) DEFAULT '0',
  PRIMARY KEY (`table_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=89 DEFAULT CHARSET=utf8
0
 
LVL 52

Expert Comment

by:_agx_
ID: 33425430
>> Finally a part is working.I add the table_id in the cfgrid but i need it as a hidden field.

Now that the basic code is functioning, does it work once you put the cfwindow back in?
0
 
LVL 2

Author Comment

by:Panos
ID: 33426987
agx i'm back it was too late for me on my last comment.

there is a misfunction using cfwindow It is not updating the correct row.
0
 
LVL 2

Author Comment

by:Panos
ID: 33427042
Disregard my last post.
It is the first time i use cfgrid and unfortunately i'm using it in a wrong way.I did  wrong updated  row because i was selecting before submitting another row to deselect the altered row field.Now i'm clicking on the white space and have no mistaces.The only error that remains is if i have not make any change.
0
 
LVL 2

Author Comment

by:Panos
ID: 33443585
agx.
Finally i did solve my problem using cfgridaction instead of cfgridupdate.
and for a more complex example i followed the tutorial on this page:http://www.coldfusion-ria.com/Blog/index.cfm/2009/1/21/ColdFusion-8-cfgrid-adding-Add-and-Delete-Record-Functionality where i did add the edit function.
I post a working code below for the first example:
<cfquery name="q_Geschlecht" datasource="#request.dsn#">
    SELECT geschlecht_text ,geschlecht_ID,langid
    FROM t_geschlecht
    </cfquery>
   <cfform>
    <cfgrid name="Geschlecht" format="html" pagesize="30"   delete = "Yes" sort="yes" 
    rowheaderbold="yes" colHeaderBold = "Yes"    pictureBar="yes" hspace="0" vspace="0"  bind="cfc:cfc.geschlecht.getGeschlecht({cfgridpage},{cfgridpagesize},{cfgridsortcolumn},{cfgridsortdirection})" selectmode="edit" onchange= "cfc:cfc.geschlecht.updateGeschlecht({cfGridAction},{cfgridrow},{cfgridchanged})">
      <cfgridcolumn name="geschlecht_ID" header="ID" width="30"/>
      <cfgridcolumn name="geschlecht_text" header="Text" width="120"/>
      <cfgridcolumn name="LangID" header="LangID" width="40"/>
      <cfgridcolumn name="table_id" header="Table_id"  select="no"/>
    </cfgrid>
  </cfform>

the cfc:
<cffunction name="getgeschlecht" access="remote">
      <cfargument name="page" required="yes">
      <cfargument name="pageSize" required="yes">
      <cfargument name="gridsortcolumn" required="yes">
      <cfargument name="gridsortdirection" required="yes">
    <cfquery name="q_geschlecht" datasource="#request.dsn#">
    SELECT geschlecht_text ,geschlecht_ID,langid,table_id
    FROM <!---dbo.--->t_geschlecht
    <cfif gridsortcolumn neq ''>
      order by #gridsortcolumn# #gridsortdirection#
     </cfif>
    </cfquery>
   <cfreturn queryconvertforgrid(q_geschlecht,page,pagesize)/>
</cffunction>
<cffunction name="updategeschlecht" access="remote">
      <cfargument name="gridaction" type="string" required="yes">
      <cfargument name="gridrow" type="struct" required="yes">
      <cfargument name="gridchanged" type="struct" required="yes">
      <cfset var colname = "">
      <cfset var value = "">
 <cfswitch expression="#arguments.gridaction#">
     <!--- update --->
   <cfcase value="U">
    <cfset colname = StructKeyList(arguments.gridchanged)>
    <cfset value = arguments.gridchanged[colname]>

      <cfquery datasource="#request.dsn#">
        UPDATE t_geschlecht
        SET #colname# = '#value#'
        WHERE table_id = <cfqueryparam value="#arguments.gridrow.table_id#" cfsqltype="cf_sql_integer">
       </cfquery>
     </cfcase>
     <!--- delete --->
      <cfcase value="D">
       <cfquery datasource="#request.dsn#">
       DELETE FROM t_geschlecht
       where table_id = <cfqueryparam value="#arguments.gridrow.table_id#" cfsqltype="cf_sql_integer">
       </cfquery>
      </cfcase>
     </cfswitch>
</cffunction>

Open in new window

0
 
LVL 2

Author Closing Comment

by:Panos
ID: 33443591
Thank you
regards
panos
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

The technique is by far very Simple! How we can export the ColdFusion query results to DOC file?  Well before writing this I researched a lot in Internet but did not found a good Answer anyways!  So i thought now i should share my small snippet w…
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 …
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

813 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

14 Experts available now in Live!

Get 1:1 Help Now