Hello,
We're running CFMX 6.1 on Win2003 Servers with Oracle 8.0.3 DB on UNIX.
We have free projects available on our website in exchange for a consumer
signing up for our email newsletter. Somewhere along the way between 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 [EMAIL PROTECTED] 15:40:39 PM
150383 [EMAIL PROTECTED] 15:40:40 PM
150384 [EMAIL PROTECTED] 15:40:42 PM
150385 [EMAIL PROTECTED] 15:40:41 PM
150386 [EMAIL PROTECTED] 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. The code checks to
see if the email_add is in the DB already and is NOT supposed to enter it again
but just CFLOCATE them to the page they want to view.
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 email_Add = "#Form.emailAdd#">
<cftransaction>
<cfquery name="addToEmailList" datasource="#secondaryDS#">
<!--- this selects the email_news_list_id from a next_key table
--->
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="[EMAIL PROTECTED]" 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>
The major sticking point is that I don't understand HOW and WHY this is
happening. I have tens of thousands of email addresses where - at least by the
naked eye - it appears that I don't have any doubles. Yet starting around
August 21, 2006 these duplicates are multiplying. Due to my lack of
organization - and not always using custom tags - some of the code pages as
presented above are duplicates whereas the newer ones are using the custom
tags. Regardless, it appears that I am getting multiple entries for the same
submit.
I cannot really change the underlying database table because it's used to
support another application (Campaign8) that has it's own requirements which
are met by the current table.
Thanks much,
Jerry
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four
times a year.
http://www.fusionauthority.com/quarterly
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:254389
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
