Philip Philip - 3 months ago 11
MySQL Question

Update the First Row in a database C# mysql

My problem is that:


  1. I want to Select one row from the database, The data should be arrange in expiry (the ones that are not yet expired and I don't want to limit it). The items that passed the current date must be left alone. And with all the same ITEMID lets say I00001.

  2. Then after selecting I want to Update the first row of the database. if the quantity reaches 0 then it will go the next row to update and so on.



Here is my example


  • Here is the current database screenshot.

  • I want select the itemid where = I00001 and deduct 50.

  • Then it should look like this

  • Then I want to arrange based on the expiry as I mentioned above.

  • Select the first row.

  • Deduct the 50 from the quantity. (as I also mentioned above).



Here is my code:

for (int i = 0; i < dataGridView.Rows.Count; i++)
{
cmd = new MySqlCommand(@"SELECT * FROM inventory2 WHERE itemid = @itemid ORDER BY expiry ", sqlconnection);

//CODE HERE

MySqlDataReader myReader = cmd.ExecuteReader();

while (myReader.Read())
{
//CODE HERE
}

//DEDUCTION OF QUANTITY HERE

myReader.Close();
sqlconnection.Close();

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

//CODE HERE

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


I'm open for another suggestion in doing this. I hope you understand my problem. Thank you very much. I'm sorry I cannot send another screenshot.

Answer

Try this,

void UpdateQuantity() {
        // your connection string
        MySqlDataAdapter adp = new MySqlDataAdapter("Select * from table where ItemID = " + 13 + " Order BY expiry", cnn); // I have test db and I used it
        DataTable dt = new DataTable();
        adp.Fill(dt);
        int deductNum = 50;
        foreach (DataRow item in dt.Rows)
        {
            int value = (int)item["quantity"];
            if (value >= deductNum) // if had enough stock we don't need to pass the next line
            {
                int result = value - deductNum;
                item["quantity"] = result.ToString();
                break; // so need to exit from loop
            }
            else
            {
                deductNum -= value; // else we deduct value count from deduction
                item["quantity"] = 0; // quantity finished so it will be 0
            }
        }
        MySqlCommandBuilder cmb = new MySqlCommandBuilder(adp);
        adp.UpdateCommand = cmb.GetUpdateCommand();
        adp.Update(dt);
        dataGridView1.DataSource = dt; //to show the result
    }

enter image description here (You can calculate :))

Hope helps,