VB.NET, Castle ActiveRecord and MySQL: Duplicate entry in a unique field

Question

Hi. When user inserts a duplicate entry in a unique field, Castle ActiveRecord throws the following exception:

Castle.ActiveRecord.Framework.ActiveRecordException was unhandled
  Message="Could not perform Save for SerieNotaFiscal"
  Source="Castle.ActiveRecord"
  StackTrace:
       at Castle.ActiveRecord.ActiveRecordBase.InternalSave(Object instance, Boolean flush)    at Castle.ActiveRecord.ActiveRecordBase.SaveAndFlush(Object instance)    at Castle.ActiveRecord.ActiveRecordMediator.SaveAndFlush(Object instance)    at bananaCo.ctrlSerieNotaFiscal.salvar(SerieNotaFiscal novoObjeto) in D:\bananaCohibernateerp\NHibernate\Control\Data\Comercial\ctrlSerieNotaFiscal.vb:line 10    at bananaCo.ctViewSerieNotaFiscal.SerieNotaFiscalSalvar() in D:\bananaCohibernateerp\NHibernate\Control\Visual\Comercial\ctViewSerieNotaFiscal.vb:line 63    at bananaCo.UISerieNotaFiscal.btnSalvar_Click(Object sender, EventArgs e) in D:\bananaCohibernateerp\NHibernate\View\Comercial\UISerieNotaFiscal.vb:line 19    at System.Windows.Forms.Control.OnClick(EventArgs e)    at DevExpress.XtraEditors.BaseButton.OnClick(EventArgs e)    at DevExpress.XtraEditors.BaseButton.OnMouseUp(MouseEventArgs e)    at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)    at System.Windows.Forms.Control.WndProc(Message& m)    at DevExpress.Utils.Controls.ControlBase.WndProc(Message& m)    at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)    at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)    at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)    at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)    at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)    at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)    at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)    at System.Windows.Forms.Application.Run(ApplicationContext context)    at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()    at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()    at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)    at bananaCo.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81    at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)    at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)    at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()    at System.Threading.ThreadHelper.ThreadStart_Context(Object state)    at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)    at System.Threading.ThreadHelper.ThreadStart()
  InnerException: NHibernate.Exceptions.GenericADOException
       Message="could not insert: [bananaCo.SerieNotaFiscal][SQL: INSERT INTO serienf (cod_serie, dat_ult_faturamento, cdn_ult_NftNumero, id_estabelecimento, id_especie) VALUES (?, ?, ?, ?, ?)]"
       Source="NHibernate"
       SqlString="INSERT INTO serienf (cod_serie, dat_ult_faturamento, cdn_ult_NftNumero, id_estabelecimento, id_especie) VALUES (?, ?, ?, ?, ?)"
       StackTrace:
            at NHibernate.Id.Insert.AbstractSelectingDelegate.PerformInsert(SqlCommandInfo insertSQL, ISessionImplementor session, IBinder binder)    at NHibernate.Persister.Entity.AbstractEntityPersister.Insert(Object[] fields, Boolean[] notNull, SqlCommandInfo sql, Object obj, ISessionImplementor session)    at NHibernate.Persister.Entity.AbstractEntityPersister.Insert(Object[] fields, Object obj, ISessionImplementor session)    at NHibernate.Action.EntityIdentityInsertAction.Execute()    at NHibernate.Engine.ActionQueue.Execute(IExecutable executable)    at NHibernate.Event.Default.AbstractSaveEventListener.PerformSaveOrReplicate(Object entity, EntityKey key, IEntityPersister persister, Boolean useIdentityColumn, Object anything, IEventSource source, Boolean requiresImmediateIdAccess)    at NHibernate.Event.Default.AbstractSaveEventListener.PerformSave(Object entity, Object id, IEntityPersister persister, Boolean useIdentityColumn, Object anything, IEventSource source, Boolean requiresImmediateIdAccess)    at NHibernate.Event.Default.AbstractSaveEventListener.SaveWithGeneratedId(Object entity, String entityName, Object anything, IEventSource source, Boolean requiresImmediateIdAccess)    at NHibernate.Event.Default.DefaultSaveOrUpdateEventListener.SaveWithGeneratedOrRequestedId(SaveOrUpdateEvent event)    at NHibernate.Event.Default.DefaultSaveOrUpdateEventListener.EntityIsTransient(SaveOrUpdateEvent event)    at NHibernate.Event.Default.DefaultSaveOrUpdateEventListener.PerformSaveOrUpdate(SaveOrUpdateEvent event)    at NHibernate.Event.Default.DefaultSaveOrUpdateEventListener.OnSaveOrUpdate(SaveOrUpdateEvent event)    at NHibernate.Impl.SessionImpl.FireSaveOrUpdate(SaveOrUpdateEvent event)    at NHibernate.Impl.SessionImpl.SaveOrUpdate(Object obj)    at Castle.ActiveRecord.ActiveRecordBase.InternalSave(Object instance, Boolean flush)
       InnerException: MySql.Data.MySqlClient.MySqlException
            ErrorCode=-2147467259
            Message="Duplicate entry '1' for key 'cod_serie'"
            Number=1062
            Source="MySql.Data"
            StackTrace:
                 at MySql.Data.MySqlClient.MySqlStream.ReadPacket()    at MySql.Data.MySqlClient.NativeDriver.ReadResult()    at MySql.Data.MySqlClient.ResultSet.NextResult()    at MySql.Data.MySqlClient.MySqlDataReader.NextResult()    at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)    at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader()    at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()    at NHibernate.AdoNet.AbstractBatcher.ExecuteNonQuery(IDbCommand cmd)    at NHibernate.Id.Insert.AbstractSelectingDelegate.PerformInsert(SqlCommandInfo insertSQL, ISessionImplementor session, IBinder binder)
            InnerException: 

My try/catch is something like that:

Try
some code goes here ...
Catch ex As Exception
    If ex.InnerException.ToString.ToLower.Contains("duplicate entry") Then
        MsgBox("There is an user with this username.")
        txtNome.Focus()
    Else
        MsgBox("Error! " & vbNewLine & ex.Message, MsgBoxStyle.Critical, "Erro")
    End If
End Try

This catch code is pretty ugly but i don't know how to handle this... Can you help me? Thank you guys!

Answer 1

You can either check the duplicate entry yourself (i.e. run a SELECT before inserting, you'd want to wrap this in a transaction) or use ISQLExceptionConverter to convert the database exception to a custom exception that your application-level code can consume.