Replacing data in database from a csv file

I am creating a Windows based application to read csv file and display in datagridview. The csv file consist of item_no and stock.

I have two buttons, add data and replace data.

With add data, same item_no stock get added and it is then inserted into database. In database, stock get added with the existing stock of particular item_no.

With the replace data, same item_no stock get added and it is then replace with the stock of item_no in database.

I am able to add the stock in database but I can't replace the stock in the database.

Here is my code to replace data which is more likely same as add stock,

try
   {

     //we need to copy the data from datagridview into data table
     DataTable dtable = new DataTable();

     dtable.TableName = "Product";

     foreach (DataGridViewColumn col in dataGridView1.Columns)
     {
        dtable.Columns.Add(col.DataPropertyName, col.ValueType);
     }

            foreach (DataGridViewRow row in dataGridView1.Rows)

            {

                if (row.IsNewRow)

                    continue;



                DataRow dtRow = dtable.NewRow();



                for (int i = 0; i < dataGridView1.Columns.Count; i++)

                    dtRow[i] = (row.Cells[i].Value == null ? DBNull.Value : row.Cells[i].Value);



                dtable.Rows.Add(dtRow);

            }



            foreach (DataRow dr in dtable.Rows)

            {

                string itemNo = null;

                string itemName = null;

                double cost = 0.00;

                double price = 0.00;

                double Stock = 0.00;

                int dept = 1;

                double tax1 = 0;

                double tax2 = 0;

                double BulkPrize = 0.00;

                double BulkQty = 0.00;

             //   double finalStock = 0.00;

                itemNo = Convert.ToString(dr[0]);



                if (dr[1] != DBNull.Value)

                    itemName = Convert.ToString(dr[1]);





                if (dr[2] != DBNull.Value)

                    price = Convert.ToDouble(dr[2]);





                if (dr[3] != DBNull.Value)

                    cost = Convert.ToDouble(dr[3]);



                Stock = Convert.ToDouble(dr[4]);

               // finalStock = finalStock + Stock;



                if (dr[5] != DBNull.Value)

                    dept = Convert.ToInt32(dr[5]);



              开发者_Go百科  if (dr[6] != DBNull.Value)

                    tax1 = Convert.ToDouble(dr[6]);



                if (dr[7] != DBNull.Value)

                    tax2 = Convert.ToDouble(dr[7]);



                string sql_select = "select count(*) from PRODUCTS where item_no= '" + itemNo + "'";

                SqlCommand cmdCheckPmk = new SqlCommand(sql_select, Class1.conn);



                int selectItemNo = Convert.ToInt32(cmdCheckPmk.ExecuteScalar());



                if (selectItemNo != 0)

                {

                    string sql_update = "update PRODUCTS set item_stock=+'" + Stock + "' where item_no= '" + itemNo + "'";

                    SqlCommand cmd1 = new SqlCommand(sql_update, Class1.conn);

                    cmd1.ExecuteNonQuery();



                }

                else

                {

                    SqlCommand cmd11 = new SqlCommand("insert into PRODUCTS(item_no,item_name,price,cost,item_stock,dept_id,tax_rate1,tax_rate2,bulk_price,bulk_qty) values ('" + itemNo + "','" + itemName + "'," + price + "," + cost + "," + Stock + ",'" + dept + "','" + tax1 + "','" + tax2 + "'," + BulkPrize +"," + BulkQty +") ", Class1.conn);

                    cmd11.ExecuteNonQuery();

                }

            }



            MessageBox.Show("Data Replace Successfully ..!!", "Congrats", MessageBoxButtons.OK, MessageBoxIcon.Information);

        }

        catch (System.Data.SqlClient.SqlException exe)

        {

            if (exe.Number == 547)

            {

                MessageBox.Show("Add Department in Corner Store First..!!", "Add Department in Corner Store", MessageBoxButtons.OK, MessageBoxIcon.Error);

            }

            else

            {

                MessageBox.Show(exe.Message, "Error in replace - Option 3 Insert/Update", MessageBoxButtons.OK, MessageBoxIcon.Error);

            }

        }

    }

Can anyone help me?

Thanks.

Rushabh

This doesn't look right

string sql_update = "update PRODUCTS set item_stock=+'" + Stock + "' where item_no= '" + itemNo + "'";

I think you want (try this)

string sql_update = "update PRODUCTS set item_stock='" + Stock + "' where item_no= '" + itemNo + "'";

Also I'm not sure if your item_no is an int or not, if it is an int you need

string sql_update = "update PRODUCTS set item_stock='" + Stock + "' where item_no = " + itemNo ;