Recuperar ID de un registro que recién se insertó

Ejemplo para recuerar un id de una registro que recién se insertó en la BD. Normalmente se requiere ese id para insertar un segundo registro, muy usado en transacciones de SQL.


Private Sub ExecuteSqlTransaction(ByVal connectionString As String)
Using connection As New SqlConnection(connectionString)
connection.Open()
Dim intRegionID As Integer
Dim command As SqlCommand = connection.CreateCommand()
Dim transaction As SqlTransaction

' Start a local transaction
transaction = connection.BeginTransaction("SampleTransaction")

' Must assign both transaction object and connection
' to Command object for a pending local transaction.
command.Connection = connection
command.Transaction = transaction

Try
command.CommandText = _
"Insert into Region (RegionName, RegionDescription) VALUES (100, 'Description');SELECT @@Identity"
'Get the Region ID in the intRegionID variable
'Very important to use ExecuteScalar in order to get the
'numeric value of the ID
intRegionID = command.ExecuteScalar()

'Now we can use that id to our next SQL statement
command.CommandText = _
"Insert into RegionGroup (RegionID, RegionName) VALUES (" & RegionID & ", 'Description')"

command.ExecuteNonQuery()

' Attempt to commit the transaction.
transaction.Commit()
Console.WriteLine("Both records are written to database.")

Catch ex As Exception
Console.WriteLine("Commit Exception Type: {0}", ex.GetType())
Console.WriteLine(" Message: {0}", ex.Message)

' Attempt to roll back the transaction.
Try
transaction.Rollback()

Catch ex2 As Exception
' This catch block will handle any errors that may have occurred
' on the server that would cause the rollback to fail, such as
' a closed connection.
Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType())
Console.WriteLine(" Message: {0}", ex2.Message)
End Try
End Try
End Using
End Sub