<cfinsert> (coldfusion) fails to insert record in SQL database.

I tweeted this to SQL guru, Ben Forta, who advised me to use a formfields attribute. On the surface, this seemed to work; I received no error messages. However, the record was not actually inserted. Is there an expert that can help with this issue.
Gregory AmbroseAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
without further info, like posted your sample scripts here, it's very difficult for experts to provide suggestions based on imagination.
Gregory AmbroseAuthor Commented:
The form is here: http://www.gregoryambrose.com/under%20god/sign-up.cfm

Code for the form page and the subsequent form is in the attached file.Ben-Forta.txt
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
<!---<cfquery name="church_results" datasource="churches"  > //----- Query alternative to <CFINSERT>  -----//
       INSERT INTO churches(Organization, Pastor, AddrLn1, AddrLn2, City, State, ZipCode, Contact, Contact_email, HostEvent, UseVideo, SignDeclr, ConductRegistr, Comments)  
      VALUES('#form.Organization#', '#form.Pastor#', '#form.AddrLn1#', '#form.AddrLn2#','#form.city#', '#form.state#', '#form.zipCode#', '#form.contact#', #form.contact_email#, #form.HostEvent#, #form.UseVideo#,#form.SignDeclr#, #form.ConductRegistr#, '#form.comments#');

      </cfquery> --->

it seems that you have commented out the <cfquery> tag? what if you remove the comment and run it, what error did you get?

you need to remove the <!--- and --->
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
and to remove the <CIFINSERT> tag as well, hence try like:
<cfquery name="church_results" datasource="churches"  > 
       INSERT INTO churches(Organization, Pastor, AddrLn1, AddrLn2, City, State, ZipCode, Contact, Contact_email, HostEvent, UseVideo, SignDeclr, ConductRegistr, Comments)  
      VALUES('#form.Organization#', '#form.Pastor#', '#form.AddrLn1#', '#form.AddrLn2#','#form.city#', '#form.state#', '#form.zipCode#', '#form.contact#', #form.contact_email#, #form.HostEvent#, #form.UseVideo#,#form.SignDeclr#, #form.ConductRegistr#, '#form.comments#');
      </cfquery>

Open in new window

Gregory AmbroseAuthor Commented:
The <cfquery> was my original approach. I commented it out to try the <cfinsert>

The <cfquery> produces the error in the attached file (partially quoted.Ben-Forta2.txt
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
>> ...  'Gregory Ambrose', greg@gregoryambrose.com, 0, 1,1, 0, '');

you need to add the single quotes for the email address...

so try:

<cfquery name="church_results" datasource="churches"  >
       INSERT INTO churches(Organization, Pastor, AddrLn1, AddrLn2, City, State, ZipCode, Contact, Contact_email, HostEvent, UseVideo, SignDeclr, ConductRegistr, Comments)  
      VALUES('#form.Organization#', '#form.Pastor#', '#form.AddrLn1#', '#form.AddrLn2#','#form.city#', '#form.state#', '#form.zipCode#', '#form.contact#', '#form.contact_email#', #form.HostEvent#, #form.UseVideo#,#form.SignDeclr#, #form.ConductRegistr#, '#form.comments#');
      </cfquery>
Gregory AmbroseAuthor Commented:
That ran without producing any errors. However, the record was not added to the database.
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
1. did you remove <cfinsert> before the <cfquery> ?

>><cfinsert datasource="churches" tablename="churches"  formfields="Organization, Pastor, AddrLn1, AddrLn2, City, State, ZipCode, Contact, Contact_email, HostEvent, UseVideo, SignDeclr, ConductRegistr, Comments">

2. if you do a <cfoutput>, can you see if it's a valid insert SQL statement generated? (temporarily remove the <cflocation url="index.cfm"> as well so there will be no redirection after the insertion)

<cfoutput>
INSERT INTO churches(Organization, Pastor, AddrLn1, AddrLn2, City, State, ZipCode, Contact, Contact_email, HostEvent, UseVideo, SignDeclr, ConductRegistr, Comments)  
	VALUES('#form.Organization#', '#form.Pastor#', '#form.AddrLn1#', '#form.AddrLn2#','#form.city#', '#form.state#', '#form.zipCode#', '#form.contact#', '#form.contact_email#', #form.HostEvent#, #form.UseVideo#,#form.SignDeclr#, #form.ConductRegistr#, '#form.comments#');
</cfoutput>

Open in new window


can you copy the generated SQL and do a test by run the SQL statement above manually?
Gregory AmbroseAuthor Commented:
Yes.
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
so, i guess you answered yes for both questions I had asked above?

can you post what's the Insert SQL statement that generated as the output? is it the same as:
INSERT INTO churches(Organization, Pastor, AddrLn1, AddrLn2, City, State, ZipCode, Contact, Contact_email, HostEvent, UseVideo, SignDeclr, ConductRegistr, Comments) VALUES('N/A', 'Father Gregory', '182 Moseley St.', '','Elgin', 'Illinois', '60123', 'Gregory Ambrose', 'greg@gregoryambrose.com', 0, 1,1, 0, '');

Open in new window

?

do you have any trigger for table churches when a new record was inserted?
Gregory AmbroseAuthor Commented:
This is the output generated by the <cfoutput> tag.

I have set no triggers and, frankly, I don't know haw or what purpose they serve.

INSERT INTO churches(Organization, Pastor, AddrLn1, AddrLn2, City, State, ZipCode, Contact, Contact_email, HostEvent, UseVideo, SignDeclr, ConductRegistr, Comments) VALUES('N/A', 'Father Gregory', '182 Moseley St.', '','Elgin', 'Illinois', '60123', 'Gregory Ambrose', 'greg@gregoryambrose.com', 1, 1,0, 0, 'SQL Test.');
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
it's weird if the SQL is valid, and it's run, but no record was generated. I just went to the site and test, it showed up with the generated SQL statement...

you may restore your scripts when necessary, but what is the ideal scripts would be similar to below (as already mentioned):

sign-upaction.cfm:
<cfparam name="Form.HostEvent" default= 0 >
<cfparam name="Form.useVideo" default= 0 >
<cfparam name="Form.SignDeclr" default= 0 >
<cfparam name="Form.ConductRegistr" default= 0 >


<cfmail 
from = "gregoryambrose@1Undergod.com"
<!--- to = "pcaprio1@yahoo.com" ---> 
to ="greg@gregoryambrose.com"  
subject = "Protect Religious Freedom Program Sign-up"
server = "mail.1undergod.com"
username="gregoryambrose@1undergod.com"
cc = "greg@gregoryambrose.com"
password="*******" 
port = "8889" >
Voter  Registration Project Sign-up from #form.Organization#

#form.Pastor#  IP: #cgi.remote_addr#
#form.AddrLn1#
#form.AddrLn2#
#form.City#
#form.State# #form.ZipCode#
//---------------------------------------------------------//
Contact: #form.Contact# 
Contact Email: #form.Contact_email# 
//---------------------------------------------------------//

#form.HostEvent# We will Have Religious Freedom Sunday at this Church.
    
#form.UseVideo# We will use the David Barton Video on Religious Freedom.

#form.SignDeclr# We will Conduct a Voter Registration Sunday at this Church.

#form.ConductRegistr#  We will Sign the Declaration of Religious Independence

#form.Comments#

</cfmail>
 
<cfquery name="church_results" datasource="churches"  >
	 INSERT INTO churches(Organization, Pastor, AddrLn1, AddrLn2, City, State, ZipCode, Contact, Contact_email, HostEvent, UseVideo, SignDeclr, ConductRegistr, Comments)  
	VALUES('#form.Organization#', '#form.Pastor#', '#form.AddrLn1#', '#form.AddrLn2#','#form.city#', '#form.state#', '#form.zipCode#', '#form.contact#', '#form.contact_email#', #form.HostEvent#, #form.UseVideo#,#form.SignDeclr#, #form.ConductRegistr#, '#form.comments#');
	</cfquery>

<cflocation url="index.cfm">

Open in new window


side note for SQL triggers...

CREATE TRIGGER (Transact-SQL)
https://msdn.microsoft.com/en-sg/library/ms189799.aspx
Gregory AmbroseAuthor Commented:
I replaced the entire Action code with the code you provided in your last comment.

I received the generated email but, again no record was inserted in the database.

I truly appreciate all of you help but don't begin to understand why thing are failing.
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
what if you putting the <cfquery> in front of <cfmail> and remove the <cflocation>

will the record being inserted? if necessary, backup and clear the table: churches

and... are you pointing to the correct database?
Gregory AmbroseAuthor Commented:
Hey, Ryan,

I guess I misinterpreted your instructions about using the generated output to manually insert the record.

I did that now using that SQL as a new query in the SQL Management Studio Express.

I get the following error:
//--------------------------------------------------//
Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'ID', table 'thechurches.dbo.churches'; column does not allow nulls. INSERT fails.
The statement has been terminated.

I originally created the database in MS Access and upsized to SQL. The field/columnID 'ID' is an automatically incremented record number/index in the database. Now, I don't know how to create such a field/index in SQL directly. Do I simply remove the column from the table, Does SQL automatically generate a record number if I delete 'ID' ??
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
>> Does SQL automatically generate a record number if I delete 'ID' ??
if you have a field and set as an Auto Increment Number, then the database will take care of it, and usually it will start with 1.

to create an auto increment number (usually as the primary key) in MS SQL, try this... (you can do it via SQL Management Studio Express without any SQL scripts)

in your field properties, try look for these attributes and change accordingly.

(image copied from Internet)
configure auto number field
Gregory AmbroseAuthor Commented:
I think I'm finally on the right track here but I had to delet the databas and am now recreating it.

In MS Access I created the four fields that are checkboxes on the form. This caused problems in SQL with errors showing up regarding conversion of the (Access) Yes/No convention to the bit data that was specified in SQL. Is there a Yes/No convention that I can use in SQL or must I declare these as integers?
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
>>Is there a Yes/No convention that I can use in SQL or must I declare these as integers?
in MS SQL, you can define a Yes/No as a BIT data type.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gregory AmbroseAuthor Commented:
Ryan,

I can't tell you how much I appreciate all of your help and patience with me in dealing with my issue. Things now seem to work fine. You definitely saved me from considerable embarrassment on this project

Thank you.
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
no worries and glad that I could make helps here! we either learned during our working hours or else it's even better if someone is willing to guide through the process.

cheers = )
Gregory AmbroseAuthor Commented:
It took quite a bit of back and forth but Ryan was definitely able to solve my problem.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.