Home

tSQL Return Identity When Adding New Record

Posted by SteveHardie | On: Mar 21 2011

@@Identity (Transact-SQL) Is a system function that returns the last-inserted identity value.

To use this in a tSQL statement, you need to declare an output variable and set the value after inserting a new row into a table.

   SET @RecordID=@@IDENTITY

Using VB.NET

Using MyCommand As IDbCommand = DB.CreateCommand
                
                MyCommand.CommandText = "INSERT INTO [table](field) VALUES(@fieldValue) " _
                        & " SET @RecordID=@@IDENTITY"
                
                Dim fieldValue As IDataParameter = MyCommand.CreateParameter
                fieldValue.ParameterName = "@fieldValue"
                fieldValue.Value = "Value"
                MyCommand.Parameters.Add(fieldValue)
                
                Dim RecordID As IDataParameter = MyCommand.CreateParameter
                RecordID.ParameterName = "@RecordID"
                RecordID.Direction = ParameterDirection.Output
                MyCommand.Parameters.Add(RecordID)

                MyCommand.ExecuteNonQuery()

End Using

Using xPress

MyCommand.Parameters.Add(xPress.Data.BuildParameter(MyCommand, "@RecordID", Nothing, ParameterDirection.Output))

Leave a comment