CODEWITHSUNDEEP
c#mysqlasp.netinsertforeign-keys
aiden87
aiden87

Reputation: 969

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...

Upvotes: 1

Views: 127

Answers (2)

Joshua Hysong
Joshua Hysong

Reputation: 1072

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();
    }

Upvotes: 1

ahmettek
ahmettek

Reputation: 11

Could it be that your parametry is missing? 

  cmd.Parameters.AddWithValue("@Customer_ID", input4.Text);

Upvotes: 1

Related Questions