How we can avoid duplicate insertion of record in database in ASP.NET

How we can avoid duplicate insertion of record in database in ASP.NET

Introduction- In this article, i will discuss that how we can avoid duplicate insertion of records in database. This is very helpful article for any .Net developer.

Implementation- create a website , add page named register.aspx. place five textboxes named name_txt, address_txt , contact_txt,username_txt,password_txt and a button named submit_button and a label named lbl_msg.

---

Database Script- create a table in database named tb_register. Here I am giving you to complete database script of the table tb_register. See below:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tb_register]') AND type in (N'U'))

BEGIN

CREATE TABLE [dbo].[tb_register](

[id] [bigint] IDENTITY(1,1) NOT NULL,

[ename] [varchar](50) NULL,

[eaddress] [varchar](200) NULL,

[econtact] [varchar](50) NULL,

[eusername] [varchar](50) NULL,

[epassword] [varchar](50) NULL

) ON [PRIMARY]

END

Code fo register.aspx.cs Page-

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

using System.Collections;

using System.Globalization;

public partial class detail : System.Web.UI.Page

{

SqlConnection con = new SqlConnection();

SqlCommand cmd = new SqlCommand();

SqlDataAdapter adp = new SqlDataAdapter();

protected void Page_Load(object sender, EventArgs e)

{

}

protected void submit_button_Click(object sender, EventArgs e)

{

con.ConnectionString = ConfigurationManager.ConnectionStrings["cnn"].ConnectionString;

con.Open();

// here i am fetching name from the database and compare this name with the name that will be

// entered by the user in name_txt textbox

adp = new SqlDataAdapter(@"select ename from tb_register where ename=@ename", con);

cmd.Connection = con;

adp.SelectCommand.Parameters.AddWithValue("@ename", name_txt.Text);

DataSet ds = new DataSet();

adp.Fill(ds);

//here i am comapre name which is stored in the database with the name that will be entered by the user

// if the name that will be enteres by the user and the name which is store in the database are same

//then it goes in the else statement & label print the message the name enter by you is alreay available

//in the database. "please enter any other name"

if (ds.Tables[0].Rows.Count == 0)

{

String compare;

// here i am using try catch statement because if the compared name not available in database

// then there will be no any value in the string vaiable compare and it craetes an error

//there in no any row in the position of o. so i am using try catch statement

try

{

compare = (ds.Tables[0].Rows[0]["ename"]).ToString();

//It will avoid to save the duplicate name using Comparing string function.

// here i am using String.Compare function because suppose there is name "BHARAT" already stored

//in database.

// if u enter "bharat" it will accept. to avoid this drawback i am

//using string.compare method. it compare the strings

// and avoid to save dulpicate record

if (String.Compare(compare.ToUpper(), name_txt.Text.ToUpper()) == 0)

{

cmd.CommandText = @"INSERT INTO tb_register(ename,eaddress,econtact,eusername,epassword)

VALUES(@ename,@eaddress,@econtact,@eusername,@epassword)";

cmd.Connection = con;

cmd.Parameters.AddWithValue("@ename", name_txt.Text);

cmd.Parameters.AddWithValue("@eaddress", address_txt.Text);

cmd.Parameters.AddWithValue("@econtact", contact_txt.Text);

cmd.Parameters.AddWithValue("@eusername", username_txt.Text);

cmd.Parameters.AddWithValue("@epassword", password_txt.Text);

cmd.ExecuteNonQuery();

cmd.Dispose();

con.Close();

clr_rec();

lbl_msg.Text = "Your record sumitted successfully";

}

else

{

lbl_msg.Text = name_txt.Text + " " + " " + "already exists In the database";

clr_rec();

return;

}

}

catch

{

cmd.CommandText = @"INSERT INTO tb_register(ename,eaddress,econtact,eusername,epassword)

VALUES(@ename,@eaddress,@econtact,@eusername,@epassword)";

cmd.Connection = con;

cmd.Parameters.AddWithValue("@ename", name_txt.Text);

cmd.Parameters.AddWithValue("@eaddress", address_txt.Text);

cmd.Parameters.AddWithValue("@econtact", contact_txt.Text);

cmd.Parameters.AddWithValue("@eusername", username_txt.Text);

cmd.Parameters.AddWithValue("@epassword", password_txt.Text);

cmd.ExecuteNonQuery();

cmd.Dispose();

con.Close();

clr_rec();

lbl_msg.Text = "Your record sumitted successfully";

}

}

else

{

lbl_msg.Text = name_txt.Text + " " + " " + "already exists in the database";

clr_rec();

return;

}

}

private void clr_rec()

{

name_txt.Text = "";

address_txt.Text = "";

contact_txt.Text = "";

username_txt.Text = "";

password_txt.Text = "";

}

}