Philip Philip - 2 months ago 9
C# Question

How to update a single row in C# mysql

My problem is that


  1. It updates all the data with the same id. Because I have a multiple data with the same id. [see the picture below].

  2. I want to update only the row with near expiration (30 days). I don't want to do anything with those items that are already expired.



Here is my code:

for (int i = 0; i < dataGridViewPOS.Rows.Count; i++)
{
cmd = new MySqlCommand(@"UPDATE inventory2 SET quantity = @quantity WHERE itemid = @itemid ORDER BY expiry", sqlconnection);
//somethinghere
sqlconnection.Open();
cmd.ExecuteNonQuery();
sqlconnection.Close();
}


Screenshot

Thanks

UPDATE:

for (int i = 0; i < dataGridViewPOS.Rows.Count; i++)
{
sqlconnection = new MySqlConnection(CLASS.con);
sqlconnection.Open();
cmd = new MySqlCommand(@"SELECT * FROM inventory2 WHERE itemid = @itemid ORDER BY expiry", sqlconnection);
//somethinghere
MySqlDataReader myReader = cmd.ExecuteReader();

while (myReader.Read())
{
newqty = myReader["quantity"].ToString();
}
myReader.Close();
sqlconnection.Close();

string newqty111 = (Convert.ToDouble(newqty) - Convert.ToDouble(dataGridViewPOS.Rows[i].Cells[3].Value)).ToString();

cmd = new MySqlCommand(@"UPDATE inventory2 SET quantity = @quantity WHERE itemid = @itemid AND CURDATE() < expiry ORDER BY expiry", sqlconnection);

//somethinghere
sqlconnection.Open();
cmd.ExecuteNonQuery();
sqlconnection.Close();
}

Answer

you need to check the expiration in the SQL ie

WHERE itemid = @itemid and CURDATE() < expiry

the exact nature of your where depends on how you define "Near"

WHERE itemid = @itemid and DATE_ADD(CURDATE(),INTERVAL 30 DAY) < expiry 

This would be within 30days of expiry

once you have your "Near" date you could then combine with a select max

AND expiry = (SELECT MAX(expiry) FROM inventory2 WHERE itemid = @itemid)

so get just the largest

EDIT: the information you added says you want to edit records not expired this is the exact opposite of what your original question seemed to imply as such you need to do

expiry BETWEEN CURDATE() and DATE_ADD(CURDATE(),INTERVAL 30 DAY)

this will get everything that will expiry in the next 30days but hasn't already expired

Comments