<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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
The <cfquery> produces the error in the attached file (partially quoted.Ben-Forta2.txt
>> ... '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
</cfquery>
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
</cfquery>
ASKER
That ran without producing any errors. However, the record was not added to the database.
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)
can you copy the generated SQL and do a test by run the SQL statement above manually?
>><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>can you copy the generated SQL and do a test by run the SQL statement above manually?
ASKER
Yes.
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:
do you have any trigger for table churches when a new record was inserted?
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, '');do you have any trigger for table churches when a new record was inserted?
ASKER
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',
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',
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:
side note for SQL triggers...
CREATE TRIGGER (Transact-SQL)
https://msdn.microsoft.com/en-sg/library/ms189799.aspx
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">side note for SQL triggers...
CREATE TRIGGER (Transact-SQL)
https://msdn.microsoft.com/en-sg/library/ms189799.aspx
ASKER
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.
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.
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?
will the record being inserted? if necessary, backup and clear the table: churches
and... are you pointing to the correct database?
ASKER
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'
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' ??
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'
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' ??
>> 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)
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)
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
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 = )
cheers = )
ASKER
It took quite a bit of back and forth but Ryan was definitely able to solve my problem.
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 --->