INSERT statement for one to large relationship
So I am working on a project to catalog my games and I am facing a problem. I have a DB with two tables that are split like this:
Game_Information
- gameId (PK) is the id (auto-increment)
- gamePrice
- name
- edition
Game_Notes
- noteId (PK) Identity (autoincrement)
- notes
- noteDate
- GameID (FC)
There can be many notes in one game, so there is one-many relationship between the two tables, where the column inside "Game_Notes" called "gameId" is FK / refers to the primary key "Game_Information" with the same name.
Now in Webmatrix I have coded a page that allows me to enter game price, name and release. On this page, I can also add a note about the game. In the column, the page takes information from the text boxes and tries the following:
db.Execute("INSERT INTO Game_Information (gamePrice, name, edition) VALUES (@0, @1, @2)", gamePrice, name, edition);
db.Execute("INSERT INTO Game_Notes(notes, noteDate) VALUES (@0, @1)", notes, noteDate);
This now works and puts the information in the correct tables, but it leaves the gameId (FK) in "Game_Notes" blank.
I would like the number assigned to gameId (PK) in "Game_Information" to be reflected in gameId (FK) in "Game_Notes".
Can anyone help me with this or point out articles I can read? Thank.
-Update- Made the question clearer.
source to share
If I understand your question correctly, you are trying to accomplish two things:
- Get the gameId of the newly inserted entry in Game_Information
- Use this game in the following insert instruction in Game_Notes
While there are many ways to do this, using an "output" value would be one sentence. Something like:
var id = db.QueryValue("INSERT INTO Game_Information (gamePrice, name, edition) output Inserted.gameId VALUES (@0, @1, @2)", gamePrice, name, edition);
Check out some posts for more information: Sql Server returns identity column value after insert statement
Then use that id in the following expression:
db.Execute("INSERT INTO Game_Notes(gameId, notes, noteDate) VALUES (@0, @1, @2)", id, notes, noteDate);
Disclaimer: I haven't tested this, just trying to point you in the right direction.
source to share