Insert statement with Output Clause

Question

I have the following two tables:

Game_Information

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

Game_Notes

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

I'm currently trying to code a cshtml page in WebMatrix that will allow me to add information about my game collection across two tables but I'm having trouble getting the gameId to match across both tables so, multiple notes reference one game.

I tried the following:

var id = db.Execute("INSERT INTO Game_Information (gamePrice, name, edition, addedDate) output Inserted.gameId VALUES (@0, @1, @2, @4)", gamePrice, name, edition, date); 

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

Which puts the information into the desired columns but "gameId" inserted into Game_Notes always defaults to "1" instead of the correct id of the newly inserted game.

So I tried the following:

db.Execute("INSERT INTO Game_Information (gamePrice, name, edition) output Inserted.gameId, Inserted.date INTO Game_Notes(gameId, noteDate) VALUES (@0, @1, @2)", gamePrice, name, edition); 

This inserts the correct id into "gameId", so there is a match between the two tables but now I'm lost as how to get the "notes" variable into that same row. Doing a second Insert is clearly out and going for an Update on the last row of the table doesn't seem like a wise idea.

Anyone have any tips on what I can do here? I'm thinking restructuring the Insert to accommodate for the Notes variable is the key but I keep hitting a wall on it.

Answer 1

As I saidDatabase.Execute method returns the count of records affected by the SQL statement. So you were inserting one row and getting 1 as a result.

The Execute method is used to perform non-query commands on a database, such as the SQL Drop, Create, Delete, Update, and Insert commands. https://msdn.microsoft.com/en-us/library/webmatrix.data.database.execute(v=vs.111).aspx

You need Database.QueryValue method that executes a SQL query that returns a single scalar value as the result.

var id = db.QueryValue(...