0

I have a sql table for storing unique e-mails.I need a stored procedure that could let a user that uses aspnet 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:

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
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?

3
Contributors
4
Replies
5
Views
5 Years
Discussion Span
Last Post by Verygoodguy
0

Make the stored procedure having input parameter @EmailAddress and write following query:

select count(UserID) from E-MAIL where EMailAdress = @EmailAddress.

In Page parse the result into int and check if it is greater than 0, it means email address already exist, else this is new email address. You can use Execute Scaler method for excuting the query.

0

Also, you can specify the email address as Unique key/Primary Key or composite key with User ID to avoid duplicate record.

0

Stored procedure with checking if email address already exists is more preferable. If you just specify Unique key, then you will get an exception when attempting to insert a duplicate email address. And if you use SP, you can put there also some actions to perform in a case if user tries to store a duplicate email.

0

I forgot to let You know that my table already has a primary key column named EMailID.It's an auto incriment column. I also use this column for relashionship with other tables. So sql want accept to make the e-mail column, a second primari key. I adopted some stored procedures that check if the e-mail exists as instructed by some good helpers in this post. But I stiil not know what to include im my stored procedure and in my aspnet C# code so that my user is informed that the e-mail already exists and no action took place. In this case He/She would use apropriate webforms tha I already created to update e-mail table.
Please help. How can I overcame this?

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.