Free Information Technology Magazines and eBooks

Tuesday, March 03, 2009

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

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.


Jacob Sebastian said...
This comment has been removed by the author.
Anonymous said...

Thank you. Exactly what I was looking for!

Excelsoft said...

Usually I create a GetMaxID query in the table adapter and it gives me that field..