Use of SQL OUTPUT parameter with INSERT to get Identity
29-Jul-1010 Leave a comment
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)
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
ExecuteNonQuery(command, InsertToolSQL)
cTool.ID = CType(command.Parameters(NewIDParameterName).Value, Integer)
Catch ex As Exception
Throw ex
End Try