Why does SQL Server keep throwing exceptions?

Question

I have my project in .NET that uses a database in SQL Server. I'm using Linq-to-SQL, sometimes when the project throws me an exception (Constraint) in a part of the project this same error keeps showing in other part of the project when I do another thing with the database. Like when I do an insertion and I had before an exception on delete the insertion throws me the delete exception, and it remains this way until I close and open again the project. My major problem is when this happen in my online project, this error in my project causes me problems in the project I'm testing online (I use the same database). I don't know if this exception is on the memory or something but its have been causing me a lot of headaches.

This is the Exception thats throws me when i do a wrong delete (not doing the cascade delete)

var actividad = (from ta in modeloDatos.tActividad
                         where ta.id_actividad == idActividad
                         select ta).Single()
     modeloDatos.tActividad.DeleteOnSubmit(actividad);
        modeloDatos.SubmitChanges();
        return true;
    }
    catch (Exception ex)
    {
        return false;

    }

    ex = {"The DELETE statement conflicted with the REFERENCE constraint \"FK_tActividadRiesgo_tActividad\". The conflict occurred in database \"DBDESARROLLO\", table \"dbo.tActividadRiesgo\".\r\nThe statement has been terminated."}

and when it go in this code (insert)

  proceso.id_encabezado = encabezadoProceso.id_encabezado;
        proceso.id_procesopadre = idProcesoPadre;
        modeloDatos.tProceso.InsertOnSubmit(proceso);
        modeloDatos.SubmitChanges();
 catch (Exception ex)
    {
        return -1;

    }

throws me the exact same exception that have nothing to do with what im doing

ex = {"The DELETE statement conflicted with the REFERENCE constraint \"FK_tActividadRiesgo_tActividad\". The conflict occurred in database \"DBDESARROLLO\", table \"dbo.tActividadRiesgo\".\r\nThe statement has been terminated."}

i'm in fact using diferent tables.

Answer 1

It sort of sounds like you may be displaying the error using a session variable but not clearing it out for each new page.

Answer 2

I'm guessing that you're still using the same DataContext even after the exception, so it still has the same pending changes (updates/inserts/deletes) and when you try to submit changes again, those same pending changes will throw the same exception again.

If that's the case, there's a few different options for handling it, but the simplest is to just throw away the 'broken' datacontext (dispose of it to be nice) and create a new one. Since it's the exception path doing this, I'd rather do a simpler thing like this rather than something more complicated that might run faster :)