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.