Hi what is the correct way to insert data from two tables to one table ,so they are 3 tables, my fist table is Tbl_order has Order_IDand the second Tbl_Cashier has Cashier_ID and the 3rd is Tbl_Finalized has Order_ID and Cashier_ID . the full design of the tables
Tbl_Cashier
CREATE TABLE [dbo].[Tbl_Cashier] (
[Cashier_ID] INT IDENTITY (1, 1) NOT NULL,
[FName] VARCHAR (50) NOT NULL,
[MName] VARCHAR (50) NULL,
[LName] VARCHAR (50) NOT NULL,
[Address] NVARCHAR (50) NOT NULL,
[ContactNo] VARCHAR (50) NOT NULL,
[Email] NVARCHAR (50) NOT NULL,
[Age] INT NOT NULL,
[Gender] VARCHAR (50) NOT NULL,
[Password] VARCHAR (50) NOT NULL,
[role] NVARCHAR (50) NULL,
PRIMARY KEY CLUSTERED ([Cashier_ID] ASC)
);
Tbl_order
CREATE TABLE [dbo].[Tbl_order] (
[Order_ID] INT IDENTITY (1, 1) NOT NULL,
[CustomerNo] INT NOT NULL,
[OrderName] NVARCHAR (MAX) NOT NULL,
[Quantity] INT NOT NULL,
[Price] FLOAT (53) NOT NULL,
[Serves_way] INT NOT NULL,
[Date] DATETIME NOT NULL,
PRIMARY KEY CLUSTERED ([Order_ID] ASC),
CONSTRAINT [Serves_wayFK] FOREIGN KEY ([Serves_way]) REFERENCES [dbo].[Tbl_Serve] ([Serve_ID])
);
Tbl_Finalized
CREATE TABLE [dbo].[Tbl_Finalized] (
[Finalized_ID] INT IDENTITY (1, 1) NOT NULL,
[Order_ID] INT NOT NULL,
[Cashier_ID] INT NOT NULL,
[Customer_ID] INT NOT NULL,
PRIMARY KEY CLUSTERED ([Finalized_ID] ASC),
CONSTRAINT [CashierFK] FOREIGN KEY ([Cashier_ID]) REFERENCES [dbo].[Tbl_Cashier] ([Cashier_ID]),
CONSTRAINT [CustomerFK] FOREIGN KEY ([Customer_ID]) REFERENCES [dbo].[Tbl_Customer] ([Customer_ID])
);
so when I log in the application I use Tbl_Cashier by FName and password
I log in , How to get the Cashier_ID that logged in and insert it to Cashier_ID in Tbl_Finalized ? this is my log in code :
private void but_log_in_Click(object sender, EventArgs e)
{
if (tbx_username.Text == "" || Tbx_Password.Text == "")
{
MessageBox.Show("Please provide UserName and Password");
return;
}
try
{
using (SqlConnection con = new SqlConnection(connectionString))
using (SqlCommand cmd = new SqlCommand("Select * from
Tbl_Cashier where FName=@username and Password=@password", con))
using (SqlDataAdapter adapt = new SqlDataAdapter(cmd))
{
con.Open();
cmd.Parameters.AddWithValue("@username", tbx_username.Text);
cmd.Parameters.AddWithValue("@password", Tbx_Password.Text);
HabibisGrll.Globals.sss = tbx_username.Text;
DataSet ds = new DataSet();
adapt.Fill(ds);
con.Close();
int count = ds.Tables[0].Rows.Count;
//If count is equal to 1, than show frmMain form
if (count == 1)
{
MessageBox.Show("Login Successful!");
this.Hide();
HabibisGrll fm = new HabibisGrll();
fm.Show();
}
else
{
MessageBox.Show("Login Failed!");
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
And from the application I use listboxes to insert the orders to Tbl_order i want to insert the Order_ID it has been i selected and inserted from Tbl_orderto Order_ID in Tbl_Finalized. my code that insert all orders to the database from listbox
private void Order()
{
using (SqlConnection connection = new SqlConnection(connectionString1))
{
String query = "INSERT INTO Tbl_order (OrderName,Quantity,Price,Serves_way,Date,CustomerNo) VALUES (@OrderName,@Quantity, @Price,'" + servers + "','" + time1.ToString(format1) + "','" + Globals.order + "' )";
Globals.order++;
connection.Open();
using (SqlCommand command = new SqlCommand(query, connection))
{
// Add the length of this text column as third parameter...
command.Parameters.Add("OrderName", SqlDbType.NVarChar, 50);
command.Parameters.Add("Quantity", SqlDbType.Int);
command.Parameters.Add("Price", SqlDbType.Money);
command.Prepare();
for (int i = 0; i < lst_OrderName.Items.Count; i++)
{
command.Parameters[0].Value = lst_OrderName.GetItemText(lst_OrderName.Items[i]);
command.Parameters[1].Value = Convert.ToInt32(lst_QTY.Items[i]);
command.Parameters[2].Value = Convert.ToDouble(lst_Price2.Items[i]);
command.ExecuteNonQuery();
/* int totalRows = CustomerNo.Rows.Count;
int totalCols = CustomerNo.Columns.Count;
string value = CustomerNo.Rows[totalRows - 1][totalCols - 1].ToString();*/
}
}
}
}
