Might also want to trim those form values before comparing them. That's
certainly caused issues for me in the past.
However, I think you've got a bot spamming those posts. Adding the lock, or
expanding the transaction to include the email check might solve your
problem.
There are also plenty of resources out there on how to try to outsmart the
bot posts.
Good Luck
On 9/27/06, Russ <[EMAIL PROTECTED]> wrote:
>
> 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:254500
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
