inserting user email address only if it doesn't alreadyexist...

[P_Roberts]

Hi everyone, I'm fairly new to CF and everything in the new release looks just
so good that I decided it had to be CF over learning any other scripting
language....anyway.....

I'm tasked with creating a large online community site, and obviously the
first place to start is to create a registration system. Obviously i want to
use an online form to do this (cfform), with validation on it which then writes
this information into the database. I have already achieved writing information
to the database using cfquery and SQL. But have become stuck, when trying to
validate the fact that two people can not have the same email address. (i have
designated the email as being the primary key and therefore login along with a
password). I am using an Access database. Once I have achieved this validation
I shall be inserting a PayPal system into the registration system as it is
going to be a paid membership site (paypal looks fairly easy to get working).

I have tried using SQL (of which I have a basic - intermediate level from uni)
to do it, something like the below:

INSERT INTO dRegDetails (dEmail)
WHERE #FORM.dEmail# <> (SELECT * FROM dRegDetails (dEmail))
VALUES (
'#FORM.dEmail#'
)

and also I would like an error to pop up if the email address already exists,
so have enclosed the whole statement in CFIF with a CFELSE. the closest attempt
i came looks something like below, i have searched high and wide for support
and came across several different errors whilst changing small things but
cannot get it to work!! any assistance would be greatly accepted :)

<cfquery datasource="cfukdoorstaff" name="dRegEmail">
<cfif isDefined("Form.dEmail")>
INSERT INTO dRegDetails (dEmail)
WHERE #FORM.dEmail# <> (SELECT * FROM dRegDetails
(dEmail))
VALUES (
'#FORM.dEmail#'
)
<cfelse>
<cfoutput>
<b>This email address is already in use by another member, please amend and
try again.</b>
</cfoutput>
</cfif>
</cfquery>

[SQLMenace]

Convert you code to a stored proc and use this code

if not exists (select * from dRegDetails where dEmail =@Email)
begin
INSERT INTO dRegDetails (dEmail)
VALUES (@Email)
end

[Dan Bracuk]

or start with a check
<cfquery name="check">
select email from whereever
where email = '#form.email#"
</cfquery>
<cfif check.recordcount is 0>
insert
<cfelse>
do something else

[surenr]

Recently someone had posted a same kind of question.I think youcan use the
followng query:

INSERT INTO dRegDetails(Email)
VALUES (#FORM.dEmail#)
WHERE #FORM.dEmail# NOT IN (SELECT Email FROM dRegDetails)

To show a pop up box to show that the email already exits, First read the
number of records in the table.After you have inserted the new record, Count
the number of records before insertion and after insertion.If the number of
records are same then you can use a java script code else if the number of
records after insertion is higher then you can skip the pop up stuff