Hello,
We're running CFMX 6.1 on Win2003 Servers with Oracle 8.0.3 DB on UNIX. This is bad for me so I'm offering the maximum points.
We have free projects available on our website in exchange for a consumer signing up for our email newsletter. Somewhere along the way between say almost 2 years ago and just recently I made some kind of change that is allowing duplicate email names to be entered although the CFQUERY is supposed to check to see if the email_address is already in the database. The weird thing is that I see the time of creation is so close together that it seems like the consumer couldn't have clicked the submit button that many times so quickly, for example:
EMAIL_NEWS_LIST_ID EMAIL_ADD TO_CHAR(DATE
------------------ --------------------------------------------- -----------
150382 anonymous@yahoo.com 15:40:39 PM
150383 anonymous@yahoo.com 15:40:40 PM
150384 anonymous@yahoo.com 15:40:42 PM
150385 anonymous@yahoo.com 15:40:41 PM
150386 anonymous@yahoo.com 15:40:47 PM
When I just go to the page on my website and try to access the project by giving it my email address it does the right thing which is to check that I'm in the DB and since I am already there it does not add me again but just puts the cookie on my machine so that I can view the projects.
Here is my CF code.
<cfset date_created = CreateODBCDateTime(now())>
<!--- first check to see if the emailAdd from the form is in the DB --->
<cfif IsDefined("Form.emailAdd")>
<cfset email_Add=Lcase(Form.emailAdd)>
<cfquery datasource="#secondaryDS#" name="checkEmail">
select email_news_list_id, email_add
from email_list
where lower(email_add)=lower('#Form.emailAdd#')
</cfquery>
</cfif>
<!--- if emailAdd is in DB set status to 1 and cflocate to page --->
<cfif Form.emailAdd EQ checkEmail.email_add>
<cfcookie name="inEmailList" value="Y" expires="never">
<cflocation url="index.cfm?page=section/classroom/sewprojects/EraBonnet/eraBonnet.cfm">
<cfelse>
<!--- otherwise set emailStatus to 2 and add to DB, this will trigger proper message below --->
<cfset emailStatus = 2>
<cfset email_Add = "#Form.emailAdd#">
<cftransaction>
<cfquery name="addToEmailList" datasource="#secondaryDS#">
declare nk int;
begin
update next_key
set next_key = next_key + 1
where table_name = 'email_list';
select next_key + 1 into nk
from next_key
where table_name = 'email_list';
insert into email_list(
email_news_list_id,
email_add,
first_name,
last_name,
spanish_lang,
date_created,
promo_code
)
values(
nk,
'#Lcase(email_add)#',
'#first_name#',
'#last_name#',
'1',
#date_created#,
#promo_code#
);
end;
</cfquery>
</cftransaction>
<cfcookie name="inEmailList" value="Y" expires="never">
<!-- Run email reply -->
<cfmail from="Newsletter@anonymous.com" to="#email_Add#" subject="Anonymous Co." server="#WEBMAIL_EMAIL_SERVER#">
Hello #email_Add#
Welcome to our newsletter.
</cfmail>
<cflocation url="index.cfm?page=section/classroom/sewprojects/EraBonnet/eraBonnet.cfm" addtoken="no">
</cfif>
_______________________
Many thanks in advance,
Jerry
by: shooksmPosted on 2006-09-26 at 12:32:06ID: 17604736
Sounds like someone hitting refresh on a slow loading page after submitting the form.
I would recomend putting control of the duplicates into the hands of your database by removing the email_news_list_id column and making the email_add column your primary key. That way, it is physically impossible to insert duplicate emails. You code would also get a lot smaller and you don't have to have two trips to the database to accomplish.
<cfset date_created = CreateODBCDateTime(now())>
<cfset emailStatus = 2>
<cfset email_Add = "#Form.emailAdd#">
<cftry>
<cftransaction>
<cfquery name="addToEmailList" datasource="#secondaryDS#"
insert into email_list(
email_add,
first_name,
last_name,
spanish_lang,
date_created,
promo_code
)
values(
'#Lcase(email_add)#',
'#first_name#',
'#last_name#',
'1',
#date_created#,
#promo_code#
);
</cfquery>
</cftransaction>
<!--- there is probably a more specific error to trap for duplicates then using type="any". try dumping out CFERROR when entering a duplicate to see it --->
<cfcatch type="any">
<!--- looks like there was an error inserting the data, set their cookie and send them away --->
<cfcookie name="inEmailList" value="Y" expires="never">
<cflocation url="index.cfm?page=sectio
</cfcatch>
</cftry>
<!--- if I got this far, there was no problems inserting the email so send them a success message and redirect --->
<cfcookie name="inEmailList" value="Y" expires="never">
<!-- Run email reply -->
<cfmail from="Newsletter@anonymous
Hello #email_Add#
Welcome to our newsletter.
</cfmail>
<cflocation url="index.cfm?page=sectio