CODEWITHSUNDEEP
c#sqldatabase
zhrgci
zhrgci

Reputation: 686

Can I get the ID of the data that I just inserted?

So I want to put a product in an order from an user in the database and the query goes as follows:

INSERT INTO [dbo].[Order](UserID, To_Adress, OrderDate, TravelTime, ItemCount, Status, TotalPrice) VALUES (1, 'BIKINI BOTTOM', '20191030 15:00:00', '20191030 15:35:00', 1, 'InTheMaking', 7.50)

After I put in the order I want to put the connection between the product and the order in the database but I have to get the OrderID that I just made. Is there a way to get the OrderID from the order that I just made?

INSERT INTO [dbo].[Order_Product](OrderID, ProductID) VALUES (?[dbo].[Order].OrderID?, 12)

EDIT:

Okay, so basically SCOPE_IDENTITY is the way to get it out of my database, but how do I use the SCOPE_IDENTITY in a second INSERT while still being in the same query? Do I just have to make 2 seperate queries for that?

Upvotes: 0

Views: 543

Answers (1)

Johnathan Barclay
Johnathan Barclay

Reputation: 20373

Try using the SQL OUTPUT clause:

INSERT INTO [dbo].[Order](UserID, To_Adress, OrderDate, TravelTime, ItemCount, Status, TotalPrice)
OUTPUT inserted.ID
VALUES (1, 'BIKINI BOTTOM', '20191030 15:00:00', '20191030 15:35:00', 1, 'InTheMaking', 7.50

In combination with SqlCommand.ExecuteScalar()

Upvotes: 2

Related Questions