Solved

Form Action page error

Posted on 2010-11-16
12
329 Views
Last Modified: 2012-05-10
I'm having a problem with a form submission. I'm currently using conditional logic in an "action page" to update or insert a form. When I hit submit I get an error message stating " The CREATIONDATE fieldname cannot be found in the leads table."

The problem is that I'm not updating the "CreationDate" input into the the Leads table (see attached code). It's my understanding that using cfupdate and formfields that what you put under the formfields variables is what gets updated or inserted. I do have a input tag with the name of CreationDate in the form, but again it is not in the code to be inserted so why is Coldfusion trying to update the field?

Any help would be greatly appreciated :)
<<<<<<<<<<<<<<PAGE FORM SENDS TO>>>>>>>>>>>>>>>>>



<!--- Insert or update? --->

<cfset EditMode=IsDefined("FORM.LeadID")>



<cfif EditMode>

 <!--- Update Lead --->

 <cfupdate datasource="#application.datasource#"

          tablename="leads"

          formfields="FirstName,

          				LastName,

                        Secondary_first,

                        Secondary_last,

                        Company,

                        HomePhone,

                        WorkPhone,

                        MobilePhone,

                        OtherPhone,

                        OtherPhoneLabel,

                        AltPhone,

                        AltPhoneLabel,

                        EmailAddress,

                        SecondEmail,

                        Website,

                        PropertyAddress,

                        City,

                        State,

                        ZipCode">

 <cfset action="UPDATED">

   <cflocation url="Lead_Modifier.cfm?LeadID=#FORM.LeadID#"> 

<cfdump var="UPDATE COMMAND">

<cfelse>

 <!--- Add Lead --->

 <cfinsert datasource="#application.datasource#"

          tablename="leads"

          formfields="FirstName,

          				LastName,

                        Secondary_first,

                        Secondary_last,

                        Company,

                        HomePhone,

                        WorkPhone,

                        MobilePhone,

                        OtherPhone,

                        OtherPhoneLabel,

                        AltPhone,

                        AltPhoneLabel,

                        EmailAddress,

                        SecondEmail,

                        Website,

                        PropertyAddress,

                        City,

                        State,

                        ZipCode">

 <cfset action="ADDED">

  <!--- Get ID number of just-inserted lead--->

  <cfquery datasource="#application.datasource#" name="getNewLead">

   SELECT Max(LeadID) As NewID FROM leads

  </cfquery>

  

  <!--- Insert Lead Tracking--->

  <cfquery name ="addRecord" dataSource="#application.datasource#"> 

	   INSERT INTO lead_tracking (LeadID,Lead_SourceID,Lead_typeID,Lead_priorityID,CreationDate)

       VALUES (<cfqueryparam value="#getNewLead.NewID#" cfsqltype="cf_sql_varchar">,'#FORM.Lead_SourceID#','#FORM.Lead_typeID#','#FORM.Lead_priorityID#',#CreateODBCdate(FORM.CreationDate)#)

 </cfquery>

 

</cfif>



 <cflocation url="Lead_Modifier.cfm">

Open in new window

0
Comment
Question by:jasch2244
  • 5
  • 4
  • 3
12 Comments
 
LVL 52

Expert Comment

by:_agx_
ID: 34150246
Your understanding of how it *should* work is correct.  I do recall a few bugs with cfupdate/cfinsert. But I think those applied to columns with similar names. Like if you had "CreationColumn" and "CreationDateColumn". It doesn't seem to apply in your case.

Is the CREATIONDATE column mandatory maybe?


>> <!--- Get ID number of just-inserted lead--->
>>  <cfquery datasource="#application.datasource#" name="getNewLead">
>>    SELECT Max(LeadID) As NewID FROM leads
>>   </cfquery>

That's not thread safe.  If you're using MS SQL, MySQL, etc... use the cfquery result attribute instead.
 
0
 
LVL 11

Expert Comment

by:Brijesh Chauhan
ID: 34151685
Do you get the same error if you using cfquery for insert and updates ?

Also, if you use cfquery, then you can eliminate the below query

 <!--- Get ID number of just-inserted lead--->
  <cfquery datasource="#application.datasource#" name="getNewLead">
   SELECT Max(LeadID) As NewID FROM leads
  </cfquery>

Just use the result attribute of cfquery tag,

<cfquery datasource="#application.datasource#" result = "whatever">
        -------- Query Insert SQL here ----------
</cfquery>

depending on your database, you can simply say to get the ID

SQL Server: whatever.IDENTITYCOL
MySQL: whatever.GENERATED_KEY
Sybase: whatever.SYB_IDENTITY
Informix: whatever.SERIAL_COL
Oracle: whatever.ROWID


0
 
LVL 1

Author Comment

by:jasch2244
ID: 34151770
Thanks all:
Seems to be the problem lie's in the update only.. the insert works perfectly. Thanks on the generated key / results idea... I still don't get how to use it in this instance as when I use cfquery for MySQL update or insert I'm getting some date issue even if I use CreateODBC date function. I know sounds goofy so I resulted to what I knew would work for the situation. Thank you for the suggestion though :)

Do you think DateCreated is some reserved name for a function? _agx not sure what you mean by mandatory. I don't think it is as it's just another input tag like the rest of the formfields. But it's not in the update or insert commands so I don't know why it would be glitching.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 34152076
If you're having this much trouble with cfupdate, I'd probably switch to a regular UPDATE instead.

>> when I use cfquery for MySQL update or insert I'm getting some date
>> issue even if I use CreateODBC date function

    What issues? Can you post the code you tried, and the error message? Assuming you're
    updating a "datetime" column,  you shouldn't have any problem with createODBCDate()
    OR cfqueryparam for that matter.

>> not sure what you mean by mandatory

      I mean does it allow you to leave it empty (or null)?  

_agx_ wrote:
> If you're using MS SQL, MySQL, etc... use the cfquery result attribute instead.
jasch2244 wrote:
>>> Thanks on the generated key / results idea... I still don't get how to use it in
>>> this instance


   Obviously it's only for inserts, and only for regular INSERT's. You can't use it with CFINSERT.
   But it's much safer than using SELECT MAX(...).  That will return the wrong ID under high load.
   Here's a simple example

   <cfquery name="addRecord" result="insertedRecord" datasource="...">
          INSERT INTO YourTable ( SomeColumn )
          VALUES ( 'something' )
    </cfquery>

    <cfoutput>The new record id is #insertedRecord.GENERATED_KEY#</cfoutput>

0
 
LVL 52

Accepted Solution

by:
_agx_ earned 500 total points
ID: 34152103
>> I do have a input tag with the name of CreationDate in the form

Oh wait ... I thought you said you DON'T have a field with by name.  

1) May as well try removing it and see if it works then.
2) Also, the docs say:

      "The formFields list must include the database table primary key field, which
       must be present in the form. It can be hidden..."

I don't see FORM.leadID in your field list. So you might add it and give that a try too.
0
 
LVL 11

Expert Comment

by:Brijesh Chauhan
ID: 34152298
if it's a problem with update, then use CFABORT at line 31, so that the system does not CHECK the rest of the code.
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 11

Expert Comment

by:Brijesh Chauhan
ID: 34152335
For Update, you need the primary key of the table to be there, basically it needs to know which ID to update...
0
 
LVL 52

Expert Comment

by:_agx_
ID: 34152365
>> so that the system does not CHECK the rest of the code

Sounds like it's a runtime (not compile) error, so that wont' have any effect. ie It'll just error out the same as it's doing now.
0
 
LVL 1

Author Closing Comment

by:jasch2244
ID: 34152403
Thanks again _agx
0
 
LVL 1

Author Comment

by:jasch2244
ID: 34152405
I needed the pk (LeadID) in the formfield (cfupdate) which is weird due to the fact that I have it already as a hidden input tag.. should have picked it up.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 34152498
>> should have picked it up.

Yes but using "formfields" tells CF to ignore everything else in the FORM scope. So I guess if you want it to "see"  the LeadID field, you have to put it in the list.
0
 
LVL 1

Author Comment

by:jasch2244
ID: 34152557
Indeed! Thanks for the GeneratedKey tip I was able to convert from cfinput to SQL INSERT and used the result.GNERATEDKEY to pull the most recent insert. Thank you, thank you.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

This article  is about submitting  form through  ColdFusion.Ajax.submitForm to the action page and send a response back in JSON format which later can be decoded using ColdFusion.JSON.decode. By this way you can avoid the usual page refresh for subm…
Hi, Even though I have created this Tutorial on My personal Blog, Some people might not able to find my website, So here i am posting it again Today, from the topic it is very clear that i will be showing you here the very basic usage of how we …
I designed this idea while studying technology in the classroom.  This is a semester long project.  Students are asked to take photographs on a specific topic which they find meaningful, it can be a place or situation such as travel or homelessness.…
A company’s greatest vulnerability is their email. CEO fraud, ransomware and spear phishing attacks are the no1 threat to a company’s security. Cybercrime is responsible for the largest loss of money to companies today with losses projected to r…

930 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

11 Experts available now in Live!

Get 1:1 Help Now