Duplication of records

[nikichu]

asp.net c# sql server

I have a web form that I use to insert data into a sql database. I want to know how to prevent inserting duplicate records into the database. Thanks.

Ichu

[sknake]

This is really an SQL question. What you would do is:

Declare 
@Name varchar(1),
@City varchar(1),
@State varchar(1)

Set @Name = 'a'
Set @City = 'b'
Set @State = 'c'

Insert Into Customer (Name, City, State)
Select @Name, @City, @State
Where NOT EXISTS
(
  Select *
  From Customer (NOLOCK)
  Where Customer.Name = @Name and Customer.City = @City and Customer.State = @State
)

Select CAST(@@ROWCOUNT as int)

If the result is "0" then it was not inserted because it already exists. Likewise "1" means it was inserted.

[nikichu]

Thanks Scott,for the idea but.....

i have a dropdownlist into which i am inserting records.....which should not be duplicated either in database or in dropdownlist

My code is in C# with sql server db

Hope u will go through the following!!!

private void Button1_Click(object sender, System.EventArgs e)
        {
            SqlConnection con=new SqlConnection(@"Server=sqloledb.1;User ID=sa;password=;Data Source=IRID_BDC1;Initial Catalog=newtab");
            con.Open();
            SqlCommand cmd=new SqlCommand("insert into Loc_det (Loc,Lab,Tot_sys) values( '"+ Droploc1.SelectedItem.Text +"','" +txtloc.Text+"','" + txttot.Text + "') ",con);
            cmd.ExecuteNonQuery();
            Dropldownlist.Visible=true;

[sknake]

Please use code tags when posting code on daniweb:

You should also use parameterized queries. Try this:

private void button3_Click(object sender, EventArgs e)
    {
      //InsertRecord(something.Text, somethingElse.Text, some.Text);
      if (InsertRecord("abc1", "123", "456"))
      {
        MessageBox.Show("record Inserted!");
      }
      else
      {
        MessageBox.Show("Record already exists");
      }

    }

    private bool InsertRecord(string Loc, string Lab, string Tot)
    {
      const string connStr = @"Data Source=apex2006sql;Initial Catalog=Leather;Integrated Security=True;";
      List<string> Sql = new List<string>();
      Sql.Add("Insert Into Loc_Det (Loc, Lab, Tot)");
      Sql.Add("Select @Loc, @Lab, @Tot");
      Sql.Add("Where NOT EXISTS");
      Sql.Add("(");
      Sql.Add("  Select *");
      Sql.Add("  From Loc_Det (NOLOCK)");
      Sql.Add("  Where Loc_Det.Loc = @Loc and Loc_Det.Lab = @Lab and Loc_Det.Tot = @Tot");
      Sql.Add(")");
      Sql.Add("");
      Sql.Add("Select CAST(@@ROWCOUNT as int)");
      string query = GetText(Sql);
      using (SqlConnection conn = new SqlConnection(connStr))
      {
        conn.Open();
        using (SqlCommand cmd = new SqlCommand(query, conn))
        {
          cmd.Parameters.Add(new SqlParameter("@Loc", Loc));
          cmd.Parameters.Add(new SqlParameter("@Lab", Lab));
          cmd.Parameters.Add(new SqlParameter("@Tot", Tot));
          return (Convert.ToInt32(cmd.ExecuteScalar()) > 0);
        }
      }
    }
    private static string GetText(List<string> sc)
    {
      StringBuilder sb = new StringBuilder();
      for (int i1 = 0; i1 < sc.Count; i1++)
      {
        sb.AppendLine(sc[i1]);
      }
      return sb.ToString();
    }