Duplicate Field Entry on Database Question

[Collaterly Sisters]

Hi all,

Ok I guess this is quite a simple thing to do but as an SQL/ASP newbie
im having trouble getting my head around it.

I have a simple form which users submit via a querystring. This is
sent to an asp page which inserts the data into a SQL database. All
that I want do is check one of the strings against a database field to
see if it already exists. If it does I want to set another string to a
certain value ("idalreadyexists = 1") and then carry on writing to the
strings into the database.
I just cant work out how to phrase a query that checks the database
and compares it to a string. Below is a very simplified example of
what I have at the moment. Any help would be *much* appreciated!

set objConn = Server.CreateObject("ADODB.Connection")
strConnection = "DSN=ax;Database=ax;"
strConnection = strConnection & "UID=sa;PWD=****;"
objConn.Open strConnection

strq = "INSERT INTO Main (id, email, password, idalreadyexists)VALUES
"
strq = strq & "('" & id &"', '" & email &"', '" & password &"', '" &
idalreadyexists &"' )"
objConn.Execute strQ

Thanks

[Ray at]

"Collaterly Sisters" <jonner2004@hotmail.com> wrote in message
news:c0f756cb.0311060412.243810e6@posting.google.c om...

> Hi all,
>
> Ok I guess this is quite a simple thing to do but as an SQL/ASP newbie
> im having trouble getting my head around it.
>
> I have a simple form which users submit via a querystring. This is
> sent to an asp page which inserts the data into a SQL database.

You may want consider using method=POST on your form.

> All
> that I want do is check one of the strings against a database field to
> see if it already exists. If it does I want to set another string to a
> certain value ("idalreadyexists = 1") and then carry on writing to the
> strings into the database.
> I just cant work out how to phrase a query that checks the database
> and compares it to a string. Below is a very simplified example of
> what I have at the moment. Any help would be *much* appreciated!

Something like this:
'checking for e-mail duplications

set objConn = Server.CreateObject("ADODB.Connection")
strConnection = "DSN=ax;Database=ax;" '''can you use a connection string
here instead?
strConnection = strConnection & "UID=sa;PWD=****;"
objConn.Open strConnection

bEmailExists = objConn.Execute("SELECT COUNT([email]) FROM [Main] WHERE
[email]='" & email & "'").Fields.Item(0).Value > 0
strq = "INSERT INTO Main (id, email, password, idalreadyexists)VALUES"
strq = strq & "('" & id &"', '" & email &"', '" & password &"', '" &
Abs(CInt(bEmailExists)) &"' )"

''The abs(cint( stuff will convert your True/False into 1/0.


You could also do this all in a stored procedure or setup a trigger to check
for duplicates and update the field accordingly.

Ray at work

>
> set objConn = Server.CreateObject("ADODB.Connection")
> strConnection = "DSN=ax;Database=ax;"
> strConnection = strConnection & "UID=sa;PWD=****;"
> objConn.Open strConnection
>
> strq = "INSERT INTO Main (id, email, password, idalreadyexists)VALUES
> "
> strq = strq & "('" & id &"', '" & email &"', '" & password &"', '" &
> idalreadyexists &"' )"
> objConn.Execute strQ
>
> Thanks