Getting your last saved record in database

Question

Hi guys. When I insert a new record to my database, I need to grab the ID (identity/auto increment field) of that record RIGHT AWAY. What I have done in the past, is to run a query SELECT MAX(ID) FROM right after I inserted the new record, but this can't be a fool-proof way of doing it. What if someone else inserts a new record before I run my SELECT MAX query? So, my question is: does anyone know of a better way? Is there any built-in functionality for this in ADO.NET? Thanks!

Administrator · Answer

Alternatively: without a stored procedure use the code below straight after the insert. SqlCommand SCommand = new SqlCommand("SELECT IDENT_CURRENT('Table') AS idNum", Conn); // execute DataReader for specified command SqlDataReader ADMReader = SCommand.ExecuteReader(CommandBehavior.CloseConnection); // while there are rows to read while (ADMReader.Read()) { sMbId = ADMReader["idNum"].ToString(); } Conn.Close(); I hope this helps.

Administrator · Answer

Get identity value before You insert the record. This solution has one disadvantage: after cancelling insert there's a whole in numbering. It works in any database.

Administrator · Answer

Ahhh, good idea! Thanks alot!! Have a good one.

Administrator · Answer

Not sure about ADO.Net, but you can achieve the same thing yourself if you are using SQL server. Put your insert into a stored proc with an out parameter and assign @@Identity to this out parameter after the insert.

Getting your last saved record in database

Answers (4)