Link to home
Start Free TrialLog in
Avatar of Bruce
BruceFlag for United States of America

asked on

Recover from SQL Exception in Try Catch block

How do I continue execution after a primary key violation exception is returned?

I am executing multiple inserts and if an exception is returned I want to continue with the next insert.

Sub DoMyInsert()
  'Setup Local Variables
  Try

    For Each oRow as DataRow In datTable.Rows

      'If a primary key exception occurs, how do I jump back into this loop?

      'Get data
      'Insert into another table

    Next

  Catch ex As SqlException
    'Do I do something here?
  Catch ex As Exception
    Throw ex
  Finally
    m.Dispose()
  End Try
End Sub

Do I need to user a couter loop and hold the index and return?

Do I need to user and "On Error...Resume Next" block?

TIA,
Bruce

Avatar of mygurus
mygurus

I suggest that you should use transaction while insert data.
The transaction have rollback feature, make sure data can rollback when any error (or exception) occurred.
Avatar of Bruce

ASKER

I can to this but I still need to move onto the next set of data if there is a primary key violation.

Is On Error...Resume Next my only option?
ASKER CERTIFIED SOLUTION
Avatar of mygurus
mygurus

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial