How to prevent duplicates

Question

0.00/5 (No votes)

See more:

I have a SQL table for storing unique e-mails. I need a stored procedure that could let a user that uses ASP.NET page to insert data know that the e-mail address already exists on a table and specify a new one.
The table looks like this:

SQL

EMailID int NOT NUL (1,1),
UserID int NOT NULL FK,
EMailAdress varchar(128) NOT NULL,
Description varchar(100)

My actual stored procedure looks like this:

SQL

CREATE PROCEDURE dbo.InsertE-Mail
@UserID int,
@EMailAdress varchar(128),
@Description varchar(100)
AS
BEGIN
INSERT E-MAIL 
(
UserID,
EMailAdress,
Description
)
VALUES
(
@UserID,
@EMailAdress,
@Description
)
END
GO

Any one can help?

Posted 16-Feb-12 22:39pm

Do Afonso

Updated 17-Feb-12 3:51am

Shahin Khorshidnia

v3

Add a Solution

Comments

André Kraak 17-Feb-12 4:51am

Edited question:
Added pre tags

2 solutions

Solution 2

The simplest way would be to put a UNIQUE index (note, this is separate from the primary key) on the EmailAddress column. This will cause the insert statement to fail with a fairly intelligent error message ('duplicate entry for key EmailAddress' or something similar) which you can catch in the top tier and create a friendly error for the user.

Posted 17-Feb-12 3:30am

BobJanova

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Om Prakash Pant · Accepted Answer · 2012-02-16T23:16:00

before inserting the data, check email id already exists in table or not..

SQL

INSERT INTO E-MAIL (UserID,EMailAdress,Description) SELECT UserID,@EMailAdress,@Description
WHERE NOT EXISTS (SELECT UserID  FROM E-MAIL WHERE EMailAdress = @EMailAdress)

Or

SQL

IF NOT EXISTS (SELECT UserID  FROM E-MAIL WHERE EMailAdress = @EMailAdress)
BEGIN
    INSERT INTO E-MAIL (UserID,EMailAdress,Description) VALUES (UserID,@EMailAdress,@Description)
END