CODEWITHSUNDEEP
sqlvb.netinsert
James
James

Reputation: 187

How to continue insert if one row fails

I have a while loop where it fetches record from csv and inserts to sql table. Now csv may contain many rows.

What I want is if one row fails just log to a file and continue with next record. I was thinking of try and catch but that will exit the program. So, any suggestions?

while (csv.readnextline)
'assign csv columns to objects
try
'insert to db
Catch ex As Exception
'write to log file
End Try

I need the above code to continue after catching an exception.

Thanks

Upvotes: 0

Views: 1769

Answers (2)

GameAlchemist
GameAlchemist

Reputation: 19294

no it won't exit the program, depending on how/where you handle the exception. If you do something like :

Dim WrongValuedLinesList As New List(Of String)
Dim ConversionFailedList As New List(Of String)
Dim InsertionFailedList As New List(Of String)
Dim NumberOfInsertedLines As integer = 0

For Each  (CurrentLine in my csv)
  ' 1. line processing
  Try
    ' (process my line : split, convert, check range...)
    If (I know the insertion will fail) Then
      ' (Store information about that wrong line, in List, log, or do nothing)
        WrongValuedLinesList.Add(" This line : " & CurrentLine 
                                              & " has wrong values because... 
        Continue For
    End If
  Catch ex as exception
    ' (here handle the line conversion failed : store in list, or log, or do nothing ...)
    ' for expl :   
       ConversionFailedList.Add(" Conversion failed for line " & CurrentLine 
                                      & " exception details : " & ex.message " )
  End Try
  ' 2. Line insertion
  Try
    '(insert my processed data into database)
     NumberOfInsertedLines +=1
  Catch ex as exception
    ' (here handle the insertion failed exception (expl : primary key might not be unique)
    '                      : store in list, log, do nothing...)
    ' for example :
    InsertionFailedList.Add(" Insertion failed for line " & CurrentLine 
                                      & " exception details : " & ex.message " )
  End Try
 Next

 (Here you might wanna report how things went to your user using
    your error list.)

Upvotes: 0

SWeko
SWeko

Reputation: 30902

Try and catch do not exit the program, they just control the flow of the code in case something exceptional happens.

When an exception happens in the try block, the execution continues on the first line of the (corresponding) catch block. After the execution of the catch block, the code continues on the first line after the catch, which in your case could be the End While which will continue the loop.
So an construction like this

While dr.Read
  Try
    InsertRowIntoDataBase()
  Catch ex As Exception
    LogErrorToFile(ex)
  End Try
End While

should work for you.
However, this is a bad design, as it will generate and log an exception, no matter what the problem is, whether the data is invalid, or the sql server is down, or even if there is an error in your code (e.g. some lurking NullReferenceException). You should limit the handling of exception to a specific case, e.g. to a problem with the database, like this:

While dr.Read
  Try
    InsertRowIntoDataBase()
  Catch ex As SqlClient.SqlException
    LogDataBaseErrorToFile(ex)
  End Try
End While

Also, if there are known possible problems with the data (e.g. a string in the csv where an integer is expected) it's better to just check that than to use an exception mechanism, something along these lines:

While dr.Read
  Try
    If Not IsRowValid() Then
      LogInvalidDataToFile()
      Continue While
    End If
    InsertRowIntoDataBase()
  Catch ex As SqlClient.SqlException
    LogDataBaseErrorToFile()
  Catch ex As Exception
    LogGenericErrorToFile()
  End Try
End While

Upvotes: 3

Related Questions