If I was using sql server, I would do something like
Declare @myid int
Select @myid=email_news_list_id from emailtable where
email_add='#form.email_add#'
If @myid is NULL
Begin
Insert into emailtable (email_add) values ('#form.email_add#')
Set @myid=SCOPE_IDENTITY()
End
Select @myid as EMAIL_NEWS_LIST_ID
> -----Original Message-----
> From: loathe [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, September 27, 2006 11:37 AM
> To: CF-Talk
> Subject: RE: how can query input to DB 4 times in 1 second
>
> 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:254402
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
