Prevent duplicate entry in database

Question

     private void Save_Click(object sender, EventArgs e)
    {
        string strconn = @"Server=.\SQLEXPRESS;initial catalog=PharmacyV2;integrated   security=true;";
        SqlConnection conn = new SqlConnection(strconn);
        //SqlCommand cmd = new SqlCommand();
         DataSet ds = new DataSet();
        conn.Open();
        SqlDataAdapter da = new SqlDataAdapter("select * from Units",conn);
        da.Fill(ds, "Units");
        bool found = false;
        for (int i = 0; i < dataGridView1.Rows.Count; i++)
        {

            for (int j = 0; j < ds.Tables["Units"].Rows.Count; j++)
            {


                if (ds.Tables["Units"].Rows[j][0].ToString() == dataGridView1.Rows[i].Cells[0].Value.ToString())
                {
                    found = true;
                    break;
                }

            }

            if (found==false)
            {

                SqlCommand cmd;
                cmd = new SqlCommand("insert into Units (Unit_name) values (@name)", conn);
                cmd.Parameters.AddWithValue("@name", dataGridView1.Rows[i].Cells[0].Value.ToString());
                cmd.ExecuteNonQuery();
                MessageBox.Show("تمت الاضافه");


            }
        }
        conn.Close();

    }

my program compare the each element from datagridview with every element from Uint table from database to prevent duplicate in database if element from datagridvoew is not Similar to element in uint table in database implement insert statement Why the program does not insert any data to database? (Does not implement the insert statement )

What is the value of `dataGridView1.Rows.Count`? Put a break point and check it — sll, Nov 02 '13 at 20:09
And dispose that SqlConnection object, i.e.: `using (SqlConnection conn = new SqlConnection(strconn)) { }` — Stefan, Nov 02 '13 at 20:09
are you getting any error or is it ignoring the insert into statement execution? — Sudhakar Tillapudi, Nov 02 '13 at 20:12
try to Add initialization of bool founded inside the for statement the second one — Eiaddar, Nov 02 '13 at 20:17

Sudhakar Tillapudi · Answer 1 · 2013-11-02T20:19:13.510

initialise your found variable to false inside your first for loop :

found = false;

so that it will be set to initial state for every iteration. otherwise if once it is set to true always becomes true.thats why yor insert statement is not getting executed.

So your for loop should look like :

    for (int i = 0; i < dataGridView1.Rows.Count; i++)
    {
        found = false;

        for (int j = 0; j < ds.Tables["Units"].Rows.Count; j++)
        {


            if (ds.Tables["Units"].Rows[j][0].ToString() == dataGridView1.Rows[i].Cells[0].Value.ToString())
            {
                found = true;
                break;
            }

        }

        if (found==false)
        {

            SqlCommand cmd;
            cmd = new SqlCommand("insert into Units (Unit_name) values (@name)", conn);
            cmd.Parameters.AddWithValue("@name", dataGridView1.Rows[i].Cells[0].Value.ToString());
            cmd.ExecuteNonQuery();
            MessageBox.Show("تمت الاضافه");


        }
    }

score 0 · Answer 2 · answered Nov 02 '13 at 20:49

How about you ask the database to check if the entry exists?

var unitName = dataGridView1.Rows[i].Cells[0].Value.ToString();
var command = new SqlCommand("SELECT COUNT(*) FROM Units WHERE Unit_name = @name", connection);
command.Parameters.AddWithValue("@name", unitName);

int result = (int)command.ExectureScalar();

if(result == 0) // no entry
{
     //Insert.
}

Prevent duplicate entry in database

2 Answers2