cfupdate and SQL update looking for submit field!
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 | |
|---|---|
| EAPPROVED | 1 |
| EAPPROVEDID | 1 |
| ECOST | 0.00 |
| ECOSTO | Free |
| EDESCRIPTION | event name descri |
| EGENDESCRIPTION | gen descritpoin |
| ELOCATION | bedford house |
| ENDD | {d '2010-02-02'} |
| ENDT | {t '14:02:00'} |
| EPRIVCOM | private notations |
| EPUBCOM | public notations |
| EREGCALL | 866 |
| ETITLE | not sup group |
| ETYPE | DSME |
| EVENTID | 34 |
| FIELDNAMES | EVENTID,USERID,STARTD,STARTT,XMONTH,EGENDESCRIPTION,ENDD,ENDT,ETYPE,ETITLE,EDESCRIPTION,ECOSTO,ECOST,SPONSORID,ELOCATION,EREGCALL,EPUBCOM,EPRIVCOM,EAPPROVED,EAPPROVEDID,SUBMIT |
| MOREDESCRIPTION | 0 |
| SPONSORID | 12 |
| STARTD | {ts '2010-01-01 00:00:00'} |
| STARTT | {t '13:01:00'} |
| SUBMIT | Update Event Listing |
| USERID | 1 |
| XMONTH | 1 |
| XYEAR | 0 |
Any help would be GREATLY appreciated. My apologies for the super long opening thread.
