prevent insert of duplicated records sql

Question

I need to prevent inserting duplicated records in a sql table and if exists show message says already exists

this is my button event :

private void button2_Click(object sender, EventArgs e)

{

conn.Open();

com.Connection = conn;

sql = "INSERT INTO lapusers([username],[fillingcode],[branch],[department],[agency])VALUES(@username,@fillingcode,@branch,@department,@agency)";

com.CommandText = sql;

com.Parameters.Clear();

com.Parameters.AddWithValue("@username", userlapbox.Text);

com.Parameters.AddWithValue("@fillingcode", userfilllapbox.Text);

com.Parameters.AddWithValue("@branch", comboBox2.Text);

com.Parameters.AddWithValue("@department", comboBox1.Text);

com.Parameters.AddWithValue("@agency", comboBox3.Text);

com.ExecuteNonQuery();

MessageBox.Show("Created Successfully ..");

conn.Close();

}

Vulpes · Answer

The primary key needs to be set in the database itself. Suppose 'username' is your primary key and therefore unique. Then you can check whether it already exists in the database or not as follows: private void button2_Click(object sender EventArgs e { conn.Open(); com.Connection = conn; sql = SELECT COUNT(*) FROM lapusers WHERE [username] = @username; com.CommandText = sql; com.Parameters.Clear(); com.Parameters.AddWithValue(@username userlapbox.Text); int numRecords = (int)com.ExecuteScalar(); if (numrecords == 0) { sql = INSERT INTO lapusers([username][fillingcode][branch][department][agency])VALUES(@username@fillingcode@branch@department@agency); com.CommandText = sql; com.Parameters.Clear(); com.Parameters.AddWithValue(@username userlapbox.Text); com.Parameters.AddWithValue(@fillingcode userfilllapbox.Text); com.Parameters.AddWithValue(@branch comboBox2.Text); com.Parameters.AddWithValue(@department comboBox1.Text); com.Parameters.AddWithValue(@agency comboBox3.Text); com.ExecuteNonQuery(); MessageBox.Show(Created Successfully ..); } else { MessageBox.Show(A record with a user name of {0} already exists userlapbox.Text); } conn.Close(); }

Vulpes · Answer

Sorry that's a typo on my part. It should be: if (numRecords == 0)

Vulpes · Answer

Well if your table has a primary key or one of the columns has a 'unique' constraint then this will effectively prevent you from inserting duplicate records in the database. Rather than attempt to make the insertion and catch the resulting exception I'd simply check whether a record with the primary key or unique column already exists and if it does display an appropriate message. Otherwise you can safely make the insertion.

Vance Keizer Bacarra · Answer

I get a red line under sql...

Shourya Verdhan · Answer

Hello to all of you There is also a simplest way Just write this code in your page. Put This code before the Page_Load protected void Page_Init(object sender EventArgs e) { Response.Cache.SetCacheability(System.Web.HttpCacheability.NoCache); Response.Cache.SetExpires(System.DateTime.UtcNow.AddSeconds(-1)); Response.Cache.SetNoStore(); } protected void Page_PreRender(object sender EventArgs e) { ViewState[update] = Session[update]; } Then protected void Page_Load(object sender EventArgs e) { if (!IsPostBack) { Session[update] = Server.UrlEncode(System.DateTime.Now.ToString()); } } Then at the time of insert the data if (Session[update].ToString() == ViewState[update].ToString()) { Write your insert Query here }

mind controll · Answer

I get a red line under numrecords in the following line : if (numrecords == 0)

mind controll · Answer

can u show me how to do that if u plz ... I can make the primary key but the problem is how to make that in button event code

shahidkamal ansari · Answer

There are two ways you can achieve . The first one is very simple. In your database tableidentify the primary key or Composite key depending upon your requirement of How you define the row as unique. Then.put your c# code in try catch block. So when ever you inswrt a duplicate record Database exception wpuld be thrown which woulde catch At c# code level Second method would be simple checking the exitance of the record In database before inserting the new record. But here there will be two Database hits.