Validation method not working as intended

Question

Some Background

I am trying to create a form application which will manage orders of products. I am using an MS Access Database (but this isn't important to the question) to store all data about products, orders etc. I believe I have a fairly specific problem here and I cannot get my head around how I can make it work. Some suggestions/ideas would be great.

In my Orders Form I have an event btn_AddToOrder_Click which adds the information about a product (Product Name, Product SKU, Product Price Each (from database)) and the quantity to add (from a Numeric Up Down Object) into a Data Grid View.

Problem

My code for the btn_AddToOrder_Click Event validates that there is sufficient quantity of the desired product to add to the order before adding the product to the order. If the product is already in the order then it will simply add the quantity and if not it will add all the details. This all works fine so long as the order is a new one. If the order is one that already exists (i.e. the Update Button will be clicked after changes have been made) and I try to add any quantity of any product already in the order into the order then the Message Box with "There are only " + tempProductStock + " of these products in stock." displays. For example, if there is already a product "x" in the order with a quantity of 4, there are 2 of the same product "x" in stock and I try to add 2 of the same product "x" to the order (and thus to the Data Grid View and the ItemOrder List), then it will only add 2 of product "x" if I am making a new order. It doesn't if the order details were loaded into the forms objects already and a Message Box with "There are only 2 of these products in stock." would display.

Note

When I say that order information is being loaded into the forms objects I mean that there is a Combo Box on the form which lists all the saved orders and selecting one will fill out all the information about the order into the form.

btn_AddToOrder_Click Event Code

private void btn_AddToOrder_Click(object sender, EventArgs e)
    {
        bool productInOrder = false;
        tempProductQuantityToAdd = Convert.ToInt32(numericUpDown1.Value);
        int tempTotalProductQuantity = tempProductQuantityToAdd;
        foreach (DataGridViewRow row in dataGridView1.Rows)
        {
            if (row.Cells[0].Value.ToString() == tempProductName)
            {
                productInOrder = true;
                tempTotalProductQuantity += Convert.ToInt32(row.Cells[2].Value);
            }
        }
        if (cobo_Products.Text == "")
        {
            MessageBox.Show("Please select a product to add to the order.");
        }
        else if (tempProductQuantityToAdd < 1)
        {
            MessageBox.Show("Cannot add a product to an order without any quantity.");
        }
        else if (tempProductStock == 0)
        {
            MessageBox.Show("There are none of these products in stock.");
        }
        else if (tempTotalProductQuantity > tempProductStock) //CAUSING SOME ERRORS HERE?
        {
            MessageBox.Show("There are only " + tempProductStock + " of these products in stock.");
        }
        else
        {
            if (productInOrder)
            {
                MessageBox.Show("Product is already in this order. Product quantity will be added to the order.");
                foreach (DataGridViewRow row in dataGridView1.Rows) //Add quantity to specified product in data grid view
                {
                    if (row.Cells[0].Value.ToString() == tempProductName)
                    {
                        row.Cells[2].Value = Convert.ToInt32(row.Cells[2].Value) + tempProductQuantityToAdd;
                    }
                }
                //Change product quantity of specific product in list itemOrder
                //Currently deletes specific product then adds that product again with quantity increased
                for (int i = 0; i < itemOrder.Count; i++)
                {
                    if (itemOrder[i].productName == tempProductName)
                    {
                        var productDetails = new ProductDetails();
                        productDetails.productName = tempProductName;
                        productDetails.productSKU = tempProductSKU;
                        productDetails.productRP = tempProductRP;
                        productDetails.productWP = tempProductWP;
                        productDetails.productQuantity = tempTotalProductQuantity;
                        productDetails.previousProductQuantity = itemOrder[i].previousProductQuantity;
                        productDetails.productStock = tempProductStock;
                        productDetails.productSold = tempProductSold;
                        itemOrder.RemoveAt(i);
                        itemOrder.Add(productDetails);
                    }
                }
            }
            else if (tempProductQuantityToAdd >= 1)
            {
                //Add product to list
                var productDetails = new ProductDetails();
                productDetails.productName = tempProductName;
                productDetails.productSKU = tempProductSKU;
                productDetails.productRP = tempProductRP;
                productDetails.productWP = tempProductWP;
                productDetails.productQuantity = tempProductQuantityToAdd;
                productDetails.previousProductQuantity = productDetails.productQuantity;
                productDetails.productStock = tempProductStock;
                productDetails.productSold = tempProductSold;
                itemOrder.Add(productDetails);
                //Add the product to data grid
                dataGridView1.Rows.Add(tempProductName, tempProductSKU, tempProductQuantityToAdd, tempProductRP);
            }
            //Total the order
            orderTotal += tempProductRP * tempProductQuantityToAdd;
            if (txt_DiscountFixed.Text != "0")
            {
                discountFixed = Convert.ToDecimal(txt_DiscountFixed.Text);
                txt_Total.Text = "Total: £" + (orderTotal - discountFixed + shippingCost);
            }
            else if (txt_DiscountPercentage.Text != "0")
            {
                discountPercentage = Convert.ToInt32(txt_DiscountPercentage.Text);
                txt_Total.Text = "Total: £" + (Math.Round(Convert.ToDecimal(orderTotal) * (1 - Convert.ToDecimal(discountPercentage) / 100), 2) + shippingCost);
            }
            else
            {
                txt_Total.Text = "Total: £" + (orderTotal + shippingCost);
            }
            //Test the list itemOrder - display its contents
            //StringBuilder sb = new StringBuilder();
            //foreach (ProductDetails item in itemOrder)
            //{
            //    sb.AppendLine("Name: " + item.productName + ", SKU: " + item.productSKU + ", RP: " + item.productRP + ", WP: " + item.productWP + ", Qty: " + item.productQuantity + ", Stock: " + item.productStock + ", Sold: " + item.productSold);
            //}
            //MessageBox.Show(sb.ToString());
        }
    }

How tempProductStock is set

private void cobo_Products_SelectedIndexChanged(object sender, EventArgs e)
    {
        try
        {
            connection.Open();
            OleDbCommand command = new OleDbCommand();
            command.Connection = connection;
            string query = "select * from Products where ProductName= '" + cobo_Products.Text + "'";
            //MessageBox.Show(query);
            command.CommandText = query;
            OleDbDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                tempProductName = reader["ProductName"].ToString();
                tempProductSKU = reader["ProductSKU"].ToString();
                tempProductRP = Convert.ToDecimal(reader["RP"]);
                tempProductWP = Convert.ToDecimal(reader["WP"]);
                tempProductStock = Convert.ToInt32(reader["Stock"]);
                tempProductSold = Convert.ToInt32(reader["Sold"]);
            }
            connection.Close();
            //MessageBox.Show(tempProductName +", "+ tempProductSKU +", "+ tempProductRP +", "+ tempProductWP +", "+ tempProductStock +", "+ tempProductSold);
        }
        catch (Exception ex)
        {
            connection.Close();
            MessageBox.Show("Error " + ex);
        }
    }

I would greatly appreciate some ideas of how I can make this work. Thanks in advance.

I appreciate I may not have given all the information that may be needed to answer my question. Let me know if there is anything more I can provide. Unfortunately I cannot add pictures to my question which does make things a little difficult for me to explain.

Answer 1

Using your example:

1.   Product: Gloves          Qty in order: 4          Qty in stock: 2

The user then chooses to add 2 of the same product and your method will do the following calculations:

set tempTotalProductQuantity to 2
add existing orders (4) to tempTotalProductQuantity (2)
if tempTotalProductQuantity is more than in stock.. (6 > 2)
  display "There are only 2 in stock.." (the 2 from stock variable) 

What's the solution then?

Since you've already added 4 to the order, and deducted this from the stock, you should not check against tempTotalProductQuantity (since this is the total number, including the already deducted quantity). Instead check against tempProductQuantityToAdd, the quantity the user wants to add right now.

else if (tempProductQuantityToAdd > tempProductStock)

Of course there are many ways to fix this. Another would be to increase the tempProductStock variable with what already exists:

else if (tempTotalProductQuantity > (tempTotalProductQuantity - tempProductQuantityToAdd + tempProductStock))

This will check the total order quantity against the stock + what is in the order. So in this case it would be 6 > (6 - 2 + 2).

Edit:

This assumed that you deducted from stock when inserting a new order. Otherwise I don't know how an order could contain 4 products, with only 2 in stock.

The solution assumes that you re-set tempProductStock, by cobo_Products_SelectedIndexChanged or other method, after an order has been added to the table.