title: How to deduct from 1 row to another row with the same id if stock in 1st row is not enough (FIFO)
body: <p>I am trying to make an inventory using FIFO. Everything is working fine. It is already inserting and updating. My problem is whenever the 1st row has not enough quantity for the medicine asked by the customer, it changes the value to -1 and the deducts on the other row with the same id of the med and updates the stocks to a negative number also. Here is my Stocks_IN table:</p>
  
  <pre><code>stocksID | ID | datereceive | dateexpiry | quantity |remainingstocks
  1        | 1  | datehere    | datehere   | 10       | 10
  2        | 1  | datehere    | datehere   | 5        | 5
  </code></pre>
  
  <p>I have this code in my save button</p>
  
  <pre><code>private void button9_Click_1(object sender, EventArgs e)
          {
              DataGridViewRow row;
              int quant = 0;
  
              string query = @"INSERT INTO Stocks_Out(ID,idtran,quantity,user)
                                              Values(@ID,@idtran,@quantity,@user)";
  
              using (SqlConnection xcon = new SqlConnection(@"Server=MEAND\SQLEXPRESS;Database=SHC;Integrated Security=SSPI;"))
              {
                  try
                  {
                      xcon.Open();
  
                      for (int n = 0; n &lt; dataGridView2.Rows.Count - 1; n++)
                      {
                          row = dataGridView2.Rows[n];
                          using (SqlCommand xcom = new SqlCommand(query, xcon))
                          {
                              xcom.CommandType = CommandType.Text;
                              xcom.Parameters.AddWithValue("@ID", row.Cells["ID"].Value);
                              xcom.Parameters.AddWithValue("@idtran", row.Cells["idtran"].Value);
                              xcom.Parameters.AddWithValue("@quantity", row.Cells["quantity"].Value);
                              xcom.Parameters.AddWithValue("@user", row.Cells["user"].Value);
                              xcom.ExecuteNonQuery();
                              xcom.Dispose();
  
  
                              connection.Open();
                              quant = Convert.ToInt32(row.Cells["quantity"].Value.ToString());
  
                              SqlCommand xcom2 = connection.CreateCommand();
                              xcom2.CommandType = CommandType.Text;
                              xcom2.CommandText = "update Stocks_In set remainingquantity=remainingquantity-" + quant + " where ID= '" + row.Cells["ID"].Value + "'";
                              xcom2.ExecuteNonQuery();
                              xcom2.Dispose();
  
                          }
                      }
                      DialogResult result1 = MessageBox.Show("Are you sure you want to save this?",
                          "Important Question", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
  
                      if (result1 == DialogResult.Yes)
                      {
                          Med b = new Med();
                          b.Show();
                          this.Hide();
                      }
                  }
  
                  catch (Exception)
                  {
                      throw;
  
                  }
                  finally
                  {
                      xcon.Close();
                  }
  
              }
  
          }
  </code></pre>
  
  <p>So if I will get 11 medicine, it inserts into stocks_out and updates the remainingstocks in stocks_in. But instead of returning the value of the 1st row to 0 and 2nd value to 4, it updates it into -1(1st row) and -6(2nd row). Please help me :( This FIFO thing is making me go crazy.</p>
  
  <p>Here's the updated table for Stocks_In:</p>
  
  <pre><code>stocksID | ID | datereceive | dateexpiry | quantity |remainingstocks
  1        | 1  | datehere    | datehere   | 10       | -1
  2        | 1  | datehere    | datehere   | 5        | -6
  </code></pre>