Skip to main content
July 16, 2010
Question

cfupdate and SQL update looking for submit field!

  • July 16, 2010
  • 1 reply
  • 2603 views

Help! I've got a form that passes information to the DB for adding/updating events. When I submit for a new entry I have no problems. When I edit that same record I get an error: "The SUBMIT fieldname cannot be found in the event_table table." I've checked to make sure all fieldnames are legal and that MySQL fields aren't expecting max of say 4 chars but I'm submitting 8. Also checked I'm not sending mismatched data, like date to varchar type.

On the entry form, all my variables are set based on whether this is a add or update. I've tried both cfupdate (with and without formfields attribute) and cfquery and get the same error message. When I did cfdump I see all the variables I want in my form coming across. I even cfparam to make sure every checkbox and date and time are given default values.

My guess is a field is being skipped and therefore database is somehow thinking it should receive SUBMIT fieldname data even though I'm explicitly omitting it from any cfupdate or SQL update code.

Here is my entry form with extraneous formatting removed:

<!--- determine if insert or update --->
<cfif IsDefined("URL.eID")>
<cfset editRecord = "yes">
<!--- update --->
<!--- get all info for record --->
<cfquery datasource="WebAppDB" name="getE">
SELECT *
FROM event_table
WHERE eventID = #URL.eID#;
</cfquery>
<!--- find last approval admin --->
<cfquery datasource="WebAppDB" name="approveFact">
SELECT utable.uFName + utable.uLName AS ApprovalAdmin
FROM utable
WHERE utable.uID = #getE.eApprovedID#;
</cfquery>
<cfset startD = getE.startD>
    <cfset startT = getE.startT>
<cfset xMonth = Trim(getE.xMonth)>
    <cfset xYear = Trim(getE.xYear)>
    <cfset moreDescription = Trim(getE.moreDescription)>
    <cfset eGenDescription = TRIM(getE.eGenDescription)>
    <cfset endD = getE.endD>
    <cfset endT = getE.endT>
    <cfset eType = Trim(getE.eType)>
    <cfset eTitle = Trim(getE.eTitle)>
    <cfset eDescription = Trim(getE.eDescription)>
    <cfset eCost = Trim(getE.eCost)>
    <cfset eCostO = Trim(getE.eCostO)>
    <cfset sponsorID = Trim(getE.sponsorID)>
    <cfset eLocation = Trim(getE.eLocation)>
    <cfset eRegCall = Trim(getE.eRegCall)>
    <cfset ePubCom = TRIM(getE.ePubCom)>
    <cfset ePrivCom = TRIM(getE.ePrivCom)>
    <cfset eApproved = getE.eApproved>
    <cfset eApprovedID = approveFact.ApprovalAdmin>
<cfset buttonSee = "Update Event Listing">
<cfset pageTitle = "Edit Event Listing">
<!--- insert --->
<cfelse>
<cfset editRecord = "No">
<cfset startD = "">
    <cfset startT = "">
<cfset xMonth = "">
    <cfset xYear = "">
    <cfset moreDescription = "">
    <cfset eGenDescription = "">
    <cfset endD = "">
    <cfset endT = "">
    <cfset eType = "">
    <cfset eTitle ="">
    <cfset eDescription = "">
    <cfset eCost = "">
    <cfset eCostO = "">
    <cfset sponsorID = "">
    <cfset eLocation = "">
    <cfset eRegCall = "">
    <cfset ePubCom = "">
    <cfset ePrivCom = "">
    <cfset eApproved = "">
    <cfset eApprovedID = "">
<cfset buttonSee = "Add Event Listing">
<cfset pageTitle = "Add an Event Listing">
</cfif>

<!--- get hospitals --->
<cfquery datasource="WebAppDB" name="sponsors">
SELECT wpID, wpTitle
FROM wp_table
ORDER BY wpTitle;
</cfquery>

<cfform name="eventForm" method="post" action="eventHandler.cfm">

<cfif editRecord IS "yes">
<cfinput type="hidden" name="eventID" value="#URL.eID#">

</cfif>
<cfinput type="hidden" name="userID" value="#SESSION.auth.contactID#">
        <div class="formLeft">Start Date:</div>
            <div class="formRight">
            <cfinput type="text" class="formTextBox" style="width:115px;" name="startD" value="#DateFormat((startD), "MM/DD/YY")#" size="12" validateat="onsubmit" mask="99/99/99"> MM/DD/YY</div>
        <div class="formLeft">Start Time:</div>
            <div class="formRight">
            <cfinput type="text" class="formTextBox" style="width:115px;" name="startT" value="#TimeFormat((startT), "hh:mm tt")#" size="12" validateat="onsubmit" mask="99:99 AA"> HH:MM am/pm</div>
        <div class="formLeft">Occurrence:</div>
            <div class="formRight">
            <cfoutput><input type="checkbox" name="xMonth" value="1" <cfif xMonth IS "1">checked</cfif>> Offered several times a month<br />
         <input type="checkbox" name="xYear" value="1" <cfif xYear IS "1">checked</cfif>> Offered several times a year<br />
            <input type="checkbox" name="moreDescription" value="1" <cfif moreDescription IS "1">checked</cfif>></cfoutput> See description for more information.</div>
        <div class="formLeft">General Description:</div>
            <div class="formRight">
            <cftextarea name="eGenDescription" cols="30" rows="3" wrap="virtual" value="#TRIM(eGenDescription)#" message="Enter description, i.e. Classes are held every other Tuesday"></cftextarea>
            </div>
        <div class="formLeft">End Date:</div>
            <div class="formRight">
   <cfinput class="formTextBox" style="width:115px;" type="text" name="endD" value="#DateFormat((endD), "MM/DD/YY")#" size="12" mask="99/99/99"> MM/DD/YY</div>
        <div class="formLeft">End Time:</div>
            <div class="formRight">
   <cfinput class="formTextBox" style="width:115px;" type="text" name="endT" value="#TimeFormat((endT), "hh:mm tt")#" size="12" mask="99:99 AA"> HH:MM am/pm</div>
        <div class="formLeftReq">Event Type:</div>
            <div class="formRight">
            <cfoutput><input type="radio" name="eType" value="DSME" <cfif eType IS "DSME">checked</cfif>> DSME<br />
            <input type="radio" name="eType" value="DE" <cfif eType IS "DE">checked</cfif>> Diabetes Education<br />
            <input type="radio" name="eType" value="SG" <cfif eType IS "SG">checked</cfif>> Support Group<br />
            <input type="radio" name="eType" value="Other" <cfif eType IS "Other">checked</cfif>></cfoutput> Other
            </div>
        <div class="formLeftReq">Event Name:</div>
            <div class="formRight">
            <cfinput class="formTextBoxReq" style="width:180px;" type="text" name="eTitle" value="#eTitle#" size="30" maxlength="255"> If Other<br />
            <cftextarea name="eDescription" value="#TRIM(eDescription)#" cols="20" rows="3" message="Optional: Enter additional descriptive text." wrap="virtual"></cftextarea></div>
         <div class="formLeftReq">Cost:</div>
            <div class="formRight">
            <cfoutput><input type="radio" name="eCostO" value="Free" <cfif eCostO IS "Free">checked</cfif>> Free<br />
            <input type="radio" name="eCostO" value="Ins" <cfif eCostO IS "Ins">checked</cfif>> Covered by Insurance<br />
   <input type="radio" name="eCostO" value="Other" <cfif eCostO IS "Other">checked</cfif>></cfoutput> Other<br />
   <cfinput class="formTextBox" style="width:50px;" type="text" name="eCost" value="#eCost#" size="5" maxlength="8">
        <div class="formLeftReq">Sponsor:</div>
            <div class="formRight">
            <select name="sponsorID">
    <cfoutput query="sponsors">
                 <option value="#wpID#" <cfif sponsorID IS sponsors.wpID>selected</cfif>>#wpTitle#</option>
                </cfoutput>
            </select>
            </div>
         <div class="formLeftReq">Location:</div>
            <div class="formRight">
            <cfinput class="formTextBox" style="width:200px;" type="text" name="eLocation" value="#eLocation#" size="15" maxlength="255">
            </div>
         <div class="formLeft">Registration Telephone:</div>
            <div class="formRight"><cfinput class="formTextBox" style="width:100px;" type="text" name="eRegCall" value="#eRegCall#" size="15" maxlength="16"> xxx-xxx-xxxx</div>
         <div class="formLeft">Public Comments:</div>
            <div class="formRight"><cftextarea name="ePubCom" value="#TRIM(ePubCom)#" rows="3" cols="30" wrap="virtual" message="If you have additional information about the event please enter it here."></cftextarea></div>
         <div class="formLeft">Comments to the Admins:</div>
            <div class="formRight"><cftextarea name="ePrivCom" value="#TRIM(ePrivCom)#" rows="3" cols="30" wrap="virtual" message="Do you have any comments about this event for the administrators?"></cftextarea></div>
        <!--- for admins to see only --->
        <cfif #SESSION.auth.uAccess# IS "admin">
         <div class="formLeft">Approved?</div>
            <div class="formRight"><cfoutput><input type="checkbox" name="eApproved" value="1" <cfif eApproved IS "1">checked</cfif>></cfoutput></div>
        <cfinput type="hidden" name="eApprovedID" value="#SESSION.auth.contactID#">
        </cfif>
         <div class="formLeft"></div>
            <div class="formRight">
            <cfinput type="submit" value="#buttonSee#" name="submit" validate="submitOnce">
            </div>

</cfform>

Here is my processing form with extraneous formatting removed:

<cfset FORM.startD = "#CreateODBCDate((startD))#">
<cfset FORM.startT = "#CreateODBCTime((startT))#">
<cfset FORM.endD = "#CreateODBCDate((endD))#">
<cfset FORM.endT = "#CreateODBCTime((endT))#">
<cfparam name="FORM.xMonth" default="0" type="boolean">
<cfparam name="FORM.xYear" default="0" type="boolean">
<cfparam name="FORM.moreDescription" default="0">
<cfparam name="FORM.eType" default="DSME">
<cfparam name="FORM.eCostO" default="Free">
<cfparam name="FORM.eApproved" default="0" type="boolean">
<cfparam name="FORM.eApprovedID" type="integer">
<cfdump var="#form#"><!--- so I can see what's coming across --->
<cfif NOT IsDefined("FORM.eventID")>
<!--- insert --->
    <cfinsert datasource="prideWebAppDB" tablename="event_table" formfields="userID, startD, startT, xMonth, xYear, moreDescription, eGenDescription, endD, endT, eType, eTitle, eDescription, eCost, eCostO, sponsorID, eLocation, eRegCall, ePubCom, ePrivCom, eApproved, eApprovedID">
<cfelse>
<!--- update --->
    <cfquery datasource="prideWebAppDB">
    UPDATE event_table
    SET userID=#FORM.userID#,
    startD=#CreateODBCDateTime(FORM.startD)#,
    startT=#CreateODBCTime(FORM.startT)#,
    xMonth=#Form.xMonth#,
    xYear=#FORM.xYear#,
    moreDescription='#FORM.moreDescription#',
    eGenDescription='#FORM.eGenDescription#',
    endD=#CreateODBCDate(FORM.endD)#,
    endT=#CreateODBCTime(FORM.endT)#,
    etype='#TRIM(FORM.eType)#',
    eTitle='#TRIM(FORM.eTitle)#',
    eDescription='#TRIM(FORM.eDescription)#',
    eCost=#FORM.eCost#,
    eCostO='#TRIM(FORM.eCostO)#',
    sponsorID='#FORM.sponsorID#',
    eLocation='#TRIM(FORM.eLocation)#',
    eRegCall='#TRIM(FORM.eRegCall)#',
    ePubCom='#TRIM(FORM.ePubcom)#',
    ePrivCom='#TRIM(FORM.ePrivCom)#',
    eApproved=#FORM.eApproved#,
    eApprovedID=#FORM.eApprovedID#
    WHERE eventID=#FORM.eventID#
    </cfquery>
    <cfupdate datasource="prideWebAppDB" tablename="event_table" formfields="userID, startD, startT, xMonth, xYear, moreDescription, eGenDescription, endD, endT, eType, eTitle, eDescription, eCost, eCostO, sponsorID, eLocation, eRegCall, ePubCom, ePrivCom, eApproved, eApprovedID">

What CFDUMP displays:

struct
EAPPROVED1
EAPPROVEDID1
ECOST0.00
ECOSTOFree
EDESCRIPTIONevent name descri
EGENDESCRIPTIONgen descritpoin
ELOCATIONbedford house
ENDD{d '2010-02-02'}
ENDT{t '14:02:00'}
EPRIVCOMprivate notations
EPUBCOMpublic notations
EREGCALL866
ETITLEnot sup group
ETYPEDSME
EVENTID34
FIELDNAMESEVENTID,USERID,STARTD,STARTT,XMONTH,EGENDESCRIPTION,ENDD,ENDT,ETYPE,ETITLE,EDESCRIPTION,ECOSTO,ECOST,SPONSORID,ELOCATION,EREGCALL,EPUBCOM,EPRIVCOM,EAPPROVED,EAPPROVEDID,SUBMIT
MOREDESCRIPTION0
SPONSORID12
STARTD{ts '2010-01-01 00:00:00'}
STARTT{t '13:01:00'}
SUBMITUpdate Event Listing
USERID1
XMONTH1
XYEAR0

Any help would be GREATLY appreciated. My apologies for the super long opening thread.

    This topic has been closed for replies.

    1 reply

    Inspiring
    July 16, 2010

    I'm a bit puzzled why you've got a <cfquery> doing an UPDATE, then a <cfupdate> immediately afterwards doing - what appears to be - the same thing?

    I can't see anything wrong with your <cfupdate>.  Perhaps don't waste time working out what's up and just stick with <cfquery>.

    Also: parameterise your queries; don't hard-code dynamic values into your SQL string.

    --
    Adam

    July 16, 2010

    Adam,

    Thanks for looking into this for me. I forgot to mention that the cfupdate was the original code. I just tossed it in there but forgot to keep the comment tags around it. I wanted anyone looking to compare what I had done, either way.

    Just to clarify, by this statement: "Also: parameterise your queries; don't hard-code dynamic values into your SQL string." Do you mean I should cfparam every form field that I send to the cfquery? So that I would first <cfparam name="xMonth " default="1"> for example and then in the SQL update do something like:

    SET xMonth='#xMonth#'

    Thanks for the help.

    Luke

    Inspiring
    July 16, 2010

    http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-7f6f.html

    --

    Adam