CODEWITHSUNDEEP
mysqlsqlvb.netparameters
Emma W.
Emma W.

Reputation: 215

SQL INSERT with parameter as query

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**

Upvotes: 0

Views: 559

Answers (2)

Başar Kaya
Başar Kaya

Reputation: 364

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)"

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269553

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.

Upvotes: 2

Related Questions