INSERT statement for One to Many relationship

Question

So I'm working on a project to catalog my games and I've run into an issue. I have a DB with two tables that breakdown as follows:

Game_Information

• gameId(PK) Is Identity(auto-increments)
• gamePrice
• name
• edition

Game_Notes

• noteId(PK) Is Identity(auto-increments)
• notes
• noteDate
• gameId(FK)

One game can have many notes, so there's a one to many relation between the two tables where a column within "Game_Notes" called "gameId" is a FK/references the primary key of "Game_Information" by the same name.

Now within Webmatrix, I've coded a page that lets me enter a game's price, name, and edition. Within that same page I can also add a note about the game. On post, page takes information from textboxes and tries to execute 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);

Now this works and puts the information in the correct tables but it leaves gameId(FK) within "Game_Notes" empty.

What I would like is that the number assigned to gameId(PK) in "Game_Information" reflects in gameId(FK) in "Game_Notes".

Anyone can help me on this or point to articles I can read? Thanks.

-Update- Made question clearer.

Answer 1

If I understand your question right, you're trying to accomplish two things:

1. Get the gameId of the newly inserted record in Game_Information
2. Use that gameId in the following insert statement to Game_Notes

While there are countless ways to do this, using an "output" value would be one suggestion. 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 on this: Sql Server return the value of identity column after insert statement

Then, use that id in the following statement:

db.Execute("INSERT INTO Game_Notes(gameId, notes, noteDate) VALUES (@0, @1, @2)", id, notes, noteDate);

Disclaimer: I haven't tested any of this, just trying to point you in the right direction.

Answer 2

You need to include the gameId in your second db.execute statement

db.Execute("INSERT INTO Game_Notes(gameId, notes, noteDate) VALUES (@0, @1, @2)", gameId, notes, noteDate);