Selecting an id from database using razor pages?

Question

I am the following. As a user registers his account name and password are put into an existing table. Then his id gets read from that table and another table is made using that id as the tablename.

Now inserting works great but reading the Id from that table seems to give me some problems.

Now it would be great if I was even able to get that id to display on my webpage. i feel like when I am able to do that I can just do something like CREAT TABLE + @Id + etc.

This is what I have so far

var gebruikersnaam = Request.Form["registnaam"];
var wachtwoord = Request.Form["registpassword"];

if (IsPost && Request.Form["registreren"] == "Registreren")
{
    var insertCommand = db.Execute("INSERT INTO Accounts (gebruikersnaam, wachtwoord) VALUES(@0, @1)", gebruikersnaam, wachtwoord);
    var id = db.Query("SELECT * FROM Accounts WHERE gebruikersnaam = @0", gebruikersnaam);
    Response.Write(@id);
}

And this is what the Response.Write(@id) shows me on the webpage:

System.Collections.ObjectModel.ReadOnlyCollection`1[System.Object]

I actually figured out that this all got fixed by using db.queryvalue instead of just db.query now the only other problem iam running into is when trying something like db.execute("CREATE TABLE "+ Id +" etc") I get an error saying incorrect syntax near and then my id number. — Joop de Graaf, Mar 09 '19 at 19:47
See this question https://stackoverflow.com/questions/10999396/how-do-i-use-an-insert-statements-output-clause-to-get-the-identity-value as well for more info about using an OUTPUT clause. — Brad Patton, Mar 11 '19 at 18:40
You can't name a table just as a number you need to prefix it. For example ($"CREATE TABLE T{id}" ). Put I would caution against creating tables for each user. Probably better so all that data is in rows in tables rather than as separate tables. — Brad Patton, Mar 11 '19 at 18:42

score 1 · Answer 1 · answered Mar 11 '19 at 08:08

Try merging the create and select query into one.

INSERT INTO Accounts (gebruikersnaam, wachtwoord) 
OUTPUT Inserted.Id
VALUES('value1', 'value2')

Getting the Id using a separate query could be a problem in the future especially if the data becomes large, doing this optimize the speed of data retrieval in DB like getting the Id of an inserted record.

Selecting an id from database using razor pages?

1 Answers1