Good day. I'm having a trouble on how I will able to update my data
I have 3 tables: Orders, Products, & OrderDetails Table
Orders - OrderID (PK), CustomerName, OrderDate, TotalAmount, Status
Products - ProductD (PK), ProductName, Category, Price, Quantity, CriticalLevel
OrderDetails - DetailID (PK), OrderID (FK), ProductID (FK), Quantity, Price, Amount
The process is that everytime I'll order a product, the quantity from the Products table will update, decreasing its numbers (ex. when I ordered 2 pcs. of the product, the updated qty. would be total quantity - 2. Its almost similarly called to inventory). In addition, after each time I order, the status will be put as 'COMPLETE'
Anyway, that is not my problem. I have this ViewOrder.cs which display the summary of orders. It's look like this:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Configuration;
namespace FirstApplication
{
public partial class ViewOrders : Form
{
DataTable dtOrders;
private SqlConnection cn;
public ViewOrders()
{
InitializeComponent();
}
private void ViewOrders_Load(object sender, EventArgs e)
{
OpenConnection();
ViewOrder();
CloseConnection();
}
private void OpenConnection()
{
cn = new SqlConnection();
cn.ConnectionString =
ConfigurationManager.AppSettings["AppConnection"].ToString();
cn.Open();
}
private void CloseConnection()
{
if (cn != null)
{
if (cn.State == ConnectionState.Open)
{
cn.Close();
}
}
}
private void btnCancelOrder_Click(object sender, EventArgs e)
{
OpenConnection();
CancelOrder();
ViewOrder();
CloseConnection();
}
private void ViewOrder()
{
SqlCommand cmdSelect = new SqlCommand();
cmdSelect.Connection = cn;
cmdSelect.CommandType = CommandType.Text;
cmdSelect.CommandText = "Select * from orders";
SqlDataAdapter daSelect = new SqlDataAdapter();
daSelect.SelectCommand = cmdSelect;
dtOrders = new DataTable();
daSelect.Fill(dtOrders);
dgOrders.DataSource = dtOrders;
}
private void CancelOrder()
{
if (dgOrders.Rows.Count == 0 || dgOrders.SelectedRows.Count == 0)
{
MessageBox.Show("Select first an order to remove.");
return;
}
else
{
MessageBox.Show("Are sure you want to cancel this order.", "",
MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (Convert.ToBoolean(DialogResult.Yes))
{
OpenConnection();
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "UPDATE Orders SET Status = 'Cancelled' " +
"WHERE OrderID = @OrderID";
cmd.Parameters.Add("@OrderID", SqlDbType.Int).Value =
dgOrders.SelectedRows[0].Cells[0].Value.ToString();
cmd.ExecuteNonQuery();
/*Upon executing, the 'COMPLETE' status will be changed to 'CANCELLED' when I click the btnCancelOrder. (ALREADY RUNNING)
My problem is, if I'll able to change the status of my order, is there any way that the quantity I ordered, but then was cancelled, will be put back again in the product table, or should i say rollback the transaction?
For example, I ordered 2 pcs. of Product A which has a total quantity of 10 pcs. After ordering, the total quantity will become 8. But then, when I cancelled my order, 2 pcs. will be brought back again, so that the 8 in the total quantity will become 10 again. Getting my point?
CloseConnection();
}
else
return;
}
}
}
}
any help would do. sorry if it's kinda long.
I do hope someone would help me out in this problem.