SQL INSERT with parameter as query

Question

How can I insert parameter with value from database. I have some field and I should insert value from this database + 1 (with plus one)

For example

myCommand.CommandText =
            "INSERT INTO GAMES (GAME_NR, GAME_PLAYER_ID, GAME_NRONTABLE, GAME_ROLE_ID) " &
            " VALUES (@game_nr, @game_player_id, @game_nrontable, @game_role_id)"

'Example
myCommand.Parameters.Add("@game_nr", SqlDbType.Int).Value = **"(SELECT MAX(GAME_NR) FROM GAMES)" + 1**

Gordon Linoff · Accepted Answer · 2018-01-20 19:55:41Z

2

You don't. You make GAME_NR and auto-incremented primary key:

create table games (
    game_nr int auto_increment primary key,
    . . .
);

Then you do the insert as:

INSERT INTO GAMES (GAME_PLAYER_ID, GAME_NRONTABLE, GAME_ROLE_ID)
    VALUES (@game_player_id, @game_nrontable, @game_role_id);

Let the database do the work.

answered Jan 20, 2018 at 19:55

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

Başar Kaya · Accepted Answer · 2018-01-20 22:01:04Z

0

You don't need the parameter, you can try following code.

myCommand.CommandText =
            "INSERT INTO GAMES (GAME_NR, GAME_PLAYER_ID, GAME_NRONTABLE, GAME_ROLE_ID) " &
            " VALUES ((SELECT MAX(GAME_NR) + 1 FROM GAMES), @game_player_id, @game_nrontable, @game_role_id)"

But it looks like a primary key of the table. If Game_Nr is pr, You should use auto-inc. identity, then you don't need this param.

It will be.

myCommand.CommandText =
                "INSERT INTO GAMES (GAME_PLAYER_ID, GAME_NRONTABLE, GAME_ROLE_ID) " &
                " VALUES (@game_player_id, @game_nrontable, @game_role_id)"

edited Jan 20, 2018 at 22:01

answered Jan 20, 2018 at 19:55

Başar Kaya

3646 silver badges14 bronze badges

3 Comments

Littlefoot Over a year ago

How would that behave in a multi-user environment? For example, current MAX(GAME_NR) = 100. Then you and me run INSERT you suggest, and for both of us MAX + 1 = 101. If GAME_NR was a primary key ... hm?

Başar Kaya Over a year ago

thank Littlefoot for warning me, I will edit my answer. But I don't agree with you. I think, there will first execute generate 101 then 102.

Littlefoot Over a year ago

Until user COMMITs changes (i.e. that INSERT statement), everyone else, who selects MAX(GAME_NR) would see the old value. So, if there were 20 users who did INSERT INTO with MAX(GAME_NR) + 1, all of them would want to insert 101, but only the first one who commits would succeed. Everyone else's attempt would fail because of primary key violation. That's why, generally speaking, MAX + 1 is a bad idea. Though, if AUTOCOMMIT is enabled, database would commit itself so those problems would, probably, be avoided. Personally, I prefer choosing when to commit.

Collectives™ on Stack Overflow

SQL INSERT with parameter as query

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related