Why not just add a unique constraint to the row in the table, wrap the query in a try and catch and handle it that way? I mean, let DB 2 handle it you know what I mean?
Also, with the submissions being that close together I don't think it's a human, I'm thinking it's a bot of some sort submitting that form. > -----Original Message----- > From: Gerald Weir [mailto:[EMAIL PROTECTED] > Sent: Wednesday, September 27, 2006 11:17 AM > To: CF-Talk > Subject: how can query input to DB 4 times in 1 second > > 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:254395 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
