mysql中NET更新_.net c#更新数据库mysql中的第一行

My problem is that:

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.

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

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

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.

Talk1:

I would do it all via sql without c# code. But it's 4am and I am off. Will look at it tomorrow

Talk2:

oh it's ok thanks.

Solutions1

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

}

(You can calculate :))

Hope helps,

Talk1:

Thank you. I owe you one

Talk2:

Hi! I got a chance to try your code but it displays an error. "Additional information: Specified cast is not valid." In int value = Convert.ToInt32((int)item["quantity"]);. Thanks :)

Talk3:

I don't have Convert in my answer. Cast and Comvert together will give that error. @Philip