Preventing duplicate database entries

Hi all.. I've been scouring the forums for about 6 hours to noavail. This is a really simple question. I'm trying to havea registration page that lets the user input name, email, desiredusername, and password. I want to check the username and emailfields to make sure ppl cannot sign up twice. So from what I'vegathered I have a couple of options:

1) i can set up a unique constraint on the database columns,
2) i can run a select statement before inserting,
3) i can store the whole database column in a variable then search through it.

My question is how to do option 2? All of my transactions are through a sqldatasource object in c#.

since you mentioned sqldatasource I am assuming you are working with SQL Server. You could write a stored proc and do a conditional INSERT.

CREATE PROC..

...

BEGIN

IF NOT EXISTS ( SELECT * FROM table WHERE username = @.username ANDemail=@.email...)

BEGIN

-- do the INSERT

END

ELSE

BEGIN

--the user already exists. return an appropriate message

END

|||Do #1, then catch the constraint error in sqldatasource_inserted event.|||Stored procedures are pretty exciting and cool.. I'm about to spendsome time reading up on them because there's a lot for me to learn (howto execute them, syntax, capabilities, etc.). Appreciate thereply ndinakar. I'll figure this out soon I'm sure.|||I've spent a considerable amount of time trying to set up a constrainton the email and usernames fields to no avail. This is how I'vebeen going about it:

1)in server explorer, double click my table
2)right click the email column, select Check Constraints
3) Add new

Now it asks for an Expression. No idea what to enter here I triedthe single word "UNIQUE" but that is wrong. And, all my web/forumsearches regarding the subject were unsuccessful- lots of regularexpression results returned.

Similarly, I tried playing around with the Indexes/Keys option insteadof check constraints option. I thought this would give me moreluck since it looks like you can select from a drop-down which columnsyou want to apply the effect to. Problem here is the drop-down isonly populated with one column, the PK.

I'm thinking I need to head over to the bookstore and park myself thereuntil I figure some things out- sick of staring at the monitor. Thanks for the reply.|||

You can also do it via script from Query Analyzer:

ALTERTABLE <tableName> ADD COSNTRAINT <constraint_name> UNIQUE(<column1>,<column2>,<column3>)

|||It's not a check constraint, it's really a unique index that you build.

prevent zero length entries

I have a couple critical fields where I am setting them to no nulls but just
realized that they can be set to zero length strings very easily. Other than
a trigger or handling the validation in the front end, is there an easy way
to handle this?
MS Access is my front end so interestingly enough, if you select an the
entire text in a field and hit the delete key it trys to set to NULL not ""
so it's ok. But in EM you can just hit the delete key and it goes to ""
instead of NULL. Obviously end users are goign to use the front end and not
EM so this isn't really a problem but I was just wondering about other front
ends that might not behave the same way. They might set to "" on hitting the
delete key.
THanks,
KeithKeith G Hicks wrote:
> I have a couple critical fields where I am setting them to no nulls but ju
st
> realized that they can be set to zero length strings very easily. Other th
an
> a trigger or handling the validation in the front end, is there an easy wa
y
> to handle this?
> MS Access is my front end so interestingly enough, if you select an the
> entire text in a field and hit the delete key it trys to set to NULL not "
"
> so it's ok. But in EM you can just hit the delete key and it goes to ""
> instead of NULL. Obviously end users are goign to use the front end and no
t
> EM so this isn't really a problem but I was just wondering about other fro
nt
> ends that might not behave the same way. They might set to "" on hitting t
he
> delete key.
> THanks,
> Keith
>
A check constraint of LEN(ColumnName) > 0 would do it, or you could work
in a more complex requirement. Honestly, if you're worried about people
just putting in an empty string, and you require at least one char,
they'll learn that if they put in 1 char they can save the data. If you
bump the requirement up to 2, they'll put in 2. :D
Zach|||keith,
create a check constraint on table, with expression: datalength(col)>0
hth,
dean
"Keith G Hicks" <krh@.comcast.net> wrote in message
news:%23w4ZvbwCFHA.1932@.TK2MSFTNGP14.phx.gbl...
> I have a couple critical fields where I am setting them to no nulls but
just
> realized that they can be set to zero length strings very easily. Other
than
> a trigger or handling the validation in the front end, is there an easy
way
> to handle this?
> MS Access is my front end so interestingly enough, if you select an the
> entire text in a field and hit the delete key it trys to set to NULL not
""
> so it's ok. But in EM you can just hit the delete key and it goes to ""
> instead of NULL. Obviously end users are goign to use the front end and
not
> EM so this isn't really a problem but I was just wondering about other
front
> ends that might not behave the same way. They might set to "" on hitting
the
> delete key.
> THanks,
> Keith
>|||Thank you to both of you. That works just fine of course.
I feel sort of silly now. :)|||"Zach Wells" wrote:
<snip>

> Honestly, if you're worried about people just putting in
> an empty string, and you require at least one char, they'll
> learn that if they put in 1 char they can save the data. If
> you bump the requirement up to 2, they'll put in 2. :D
LOL! Never underestimate the ability of users to circumvent your data
validation routines. We had a situation just like that. You could even map
out the evolution of the validation routine and the users' innovation
(especially since the application had no way of exactly determining what a
correct string was for that particular column). The progression looked
something like:
.
NA
N/A
XXXX
DONTKNOW
:)
Craig