Use of SQL OUTPUT parameter with INSERT to get Identity

 

 

        Private Const InsertToolSQL As String = "INSERT INTO [Production].[Tool] ( Bin, DateLastStockCheck, DieIntroDate, DieLastUpdate, ID, NoOfTools, Notes, QtyInAvailable, QtyInRepair, QtyOut, SetNo, Tool, ToolStoresLastUpdateNowFromToolLog, ToolSupplierID, ToolTypeID) VALUES (@Bin, @DateLastStockCheck, @DieIntroDate, @DieLastUpdate, @ID, @NoOfTools, @Notes, @QtyInAvailable, @QtyInRepair, @QtyOut, @SetNo, @Tool, @ToolStoresLastUpdateNowFromToolLog, @ToolSupplierID, @ToolTypeID); SELECT @NewID = IDENT_CURRENT(‘Production.Tool’)"

 

        Private Const NewIDParameterName As String = "@NewID"

…

 

            Dim command As New SqlCommand
            SetCommonParameters(cTool, command)
            command.Parameters.Add(NewIDParameterName, SqlDbType.Int)

            command.Parameters(NewIDParameterName).Direction = ParameterDirection.Output

 

            Try
                ExecuteNonQuery(command, InsertToolSQL)
                cTool.ID = CType(command.Parameters(NewIDParameterName).Value, Integer)
            Catch ex As Exception
                Throw ex
            End Try