0 Replies Latest reply on Sep 27, 2006 8:41 AM by Mallethead

    Multiple DB inserts for 1 Submit


      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:

      ------------------ --------------------------------------------- -----------
      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. 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#')

      <!--- 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.
      <!--- otherwise set emailStatus to 2 and add to DB, this will trigger proper message below
      <cfset email_Add = "#Form.emailAdd#">
      <cfquery name="addToEmailList" datasource="#secondaryDS#">
      <!--- this selects the email_news_list_id from a next_key table --->

      declare nk int;


      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(
      <cfcookie name="inEmailList" value="Y" expires="never">

      <!-- Run email reply -->
      <cfmail from="Newsletter@anonymous.com" to="#email_Add#" subject="Anonymous Co." server=
      Hello #email_Add#

      Welcome to our newsletter.


      <cflocation url="index.cfm?page=section/classroom/sewprojects/EraBonnet/eraBonnet.cfm" addtoken=

      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,