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.

Are you actually looking for something like this? http://stackoverflow.com/questions/1920558/what-is-the-difference-between-scope-identity-identity-identity-and-ide — James Z, Jun 17 '15 at 15:04
I don't know what is webmatrix. Are you sure Execute function returns you what you select? may be 1 is just a result of this function when it is succeeds? — Giorgi Nakeuri, Jun 17 '15 at 15:05
I'm aware of scope_Identity but I honestly feel it would be overkill in this situation. @Giorgi, if that is the case, how would I get the db.execute to pass the gamerId to the variable "id"? — LearningProgrammer, Jun 17 '15 at 15:10
After you got the correct id from your modified code, what did you do with it? Your question shows no effort to use it in your 2nd insert. — Dan Bracuk, Jun 17 '15 at 15:15
Why would I try to use it in a second Insert? Once I modified the code, the Insert of "gameId" into Game_Notes is happening within the first Insert. Trying a second Insert at that point would add the notes variable into another row other than the newly created, as insert always creates a new row. Hence why I dropped it in the altered code. — LearningProgrammer, Jun 17 '15 at 15:22

score 2 · Accepted Answer · answered Jun 17 '15 at 15:25

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

Thanks Giorgi Nakeuri. Complete failure on my part not putting more thought on what the "1" was representing. Well, it's another poor mistake turned into a lesson. — LearningProgrammer, Jun 17 '15 at 15:38

Insert statement with Output Clause

1 Answers1