fokz8 fokz8 - 6 days ago 6
C# Question

How to insert data into two tables

I want to know how to insert data into two different tables.

I have

Orders table, which has:

OrderID, info, Price, VAT, Customer_ID

and Customer table

CustomerID, Name, LastName

Now i want to enter new order, which would have primary key of 1.

How can i write SQL that would enter this order for specific person,..let's say a person who has id of 1 in customer table?

This is what i have so far, but i just can't find solution

using (SqlCommand cmd = new SqlCommand("INSERT INTO [Orders] (info, Price, VAT, Customer_ID) VALUES (@info, @Price, @VAT, @Customer_ID)"))
{
cmd.Connection = conn;
cmd.Parameters.AddWithValue("@info", input1.Text);
cmd.Parameters.AddWithValue("@Price", input2.Text);
cmd.Parameters.AddWithValue("@VAT", input3.Text);
conn.Open();
cmd.ExecuteNonQuery();
}


UPDATE

i don't want to enter foreign key(id) via textbox, but get it from current logged in user...

Answer

Unless I'm misunderstanding your question it should be a simple matter of adding another parameter.

using (SqlCommand cmd = new SqlCommand(
    "INSERT INTO [Orders] (info, Price, VAT, Customer_ID) VALUES (@info, @Price, @VAT, @Customer_ID)"))
{
    cmd.Connection = conn;
    cmd.Parameters.AddWithValue("@info", input1.Text);
    cmd.Parameters.AddWithValue("@Price", input2.Text);
    cmd.Parameters.AddWithValue("@VAT", input3.Text);
    cmd.Parameters.AddWithValue("@Customer_ID", input4.Text);
    conn.Open();
    cmd.ExecuteNonQuery();
}

If you do not have the customer_ID as an input field and want to make it always create an order for customer "1" then do the following

using (SqlCommand cmd = new SqlCommand(
    "INSERT INTO [Orders] (info, Price, VAT, Customer_ID) VALUES (@info, @Price, @VAT, 1)"))
{
    cmd.Connection = conn;
    cmd.Parameters.AddWithValue("@info", input1.Text);
    cmd.Parameters.AddWithValue("@Price", input2.Text);
    cmd.Parameters.AddWithValue("@VAT", input3.Text);
    conn.Open();
    cmd.ExecuteNonQuery();
}

If instead you are wanting to do something a bit more dynamic with looking up which customer an order is for we will need more information about your data input first. You'd need to do a select query to find the customer_ID and then pass that in as a parameter or just sub-select within the insert query.

EDIT:

Here's code using the dynamic query as you mentioned in the comments

 using (SqlCommand cmd = new SqlCommand(
        "INSERT INTO [Orders] (info, Price, VAT, Customer_ID) SELECT @info, @Price, @VAT, CustomerID FROM Customer WHERE Name = @Username)"))
    {
        cmd.Connection = conn;
        cmd.Parameters.AddWithValue("@info", input1.Text);
        cmd.Parameters.AddWithValue("@Price", input2.Text);
        cmd.Parameters.AddWithValue("@VAT", input3.Text);
        cmd.Parameters.AddWithValue("@Username", SomeVariableWithUsername);
        conn.Open();
        cmd.ExecuteNonQuery();
    }