Get the identity value from an insert via a .net dataset update call

Question

Here is some sample code that inserts a record into a db table:

Dim ds As DataSet = New DataSet()
da.Fill(ds, "Shippers")

Dim RowDatos As DataRow
RowDatos = ds.Tables("Shippers").NewRow
RowDatos.Item("CompanyName") = "Serpost Peru"
RowDatos.Item("Phone") = "(511) 555-5555"
ds.Tables("Shippers").Rows.Add(RowDatos)
Dim custCB As SqlCommandBuilder = New SqlCommandBuilder(da)
da.Update(ds, "Shippers")

It inserts a row in the Shippers Table, the ShippersID is a Indentity value. My question is how can i retrieve the Identity value generated when the new row is inserted in the Shippers table.

I have done several web searches and the sources I've seen on the net don't answer it speccifically or go on to talk about stored procedures. Any help would be appreciated. Thanks!

Answer 1

You can use IDENT_CURRENT().

SELECT IDENT_CURRENT("Shippers") AS CurrentIdentityShippers;

Answer 2

Instead of using SqlCommandBuilder, you could call a stored procedure to do the insert. In the stored procedure, you could return the IDENTITY value as an OUTPUT parameter.

See http://msdn.microsoft.com/en-us/library/ks9f57t0(VS.71).aspx for an example.