how to get id of last inserted row

Question

I have a table in which following are the columns and their data-types

id=>AI,PK,int(11)
param_id=>varchar(45)
type=>varchar(45)
stationname=>varchar(45)
param=>varchar(45)

now when i insert a row in database:(database is MySQL, programming language c#)

insert into param_reference (Param_Id,Type,StationName,Param) values ('Res','" + Type + "','" + damName + "','maxQ')"

The row in table is like:

'1', 'Res', 'Reservoirs', 'B', 'maxQ'

param_id must be like 'Res1' where 1 is the auto-generated id. But when we insert this row we can't insert 'Res1' coz id is unknown and we get only after insertion. I did try as soon as after insertion of this above row

select id from param_reference where Type='" + Type + "',StationName='" + damName + "',Param='maxQ',Param_id='Res'

but it always returns null, it does not return the id. How can i solve this ? How can I get the id as soon as it is inserted. Also it should be the last id inserted coz there can be many rows like:

'1', 'Res', 'Reservoirs', 'B', 'maxQ'
'2', 'Res', 'Reservoirs', 'B', 'maxQ'
'3', 'Res', 'Reservoirs', 'B', 'maxQ'
'4', 'Res', 'Reservoirs', 'B', 'maxQ'

but Res will be having the id part too..

so i actually want to update 'Res' as 'Res2' as soon as second row is inserted.

I think those are not duplicates, because in this case, getting the last insert ID is not the problem, but accessing it in the insert query. — 11684, May 19 '14 at 15:43
my question is completely different.. why marked as duplicate i did see those solution and then asked this coz i didnt find my answer there..this is ridiculous.. — Silver, May 20 '14 at 03:39
Here's an alternative solution: you already have both parts of the string you want as a result stored already. Instead of storing `'Res7'` why not do `"Res"+id`? — 11684, May 20 '14 at 15:32
By the way, I meant appending the ID to `"Res"` after you retrieved the ID from the database, where you'd only store the ID (and not `"Res"`, unless that string is not the same for every row). — 11684, Jun 01 '14 at 08:27

score 7 · Accepted Answer · answered May 19 '14 at 12:42

So a little trick to do this is to change you statement to this:

INSERT INTO tbl (...) VALUES (...); SELECT LAST_INSERT_ID()

and then insert the row with ExecuteScalar which will return you the id; you can then update the object accordingly. So the entire snippet might look like this:

using (var conn = new MySqlConnection(connString))
using (var cmd = new MySqlCommand(sql, conn))
{
    var result = cmd.ExecuteScalar();
    int id;
    if (int.TryParse(result, out id))
    {
        // set the object's id here
    }
}

score 1 · Answer 2 · answered May 19 '14 at 12:42

1

Try looking up the last_insert_id() function in MySQL.

answered May 19 '14 at 12:42

DavidG

113,891
12
217
223

it always returns zero – Silver May 19 '14 at 12:48

Hassan · Answer 3 · 2015-07-28T04:59:09.607

@MichaelPerrenoud's answer is right. But in case you want to incorporate conditionals or use LAST_INSERT_ID() in another query you should look at following link:

How to Get the Unique ID for the Last Inserted Row.

Here is extract from reference:

For LAST_INSERT_ID(), the most recently generated ID is maintained in the server on a per-connection basis. It is not changed by another client. It is not even changed if you update another AUTO_INCREMENT column with a nonmagic value (that is, a value that is not NULL and not 0). Using LAST_INSERT_ID() and AUTO_INCREMENT columns simultaneously from multiple clients is perfectly valid. Each client will receive the last inserted ID for the last statement that client executed.

If you insert a record into a table that contains an AUTO_INCREMENT column, you can obtain the value stored into that column by calling the mysql_insert_id() function.

score 0 · Answer 4 · answered May 19 '14 at 12:41

0

try this:

select max(id) from table

answered May 19 '14 at 12:41

Ronak Shah

1,539
2
13
20

2

Probably a bad idea if other functions are also inserting rows into the table. – DavidG May 19 '14 at 12:42
Okay, so the problem with this solution is it only works if there is one user using the system. If two users insert rows adjacently somebody is going to get the wrong id. – Mike Perrenoud May 19 '14 at 12:44

kRiZ · Answer 5 · 2015-06-15T07:53:44.740

0

You could get the max id from the table: SELECT MAX(id) FROM param_reference

Then append the result to Res and update it.

edited Jun 15 '15 at 07:53

answered May 19 '14 at 12:49

kRiZ

2,320
4
28
39

how to get id of last inserted row

5 Answers5