stop duplicate record in database

jeff lee

I need to stop duplicate records in my sql table and if data exists how can i show message says already exists this is my code below :

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Windows.Forms;

using System.Data;

using System.Data.SqlClient;

namespace cap

{

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

{

SqlConnection con;

protected void Page_Load(object sender, EventArgs e)

{

}

protected void Button1_Click1(object sender, EventArgs e)

{

SqlConnection con = new SqlConnection(@"Data Source=DESKTOP-6I0G8AT;Initial Catalog=cap;Integrated Security=True");

con.Open();

string store = "INSERT INTO Customer (Cust_Name,Cust_Username,Cust_Email,Cust_Contact,Cust_Password ) VALUES (@Cust_Name,@Cust_Username,@Cust_Email,@Cust_Contact,@Cust_Password)";

SqlCommand cmd = new SqlCommand(store,con);

cmd.Parameters.AddWithValue("@Cust_NAME", txtfirstname.Text + " " + txtlastname.Text);

cmd.Parameters.AddWithValue("@Cust_Username", txtusername.Text);

cmd.Parameters.AddWithValue("@Cust_Email", txtemail.Text);

cmd.Parameters.AddWithValue("@Cust_Contact", txtcontact.Text);

cmd.Parameters.AddWithValue("@Cust_Password", txtpassword.Text);

cmd.ExecuteNonQuery();

con.Close();

}

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Mageshwaran R6y ago

Hi,
Can you check with the below link:
http://www.sqlservertutorial.net/sql-server-basics/delete-duplicates-sql-server/
Rajanikant Hawaldar7y ago
Hi Jeff lee,

just write select query to check cust_username / cust_Email is exist or not if exist display user already exists message.

select cust_username from Customer where cust_username = '"+ txtusername.Text+"'
Jignesh Kumar7y ago
Hi Jef,

Its better you can create store procedure where you can check user is exist or not and if not exist then you can insert else you can show message this record already exist in database.

But if you are looking for current implementation fix then please use below link,

https://www.c-sharpcorner.com/forums/prevent-insert-of-duplicated-records-sql
Gajendra Jangid7y ago
hello dear,

you can create condidate key with multiple columns
or
you can check if not exists befor insert value
Anoop Kumar Sharma7y ago
Hi Jeff,

You can do it in multiple ways. One way is to execute the select statement with where clause on the column for which you want to check the duplicity. For example, you can use Email and username to check its existence. If you get the data table having more then zero records then it means that record already exists in the database.

Other and recommended one is to check if not Exists condition in SQL query. If the record does not exist then insert the data else return record already exists so you can check the return value on the code side.

Sample Query:
IF NOT EXISTS (SELECT id
               FROM   mytable
               WHERE  username = @UserName
                      AND password = @Password)
  BEGIN
      INSERT INTO mytable
                  (username,
                   password)
      VALUES      (@UserName,
                   @Password)

      SELECT 'Record Inserted'
  END
ELSE
  BEGIN
      SELECT 'Record Already Exists'
  END
Hope this will help you.
Faisal Pathan7y ago
before inserting you just need to check record in DB with email or any filed which is unique and if record exists simple return message with text.