prevent insert of duplicated records sql

mind controll

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();

}

9 Replies

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

Vulpes12y ago
Accepted 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();
}

+3
Marvin Gapuz7y ago
I get a red line under sql.... please help
0
Vance Keizer Bacarra11y ago
I get a red line under sql...
0
Shourya Verdhan11y ago
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
         }
0
Vulpes12y ago
Sorry, that's a typo on my part. It should be:

if (numRecords == 0)
-1
mind controll12y ago
I get a red line under numrecords in the following line :
if (numrecords == 0)
0
mind controll12y ago
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
0
shahidkamal ansari12y ago
There are two ways you can achieve
.
The first one is very simple.
In your database table,identify 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.
0
Vulpes12y ago
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.
0