What's the identity of the row I just inserted?

[sevenhalo]

I was stumbling around the net and ran into this article. With a couple slight alterations, it can be used with ADO.Net:

The example uses Orders in the Northwind database (you have to change OrderID to an identity field in order for it to work).

Dim strConn As String = "server=server;database=Northwind;"
        Dim strSQL As String = "SET NOCOUNT ON; INSERT INTO Orders(CustomerID) VALUES ('123456'); SELECT IdentityInsert=@@IDENTITY; SET NOCOUNT OFF"
        Dim sqlConn As New SqlClient.SqlConnection(strConn)
        Dim sqlCmd As New SqlClient.SqlCommand(strSQL, sqlConn)
 
        Dim intIdentity As Integer
        sqlConn.Open()
        intIdentity = Convert.ToInt32(sqlCmd.ExecuteScalar)
        sqlConn.Close()
 
        MessageBox.Show("New records identity: " & intIdentity)