Click here to Skip to main content
15,940,550 members
Please Sign up or sign in to vote.
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
Updated 17-Feb-12 3:51am
v3
Comments
André Kraak 17-Feb-12 4:51am    
Edited question:
Added pre tags

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.
 
Share this answer
 
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
 
Share this answer
 
Comments
Do Afonso 19-Feb-12 4:30am    
Now, how to let the user know that the record he/she tried to insert exists, so it was not inserted?

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900