CODEWITHSUNDEEP
c#sql-server-2008foreign-keys
Bip
Bip

Reputation: 913

Insert data into tables linked with foreign key

First of all I tried this Insert Data Into Tables Linked by Foreign Key and didn't get the answer.

I have 3 tables:

Table: Customers

ID -------LastName-------FirstName-------PhoneNumber

Table: Order

ID-------Status-------CustomerID

Table: OrderLine

ID-------OrderID-------Product-------Quantity-------PricePerUnit

I run the following query

SqlCommand myCommand2 = 
   new SqlCommand(@"INSERT INTO Order (Status, CustomerID) 
                    VALUES(13016, SELECT ID FROM Customers WHERE FirstName = 'Garderp')", 
                  myConnection);`

and it throws exception

Syntax error near Order

How can I add data into table with foreign key in SQL Server 2008 especially in this particular case?

Upvotes: 2

Views: 14472

Answers (6)

Icarus
Icarus

Reputation: 63956

It should be:

SqlCommand myCommand2 = new SqlCommand(@"INSERT INTO [Order] (Status, CustomerID) " 
   + " SELECT 13016, ID 
       FROM Customers 
       WHERE FirstName = 'Garderp')"
, myConnection);

Upvotes: 5

Johnny Circle
Johnny Circle

Reputation: 41

Order is keyword in SQL, you have to bracket all keyword if you use it as table name or column.

Upvotes: 2

Just Me
Just Me

Reputation: 244

try this

SqlCommand myCommand2 = new SqlCommand(@"INSERT INTO Order (Status, CustomerID) VALUES " + "(13016, SELECT ID FROM Customers WHERE FirstName = \'Garderp\')", myConnection);

if there are more than one customer with name "Gardep", this query would fail. You need to select only one record . Using TOP will be a better idea here 

SqlCommand myCommand2 = new SqlCommand(@"INSERT INTO Order (Status, CustomerID) VALUES " + "(13016, SELECT TOP 1 ID FROM Customers WHERE FirstName = \'Garderp\')", myConnection);

Upvotes: -2

Mithrandir
Mithrandir

Reputation: 25337

SqlCommand myCommand2 = new SqlCommand(@"INSERT INTO Order 
(Status, CustomerID)
VALUES " + "(13016, 
(SELECT ID FROM Customers WHERE FirstName = 'Garderp'))", myConnection);

Upvotes: 0

Kaerber
Kaerber

Reputation: 1653

Try using this query

INSERT INTO [Order]
( Status, CustomerID )
SELECT 13016, ID
    FROM Customers
    WHERE FirstName = 'Garderp'

Upvotes: 0

JNK
JNK

Reputation: 65157

ORDER is a reserved keyword in SQL Server (used in the ORDER BY operation).

You need to delimit that name with brackets:

"INSERT INTO [Order] (Status, CustomerID) VALUES "

That will cause SQL Server to treat it as an object name instead of reading it as a keyword.

Upvotes: 5

Related Questions