Return a value after insert the query

Hi!

create table testReturn
(
id int identity(100,1),
name varchar(10)
)

How can I return the value of identity column after inserting the value.

Dim objConn As SqlConnection
Dim SQLCmd As SqlClient.SqlCommand
Dim ds As New DataSet
Dim strsql As String

Try

objConn = New SqlConnection
objConn.ConnectionString = _
"Network Library=DBMSSOCN;" & _
"Data Source=localhost;" & _
"Initial Catalog=mydb;" & _
"User ID=userid;" & _
"Password=pass"

objConn.Open()

strsql = "insert into testReturn values ('a')"
SQLCmd = New SqlClient.SqlCommand(strsql, objConn)
Dim rowsAffected As Integer = 0
rowsAffected = SQLCmd.ExecuteNonQuery

Dim rv As String
rv = SQLCmd.Parameters(0).Value.ToString()
Response.Write(rv)

Catch ex As Exception
Response.Write(ex.ToString)
End Try

SeeHow do I get the IDENTITY / AUTONUMBER value for the row I inserted?

|||

strsql = "insert into testReturn values ('a'); select @.@.Identity"
SQLCmd = New SqlClient.SqlCommand(strsql, objConn)

dim Identiy as Object

Identity = SQLCmd.ExecuteScalar

|||

You need to put "Select @.@.identity" statement immediately after your insert query so it would return the ID value of the record inserted, using the preceding Insert statement.

Cheers
Ritesh

|||

Using @.@.IDENTITY is an vulnerable approach, since it returns the last inserted id of any table. If you are using SQL 2005 then you can use the OUTPUT clause.

SeeHow to get an Identity value with SQL Server 2005

|||

You can either use @.@.identity or scope_identity().

@.@.identity gives you the last generated identity value.

scope_identity() gives you the last generated identity value for current scope.

Just have a quick look at BOL for further understanding.

Hope this will help.