Access to INSERT database with subquery from inside C #

Question

I have two access tables: users and ratings.
The user table has columns: identifier (auto-increment identifier), username, password - identifier - primary key The points table has columns: id (user identifier from users), highScore - does not have a primary key

In the C # method, I take the username and evaluate it as parameters, and I want to insert it into the rating table so that the id field contains a user ID with the user name that matches the one provided.

So far, the commands I tried were:

string insertCommand = @"INSERT INTO scores([id], [highScore])
                         VALUES((SELECT id FROM users WHERE username = @username), @score);";

These throws: the query input must contain at least one table or query.

So, after sniffing, I found that Access DB is different from regular SQL, so I tried using DLookup:

string insertCommand = @"INSERT INTO scores([id], [highScore])
                         VALUES(DLookup(""id"", ""users"", ""username = '@username'""), @score);";

This continues, but the resulting row is empty. So I basically get an empty string that is not NULL.

I am absolutely sure that the parameters contain values, as they are added before the command is executed.

command.Parameters.AddWithValue("@username", username);
command.Parameters.AddWithValue("@score", points);

command.CommandText = insertCommand;

command.ExecuteNonQuery();

So I have no idea what I'm doing wrong here. Should I even go for it like that? Can I somehow JOIN users and evaluate tables inside the insert?

Answer 1

If you don't mind changing the request a bit, the following should work:

string insertCommand = @"INSERT INTO scores (id, highscore)
                         SELECT id,  @highScore FROM users WHERE username = ?;";

Here the code block worked for me in the test:

var highScore = 99;
var username = "johndoe";
OleDbConnection con = new OleDbConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString());

string insertCommand = @"INSERT INTO scores (id, highscore)
                        SELECT id, @highScore FROM users WHERE username = @username;";

OleDbCommand cmd = new OleDbCommand(insertCommand, con);
cmd.Parameters.AddWithValue("@highScore", highScore);
cmd.Parameters.AddWithValue("@username", username);
con.Open();
cmd.ExecuteNonQuery();
con.Close();