VB .NET : Get Identity field value from SQL INSERT

Identity field or auto number is the most common used field on a database because it is the easiest to maintain. More often than not, we need to know the generated id of the new record. Today I will give you an easy way to do it in .NET

con.ConnectionString = strConnStr 'connection string
con.Open()

cmd.CommandType = CommandType.Text
cmd.Connection = con
cmd.CommandText = "INSERT INTO tbl (Field1, Field2) VALUES(Value1,Value2) SELECT SCOPE_IDENTITY();"
Dim identity As Integer = -1
identity = Integer.Parse(cmd.ExecuteScalar().ToString()) 'Get the @Identity Column

Simply add SELECT SCOPE_IDENTITY(); at the end of your INSERT SQL command then use ExecuteScalar() instead of ExecuteNonQuery() to return the value of Identity column. You can also call SCOPE_IDENTITY() on a stored procedure (I will discuss this in detail on my next post) and it will produce the same result.