get last inserted id

[jlivvers]

Hi i'm using a table adapter.insert() method to add new data to my table and i need to get hold of the id of the inserted row. I've read around and seen that the most popular way to do this is with SELECT @@IDENTITY as Value but im not sure where to use this?

my iinsert looks like:

instalTblAdapter.Insert(Convert.ToInt32(numPaysTxt.Text), Convert.ToInt32(numPaysTxt.Text));

can anyone point me in the right direction?

thanks

[Mitja Bonca]

If I understand you correctly, you would like to insert new row into some table in dataBase. And you want the new id od this row, correct?
I hope you have numbers for id, then you can do a select query to get the latest id and increase it by 1 (to get the next number of the last one):

private int GetNewId()
        {
            int newID = 0;
            "SELECT MAX(IDColumnName) FROM MyTable";
            //create connection,
            //create command
            //use sqlDataReader to read the last inserted id
            newID = (int)reader[0];
            //increase it by 1 to get the new number (id):
            return newID++;
        }

[CrappyCoder]

Hmmm,

Not a good idea to get the Max(IDColumnName) as previously suggested it will only get the last inserted record (not the record you have added) - and in a high transactional database you will get totally incorrect results.

Much better to return the ID added within the scope of the insert procedure which is why its preferable to use SCOPE_IDENTITY and assumes you have added an auto-incrementing Identity column in your database (if not why not?)

You have several methods to retrieve the Identity value depending on your knowledge.

You could use a dataset to return a column value (example shown), or you could use RETURN SCOPE_IDENTITY at the end of your procedure or you could include an OUTPUT parameter in your argument declaration:

BEGIN

INSERT INTO <TABLE>....
(Field1, Field2...)
VALUES
(Value1, Value2...)

SELECT [InsertedRecordID] = SCOPE_IDENTITY

END

ALternatively you could use

Using conn As New SqlConnection(connString)
Dim cmd As New SqlCommand(sql, conn)
...
...
...

int InsertedRecordID = (int)cmd.ExecuteScalar();

:

You would then need to change your SQL code...

BEGIN

INSERT INTO <TABLE>....
(Field1, Field2...)
VALUES
(Value1, Value2...)

RETURN SCOPE_IDENTITY

END

But please do NOT use the SELECT MAX() method!!!!

[kvprajapati]

Take a look at MSDN article - Managing an @@IDENTITY Crisis