Skip to main content
J
jawmusic
Inspiring
December 12, 2011
Answered

Problem with update record form

  • December 12, 2011
  • 4 replies
  • 1923 views

I have an update record form that uses a <cfquery> function and has (among other fields) an optional file input field and two separate fields to specify the file name and file type. I don't get any errors when I submit the form, but it does not update the record and does not redirect to the page specified in the <cflocation> function. Instead, it simply reloads the update record page with all values removed. The cfquery function is copied below. Any ideas what I'm missing? I'm relatively new to cf and sql, but in comparing it to other update record forms I have for other tables, I can't find a problem.

<cfif IsDefined("FORM.MM_UpdateRecord") AND FORM.MM_UpdateRecord EQ "content_edit">

  <cfquery datasource="everettweb">UPDATE Content

SET Page_URL=<cfif IsDefined("FORM.Page_URL") AND #FORM.Page_URL# NEQ "">

#FORM.Page_URL#

<cfelse>

''

</cfif>

, Page_Category=<cfif IsDefined("FORM.Page_Category") AND #FORM.Page_Category# NEQ "">

#FORM.Page_Category#

<cfelse>

''

</cfif>

, Content_Description=<cfif IsDefined("FORM.Content_Description") AND #FORM.Content_Description# NEQ "">

#FORM.Content_Description#

<cfelse>

''

</cfif>

, Content_Heading=<cfif IsDefined("FORM.Content_Heading") AND #FORM.Content_Heading# NEQ "">

#FORM.Content_Heading#

<cfelse>

''

</cfif>

, Content=<cfif IsDefined("FORM.Content") AND #FORM.Content# NEQ "">

#FORM.Content#

<cfelse>

''

</cfif>

, Image_URL=<cfif IsDefined("FORM.Filename") AND #FORM.Filename# NEQ "">

/images/#FORM.Filename#.#FORM.File_type#

<cfelse>

''

</cfif>

WHERE ID=#FORM.ID#

</cfquery>

  <cflocation url="#FORM.Page_URL#">

</cfif>

Thanks for any help you can provide!

Jeremy

    This topic has been closed for replies.
    Correct answer jawmusic

    Okay, so I rebuilt the page and it worked. When I compared the two versions it looks like when I made an edit to the sql I had a bit of redundant code in the form that was throwing things off. Thanks everyone for your help!

    Dan, as for the two types of variables, the URL variable is there to so I can populate the form with the existing record data. Here's the process:

    -Main page has a link to edit content, which is identified with the ID number.

    -The edit link calls the update form: content_editor.cfm?recordID=#ID#

    -A query on the content_editor.cfm page calls for the existing record data using the url variable (because at this point that's the only remaining identifier from the previous page) to populate the form.

    -A second query updates the record upon submission of the form.

    If there's another way to do it, I don't know. I let Dreamweaver show me one way and then I adapted that method to a couple different update record forms. I'm definitely open to other suggestions!

    JAW

    4 replies

    J
    jawmusicAuthor
    Inspiring
    December 13, 2011

    Thanks everyone for your responses. I originally had the <cfparam> and <cfqueryparam> tags there (Dreamweaver put them in automatically), but when I looked in the cf documentation it didn't show them so i got rid of them thinking they were extranneous code Dreamweaver added unneccessarily. I've added them back in now. Now when I try the update I get the following error:

    Invalid data '' for CFSQLTYPE CF_SQL_NUMERIC.

    The error occured in C:\Web_Sites\Everett2\content_editor.cfm: line 17

    15: SELECT Content.ID, Content.Page_URL, Content.Page_Category, Content.Content_Description, Content.Content_Heading, Content.Content, Content.Image_Name
    16: FROM Content
    17: WHERE ID = <cfqueryparam value="#URL.recordID#" cfsqltype="cf_sql_numeric">
    18: </cfquery>

    This is the query that pulls the existing record values to be inserted into the form fields as the defaults for editing. Trying to dump the form values after processing didn't work now because I get this error. This is after trying to edit the record. The initial load of the page works fine, with all the existing record data inserted into the form fields properly.

    Is there any chance this could be an error on the server end? I'm getting the same error with other update record pages that had previously been working properly. I did some minor tweaking to some of the form fields which required eliminating items from the sql, but the fact that I'm getting the same error referencing the same bit of code from the query that calls the existing record information seems to be an awfully big coincidence.

    Thoughts? I'd be happy to post all of the sql for both queries if that would help. I just didn't want to overwhelm anyone with more code than is necessary.

    Jeremy

    D
    Dan_Bracuk
    Inspiring
    December 13, 2011

    What is the value of url.id when this happens?

    By the way, life is much simpler when you have url variables or form variables, but not both.

    J
    jawmusicAuthorCorrect answer
    Inspiring
    December 13, 2011

    Okay, so I rebuilt the page and it worked. When I compared the two versions it looks like when I made an edit to the sql I had a bit of redundant code in the form that was throwing things off. Thanks everyone for your help!

    Dan, as for the two types of variables, the URL variable is there to so I can populate the form with the existing record data. Here's the process:

    -Main page has a link to edit content, which is identified with the ID number.

    -The edit link calls the update form: content_editor.cfm?recordID=#ID#

    -A query on the content_editor.cfm page calls for the existing record data using the url variable (because at this point that's the only remaining identifier from the previous page) to populate the form.

    -A second query updates the record upon submission of the form.

    If there's another way to do it, I don't know. I let Dreamweaver show me one way and then I adapted that method to a couple different update record forms. I'm definitely open to other suggestions!

    JAW

    S
    Steve Sommers
    Legend
    December 12, 2011

    My first recommendation would be to get into the habit of using the cfqueryparam tag, especially for any and all data from a client via URL or FORM variables:

    <cfparam name="FORM.ID" default="" />
    <cfparam name="FORM.Page_URL" default="" />
    <cfparam name="FORM.Page_Category" default="" />
    <cfparam name="FORM.Content_Description" default="" />
    <cfparam name="FORM.Content_Heading" default="" />
    <cfparam name="FORM.Content" default="" />
    <cfparam name="FORM.Image_URL" default="" />

    <cfquery name="qUpdate" datasource="everettweb">
    UPDATE
      Content
    SET
      Page_URL=<cfqueryparam value="#FORM.Page_URL#" cfsqltype="CF_SQL_VARCHAR" null="#evaluate('len(FORM.Page_URL) EQ 0')#" />,
      Page_Category=<cfqueryparam value="#FORM.Page_Category#" cfsqltype="CF_SQL_VARCHAR" null="#evaluate('len(FORM.Page_Category) EQ 0')#" />,
      Content_Description=<cfqueryparam value="#FORM.Content_Description#" cfsqltype="CF_SQL_VARCHAR" null="#evaluate('len(FORM.Content_Description) EQ 0')#" />,
      Content_Heading=<cfqueryparam value="#FORM.Content_Heading#" cfsqltype="CF_SQL_VARCHAR" null="#evaluate('len(FORM.Content_Heading) EQ 0')#" />,
      Content=<cfqueryparam value="#FORM.Content#" cfsqltype="CF_SQL_VARCHAR" null="#evaluate('len(FORM.Content) EQ 0')#" />,
      Image_URL=<cfqueryparam value="#FORM.Image_URL#" cfsqltype="CF_SQL_VARCHAR" null="#evaluate('len(FORM.Image_URL) EQ 0')#" />
    WHERE
      ID=<cfqueryparam value="#val(FORM.ID)#" cfsqltype="CF_SQL_INTEGER"/>
    </cfquery>

    As to the actual question of why nothing is updating, look at the contents for FORM.ID. If this value is not what you expect, SQL won't tell you because if the where clause fails, it is still valid socfquery won't tell you. In place of the cflocation tag, substitute:

    <cfdump var="#FORM#" label="FORM" />

    <cfdump var="#qUpdate#" label="qUpdate" /> <!--- make sure to name your query --->

    D
    Dan_Bracuk
    Inspiring
    December 12, 2011

    <cfquery name="theName" result="theResult" datasource="whatEver">

    sql goes here

    </cfquery>

    You can't dump theName, but you can dump theResult.  At least that's what I observed when I tried it.

    S
    Steve Sommers
    Legend
    December 12, 2011

    Strange. I always use "name" without issue. In fact, I never knew "result" existed.

    BreakawayPaul
    BreakawayPaul
    Inspiring
    December 12, 2011

    It seems like #FORM.Page_URL# would be text, so it would probably have to have single quotes around it, no?  In fact any of the text items should have single quotes I think.

    But instead of that, they should of course be wrapped in cfqueryparam tags.

    D
    Dan_Bracuk
    Inspiring
    December 12, 2011

    First, comment out the cflocation tag.  You need to stay on the current page to figure this out.  Next, make sure debugging is turned on so you can see any sql that gets generated.

    I usually troubleshoot if/else problems like this:

    if it's what I expect,

    output yes

    else

    output no

    output what it actually was.

    In your particular case, <cfdump var="#form#"> would probably fit in nicely before any other code.

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices