Prevent Duplicate Inserts

in your stored procedure you are looking for employee user name as well along with the other three mentioned in your question, I just removed and it should work now as you desired

USE [PACYBER_WellnessTracker_12-13]
GO
/****** Object:  StoredProcedure [dbo].[InsertEmployee]    Script Date: 5/23/2013 2:19:31 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[InsertEmployee]

(
@emp_firstname varchar(50),
@emp_lastname varchar(50),
@emp_email varchar(100),
@emp_username varchar(50),
@emp_password binary(96)
)

AS

BEGIN TRANSACTION;
IF NOT EXISTS (SELECT * FROM  dbo.Employees WHERE emp_firstname = @emp_firstname AND emp_lastname = @emp_lastname AND emp_email = @emp_email)
BEGIN
   INSERT dbo.Employees (emp_firstname, emp_lastname, emp_email, emp_username, emp_password)
   VALUES (@emp_firstname, @emp_lastname, @emp_email, @emp_username, @emp_password) 
COMMIT;
END

ELSE

BEGIN
    RAISERROR('You are already in the system!', 16, 1)  
 ROLLBACK TRAN 
 
 END

Select allOpen in new window

Ok, that is still not working though :(

I just tested it and if I have duplicate first name, last name, email address BUT have different username the employee gets added.

I need to make sure that neither of first name, last name, email address, and username are duplicate. If either one of those are the same then the insert should fail.

ok let us write your test cases

employee record existing in datbase prior inserting

first name Neo LastName Jarvis email gm.surendra@gmail.com userName neo_jarvis

Now what should happen to the below

first name as Neo Last Name Expert email gm.surendra@gmail.com username neo_jarvis

should this be added or discarded...

that should be added.

if first name and last name are the same then discard.
if email is already in the system then discard.
if first name, last name, email, and username are in the system then discard.

Hi,

In your discard cases, the third one is redundant - already covered with same first name and last name.

I suggest that you will need user_name to be unique in your system as well. I can't conceive of duplicate user_name ever being a good thing.

Kind Regards
  David

@dtodd,

What do you mean by the "third one is redundant"?

I also need to make sure user_name is unique and NOT duplicated in the system.

@Neo_jarvis,

Can you please walk me through what your code will do?

@asp_net2

My code will check if the first name , last name combination already exists in the database or the e-mail id already exists in the database.
if they exists that it will raise an error or else it will insert

NOTE: It will not check for existance or duplication of the user name.

Hi,

In the example Neo_Jarvis posted, the second entry had the same username as the first, and you said that it should be added.

The tests read

if first name and last name are the same then discard.
if email is already in the system then discard.
if first name, last name, email, and username are in the system then discard.

I suggest that if the firstname.lastname pair is unique then the firstname/lastname/email/username will also be unique.

Then the test should read
if username is already in the system then discard.

Regards
  David