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
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.


3 comments:

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..