how to update the qty column in datagridview in c#.

Question

when i call this function only one colum has been updated couln't updated more than one colum

please help me

public void updatedb()

{

for (int row = 0; row < dataGridView1.Rows.Count - 1; row++)

{

string itmno = dataGridView1.Rows[row].Cells[1].Value.ToString();

string itmqty = dataGridView1.Rows[row].Cells[3].Value.ToString();

// string t = "select * from products where id=" + itmno + "";

// Connnew.DbSearch(t);

con.Close();

MySqlCommand scmd = new MySqlCommand();

scmd.Connection = con;

scmd.CommandText = "select * from products where id in" + itmno + "";

con.Open();

MySqlDataReader dr;

dr = scmd.ExecuteReader();

string oldqty = "", newqty = "";

while (dr.Read())

{

oldqty = dr[5].ToString();

newqty = (int.Parse(oldqty) - int.Parse(itmqty)).ToString();

}

string t2 = ("UPDATE products SET qty = '" + newqty + "' WHERE id = '" + itmno + "' ");

con.Close();

con.Open();

MySqlCommand upcmd = new MySqlCommand(t2,con);

upcmd.ExecuteNonQuery();

MessageBox.Show("Updateeeeeeeeeeeeeeeeeeeeee");

}

con.Close();

Rafnas T P · Answer

try as below public void updatedb() { for ( int row = 0; row { string itmno = dataGridView1.Rows[row].Cells[1].Value.ToString(); string itmqty = dataGridView1.Rows[row].Cells[3].Value.ToString(); // string t = select * from products where id= + itmno + ; // Connnew.DbSearch(t); //con.Close(); MySqlCommand scmd = new MySqlCommand(); scmd.Connection = con; scmd.CommandText = select * from products where id in + itmno +  ; con.Open(); MySqlDataReader dr; dr = scmd.ExecuteReader(); string oldqty =   newqty =  ; while (dr.Read()) { oldqty = dr[5].ToString(); newqty = ( int .Parse(oldqty) - int .Parse(itmqty)).ToString(); } string t2 = ( UPDATE products SET qty = ' + newqty + ' WHERE id = ' + itmno + '  ); con.Close(); con.Open(); MySqlCommand upcmd = new MySqlCommand(t2con); upcmd.ExecuteNonQuery(); con.Close(); } MessageBox.Show( Updateeeeeeeeeeeeeeeeeeeeee );

Nilesh Shah · Answer

I can't write all code for you it's your work you have to do but I can give you suggestion. Just for getting value of oldqty you are firing selecte query in loop for each row which is a costly operation in terms of performance. all you want to do here is update the quantity by deducting the cell value from existing one. modify your update query so that in the query itself it can get the old value and do substraction. sample code:  Update p set qty = p.qty -  + int .Parse(itmqty)).ToString() +  from products p where p.itmno =  + itmno

Kobinath Ram · Answer

sir can you write the good code for this problem?

Nilesh Shah · Answer

Although this is not the best code in terms of performance you need to change logic to not open and close connection in for loop I will answer w.r.t. current code only Do you get any error in execution? I think your code will not run for all rows. con.Close(); is written 2 times in the loop that may throw an error  comment it in one place and see.