0

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?

zhrgci
  • 584
  • 1
  • 6
  • 25
  • 1
    Does this answer your question? [Get the last inserted row ID (with SQL statement)](https://stackoverflow.com/questions/9477502/get-the-last-inserted-row-id-with-sql-statement) – Cid Nov 06 '19 at 10:51
  • I think you will find your answer here : https://stackoverflow.com/questions/7917695/sql-server-return-value-after-insert – MoDDiB Nov 06 '19 at 10:52
  • Where does order ID come from? You should storage it in a variable and then use it in both querys. And alternative way that may work is to do the query "SELECT OrderId FROM [dbo],[Order] LIMIT 1", which generally returns last record added – CR0N0S.LXIII Nov 06 '19 at 10:53
  • you can try `@@identity`. `INSERT INTO [dbo].[Order_Product](OrderID, ProductID) VALUES (@@identity, 12)` – Krishna Varma Nov 06 '19 at 10:53
  • If I understand this correctly, OrderId is a sequence or a serial or something that autoincrements, and you not give it a value on INSERT. I would recommend you not to do that. Instead, before INSERT, run a "SELECT count(*) FROM [dbo].[order]", add 1 to that, and use that number as your orderId in INSERT. – CR0N0S.LXIII Nov 06 '19 at 10:56
  • @Cid Okay so `SCOPE_IDENTITY` would get the last `OrderID` how would I be able to immediately use it in the other table `Order_Product`? – zhrgci Nov 06 '19 at 10:59
  • 1
    Does this answer your question? [How to get last inserted id?](https://stackoverflow.com/questions/5228780/how-to-get-last-inserted-id) – BugFinder Nov 06 '19 at 11:03
  • 2
    @CR0N0S.LXIII that is horrible and wide open to concurrency issues. The database should assign the ID itself. – Johnathan Barclay Nov 06 '19 at 11:06
  • @KrishnaMohanVarma How would I declare it? With `@@ID.SCOPE_IDENTITY`? – zhrgci Nov 06 '19 at 12:17
  • @BugFinder Well...partially....i know how to get it and return it now but i still want to use the ID to insert it in a second query. – zhrgci Nov 06 '19 at 12:23
  • Well if you have have the variables you should be able to use them in the next query – BugFinder Nov 06 '19 at 12:24
  • @BugFinder But there's no way to do it in one go? – zhrgci Nov 06 '19 at 12:26
  • 1
    it doesnt feel like youve done much googling - have you read https://stackoverflow.com/questions/11534500/using-identity-or-output-when-inserting-into-sql-server-view – BugFinder Nov 06 '19 at 12:33
  • @BugFinder Well...I'm still a really big novice at coding and stuff, but thanks for the help :P – zhrgci Nov 06 '19 at 12:50

1 Answers1

2

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

Johnathan Barclay
  • 18,599
  • 1
  • 22
  • 35