How to avoid duplicate record in SQL Server?

Posted by Poster under Sql Server on 12/3/2008 | Views : 7768 | Status : [Member] | Replies : 6

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

I have a user table in which I want to avoid duplicate username, how can I do that.

I want to check for the existing entry from the table and if doesn't exists then insert otherwise not.

Any code snippet?

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: perfectchourasia-9163 on: 4/3/2012 [Member] Starter | Points: 25

0	http://sandeep-chourasia.blogspot.in/2011/12/delete-duplicate-data.html ER sandeep chourasia sandeepchrs@yahoo.com (on facebook) http://www.aspnetcodes.com/ Poster, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Hmanjarawala on: 4/3/2012 [Member] Bronze | Points: 25

0	Ok, Write down IF Not Exists(SELECT 1 FROM UserTable WHERE UserName = @userName) Begin INSERT INTO UserTable (UserName) VALUES (@userName) End Mark this as answer, if it helps you..................... Himanshu Manjarawala Sr. Software Engineer@AutomationAnywhere http://fieredotnet.wordpress.com/ Poster, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Self-Innovator on: 4/3/2012 [Member] Bronze | Points: 25

0	Chek this Creat a SP create proc [dbo].[sp_InsPersonInfo] @pEmail varchar(50) as declare @Count as int set @Count=(select count(id) from Persons where PersonEmail=@pEmail) select @Count as DuplicateEmail if @Count=0 begin insert into Persons values(@pEmail) end From Front End chek for Condition of protected void btnSubmit_Click(object sender, EventArgs e) { BAL = new PersonBusinesslayer(); ds = new DataSet(); ds=BAL.Insert(txtEmail.Text); int Count; Count = Convert.ToInt32(ds.Tables[0].Rows[0]["DuplicateEmail"].ToString()); if (Count > 0) { string strjscript = "<script language='javascript'> alert('Email id already exists ');</script>"; Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "myscript", strjscript); } } Join Hands Change lives Thanks & Regards Straight Edge Society Poster, if this helps please login to Mark As Answer. \| Alert Moderator

Chek this Creat a SP

create proc [dbo].[sp_InsPersonInfo]

@pEmail varchar(50)

as

declare @Count as int

set @Count=(select count(id) from Persons where PersonEmail=@pEmail)

select @Count as DuplicateEmail

if @Count=0

begin

insert into Persons values(@pEmail)

end

From Front End chek for Condition of

protected void btnSubmit_Click(object sender, EventArgs e)

    {

      BAL = new PersonBusinesslayer();

ds = new DataSet();        ds=BAL.Insert(txtEmail.Text);

      int Count;

Count = Convert.ToInt32(ds.Tables[0].Rows[0]["DuplicateEmail"].ToString());

      if (Count > 0)

      {

string strjscript = "<script language='javascript'>

alert('Email id already exists ');</script>";

Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "myscript",   strjscript);

      }      

    }

Join Hands Change lives
Thanks & Regards
Straight Edge Society

Poster, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sksamantaray on: 4/3/2012 [Member] Silver | Points: 25

0	Hi create unique constraint on username column , so you need not have to check for duplicate .It does not allow duplicate value. Thanks, Sanjay Poster, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Sriramnandha on: 5/20/2012 [Member] Starter | Points: 25

0	HI, TO CREATE PRIMARY KEY OR UNIQUE KEY CONSTRAINT IT WILL AVOID DUPLICATE AND NULL VALUES. REGARDS sriram Poster, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Raja on: 12/3/2008 [Member] Starter

0	You can use following code snippet. DECLARE @Count int SELECT @Count = Count(AutoID) FROM UserTable WHERE UserName = @userName IF @Count = 0 BEGIN INSERT INTO UserTable (UserName) VALUES (@userName) END In this case if @Count will be 0, that is if no record found for the username then insert record into the table. Thanks Regards, Raja, USA Poster, if this helps please login to Mark As Answer. \| Alert Moderator

You can use following code snippet.

DECLARE @Count int

	SELECT @Count = Count(AutoID) FROM UserTable WHERE UserName = @userName

	IF @Count = 0

		BEGIN

			INSERT INTO UserTable (UserName) VALUES (@userName)

		END

In this case if @Count will be 0, that is if no record found for the username then insert record into the table.

Thanks

Regards,
Raja, USA

Poster, if this helps please login to Mark As Answer. | Alert Moderator

Latest Posts