How to check if the value is already there before inserting into SQL Server using asp.net?

Question

Here is the current code :

protected void Button1_Click(object sender, EventArgs e)
{
    SqlCommand cmd = new SqlCommand("insert into customers_testing (cust_id,cust_name,cust_group,acc_mgr2,acc_mgr) values('" + textCustID.Text + "','" + textCustName.Text + "','" + textCustSector.Text + "','" + dropdAccAdmin.Text + "','" + dropdAccManager.Text + "')", con);

    cmd.ExecuteNonQuery();
    con.Close();

    Label1.Visible = true;
    Label1.Text = "New Customer Added Successfully";

    textCustID.Text = "";
}

Please read through answers like [this one](http://stackoverflow.com/questions/8276336/how-can-i-prevent-inserting-duplicate-data-into-a-sql-server-table). You can modify the query to check for duplicates, you could add a unique constraint, or both. The exact answer really depends on your use case. — Tim Biegeleisen, Mar 10 '16 at 05:06
Possible duplicate of [Check if record exists in database](http://stackoverflow.com/questions/21302244/check-if-record-exists-in-database) — Faraz Ahmed, Mar 10 '16 at 05:07
[SQL Injection alert](http://msdn.microsoft.com/en-us/library/ms161953%28v=sql.105%29.aspx) - you should **not** concatenate together your SQL statements - use **parametrized queries** instead to avoid SQL injection — marc_s, Mar 10 '16 at 05:31
you need to know something unique before checking the existence of record in SQL otherwise it's get failed in some case — Satish Kumar sonker, Mar 10 '16 at 05:34

score 0 · Answer 1 · edited May 23 '17 at 11:59

A few of things. First, if you already have the customer ID, then you would know that the record exists, correct? If you are interested in doing an insert OR update in one go instead, you could use the sql MERGE statement.

Second, you probably want to use an identity column for the customer ID anyway. If you were doing a pure insert of a new record, you wouldn't need to provide an ID since it would be auto-generated.

Finally, the SQL you have provided is particularly susceptible to SQL injection style attacks. You absolutely want to use SQL parameterization to prevent this sort of vulnerability.

score 0 · Answer 2 · edited May 23 '17 at 11:50

You can apply unique constraint on single column or group of columns in sql server database table . then wrap your C# code into try catch block it will throw exception for duplicate records.

here is link to create unique index in sql server .

[Add unique constraint to combination of two columns

Or you can do in C# .

Before insert new record in to table Run a select command on table with new value if find any record then its duplicate otherwise its new record for table .

score 0 · Answer 3 · answered Mar 10 '16 at 06:34

It would depend on how you would consider it as "the value is already there" (in other words, do you only care where one column matches or all?).

In any case, I'd recommend starting the SQL with:

IF NOT EXISTS (SELECT * FROM customers_testing WHERE XXXXX)
BEGIN
    ...[your existing SQL]...
END

Where XXXXX is what you don't want to be duplicated. (example: XXXXX could be WHERE customers_testing <> '" + textBox.Text + "') In that case I'd also recommend using string.Format to create your SQL so you can just reference a control's text value once for both places.

Or even better yet, I highly recommend making a stored procedure instead of creating the SQL on the fly in C#. There are many reasons to do this such as performance, reusability, etc, but I think the most important reason is to avoid the possibility of SQL-injection. It's just much better practice.

How to check if the value is already there before inserting into SQL Server using asp.net?

3 Answers3