Access database INSERT with subquery from within C#

Question

I have two access tables: users and scores.
Users table has columns: id(auto increment user id), username, password - id is primary key Scores table has columns: id(user id from users), highScore - has no primary key

In the C# method I take username and score as parameters and I want to insert into the scores table so that in the id field is the id of the user with the username that matches the supplied one.

So far the commands I've tried were:

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

This throws: Query input must contain at least one table or query.

So after sniffing around I've found that Access DB is kind of different from usual SQL so I tried using DLookup:

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

This goes through but the resulting row is empty. So I basically get an empty row which is not NULL.

I am absolutely certain that the parameters contain values as they are added before the command executes.

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 at it this way? Could I perhaps somehow JOIN the users and scores tables inside an insert?

Why don't you break it into two steps: one step to fetch the id of the username supplied as parameter, and the other step is to insert into scores table. This avoids the sub-query problem. — Ahmad
– Ahmad, Commented Dec 24, 2013 at 21:09
@Ahmad Well, I could, but the whole reason I decided to use subqueries is to avoid dividing the process into two steps when it could be done in one. — MrPlow
– MrPlow, Commented Dec 24, 2013 at 22:48
Have you tried ""username = @username"" instead of ""username = '@username'""? — Gord Thompson
– Gord Thompson, Commented Dec 24, 2013 at 23:13
@GordThompson Yea, I tried that and got the error Too few parameters. Expected 1. — MrPlow
– MrPlow, Commented Dec 24, 2013 at 23:27

afzalulh · Accepted Answer · 2013-12-25 13:38:11Z

2

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

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

Here's the block of code worked for me in 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();

edited Dec 25, 2013 at 13:38

answered Dec 25, 2013 at 3:52

afzalulh

7,9432 gold badges28 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

MrPlow Over a year ago

That works. Gotten used to mySQL and using INSERT INTO x(col1, col2,..) VALUES(val1, val2,..) so I never thought of using SELECT like that. So thanks. One thing though. I don't really like how you concatenated highScore. Especially since you've defined it as var. If any newbie came along to this couldn't he make his code vulnerable to SQL injection by using pure concatenation?

MrPlow Over a year ago

Are you sure? Because my insert command looks like this string insertCommand = @"INSERT INTO scores (id, highscore) SELECT id, @score FROM users WHERE username = @username;"; , and works as it should.

afzalulh Over a year ago

You are right, that is working... I must have mixed up with something else.

Collectives™ on Stack Overflow

Access database INSERT with subquery from within C#

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related