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

Kobinath Ram

9y1.5kLearn iOS Programming

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();

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Rafnas T P9y ago
try as below
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();
con.Close();
}
MessageBox.Show("Updateeeeeeeeeeeeeeeeeeeeee");
Nilesh Shah9y ago
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 Ram9y ago
sir can you write the good code for this problem?
Nilesh Shah9y ago
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.