How do I rollback a failed InsertOnSubmit? (VB.NET LINQ)

Question

I'm having a problem with undoing a failed InsertOnSubmit when SubmitChanges fails. Here's the code:

    Dim NewFac As New t_Facility With
            {.FK_Instance_ID = guInstance_ID,
             .FK_AccountType_ID = guAccountType_ID,
             .FK_ATP_ID = guATP_ID,
             .FK_Repayment_ID = guRepaymentType_ID,
             .FK_InterestType_ID = guInterestType_ID,
             .FK_FT_ID = guFacilitiesType_ID,
             .NewRecord = bNewRecord,
             .IndexNum = iIndexNum,
             .SortCode = sSortCode,
             .AccountNumber = sAccountNumber,
             .Balance = decBalance,
             .LastSanction = decLastSanctioned,
             .Proposed = decProposed,
             .Term_MTHs = iTerm_MTHS,
             .Term_Expiry = dTerm_Expiry,
             .InterestRate = decInterestRate,
             .ArrangementFee = decArrangementFee,
             .DateTime_From = Now(),
             .ID = guFacilities_ID}
    db.t_Facilities.InsertOnSubmit(NewFac)
    Try
        db.SubmitChanges()
    Catch e As Exception
        Console.WriteLine(e)
        MessageBox.Show(e.Message & ". Please correct the field and try again", "ERROR", MessageBoxButton.OK, MessageBoxImage.Stop)

        Exit Sub 'Takes the user back to the form to correct the value
    End Try

When they hit submit again, it comes back around and fails at the same point with the same values as the original submission ignoring the new values that the user input.

The values in "NewFac" are the corrected new values. I've checked them on this line manually in debug: "db.t_Facilities.InsertOnSubmit(NewFac)"

I assume i need to somehow remove the failed "NewFac" that contains the incorrect values from "db.t_Facilities.InsertOnSubmit(NewFac)" in the catch somehow, but i don't see a way to do this?

FYI: I got the principal of this approach from here: https://msdn.microsoft.com/en-us/library/bb763516

Any help would be appreciated.

Answer 1

Now this is probably not going to be technically correct, so someone informed; please feel to give the real reason this worked (So its not really based on any statement of fact other than it worked, and is entirely my opinion - but it worked):

The solution dawned on me while reading through several questions from various other people having similar issues (all using various ways of Programmatically looping through the DataContext looking for a match), that the changes are just added the DataContext which appears to effectively act like an offline CLR of the database, so if InsertOnSubmit added to it, it stands to reason DeleteOnSubmit should remove it right?

So with that in mind, i tried this:

db.t_Facilities.InsertOnSubmit(NewFac)
Try
    db.SubmitChanges()
Catch e As Exception
    Console.WriteLine(e)
    MessageBox.Show(e.Message & ". Please correct the field and try again", "ERROR", MessageBoxButton.OK, MessageBoxImage.Stop)

    db.t_Facilities.DeleteOnSubmit(NewFac)

    Exit Sub 'Takes the user back to the form to correct the value
End Try

It worked! :D

So i may be totally wrong as to why it worked (please inform me why - i'd genuinely like to know), but it worked.

EDIT: If someone can give the correct reason it worked, i'll accept their answer instead of my own