Lee,

sorry for my bad English. I did not post another example, I meant the code above as an example. Sebastian is right of course, you don't need a transaction for a single INSERT statement, I forgot to pull that out. I just mixed the code above with some code of mine, that was Q&D.

When I want to write this a bit cleaner, I would do it like this:

BEGIN TRY
   BEGIN
      INSERT INTO ...
      SELECT SCOPE_IDENTITY()
   END
END TRY

BEGIN CATCH
   DECLARE @ErrMsg nvarchar(max), @ErrSeverity int, @ErrNumber int
   -- Assuming the field 'ModelNumber' has an unique index or is primary key
   SET @ErrNumber = ERROR_NUMBER()
   SET @ErrMsg = 
      CASE
         -- Unique index violation
         WHEN (@ErrNumber = 2601) THEN 'Model Number already in database - Error 2601'
         -- Primary key violation
         WHEN (@ErrNumber = 2627) THEN 'Model Number already in database - Error 2627'
         ELSE ERROR_MESSAGE() + ' - Error ' + LTRIM(RTRIM(CAST(@ErrNumber AS nvarchar(max))))
      END
   SET @ErrSeverity = ERROR_SEVERITY()
   RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH

Of course you have to handle the raised exception from your SQL statement inside you programming code somewhere, normally within constructs like this:

try {
   ...
} catch (Exception ex) {
   Exceptions.ProcessModuleLoadException(this, ex);
}

Best wishes