How to error handle OLEDBB connection to Excel if SQL statement is bad?

Question

So I have a function that I created that will allow the user to gather data from an excel file using a SQL statement. Below is that function:

Public Function query_excel_file(ByVal filepath As String, ByVal sql_statement As String) As DataTable
    On Error Resume Next
    Dim m_sConn1 As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
           "Data Source=" & filepath & ";" & _
           "Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"


    Dim conn1 As New System.Data.OleDb.OleDbConnection(m_sConn1)
    Dim da As New System.Data.OleDb.OleDbDataAdapter(sql_statement, conn1)
    Dim dt As DataTable = New DataTable
    If da Is Nothing Then
        Return Nothing
        Exit Function
    End If

    da.Fill(dt)

    conn1.Close()

    Return dt

End Function

Now this function works perfectly fine if the sql_statement is a valid sql statement, however if it is not then I get an error like this:

I fully realize that this error comes from a bad sql statement. My question is not how to fix the sql statement but for a way to error handle this so that an error dialog doesn't pop up or crash the program.

What I have tried

I have tried adding the clause shown above:

    If da Is Nothing Then
        Return Nothing
        Exit Function
    End If

but the errors still appear. I have tried looking at the properties of my variable da which is a System.Data.OleDb.OleDbDataAdapter to see if there is any property to reference on an error but have had no luck as well.

I have also tried using a simple Try Catch End Try, and On Error Resume Next but the dialog still appears.

The Try Catch is the way to go, what is your attempt with the Try Catch error handler? — Steve, Jul 14 '16 at 18:45
I tried encapsulating the entire function from start to finish as well as just the highlighted part where the error is happening but the error still appears even with that there — Eric F, Jul 14 '16 at 18:56
Uncheck the "Break when this exception type is thrown box". That should allow your try-catch to process the error. — TnTinMn, Jul 14 '16 at 19:01
OK, but if you run the app outside the VS environment (run the compiled exe) do you still get the exception? Note that you have that flag set: _break when this exception type is thrown_ means that whatever you have around the exception the VS environment stops. More info here http://stackoverflow.com/questions/10506695/how-to-turn-off-break-when-exception-is-thrown-for-custom-exception-types — Steve, Jul 14 '16 at 19:01
I am aware that I can uncheck to allow the exception but I was hoping for another way other than that since I don't like to have exceptions. The errors are there for a reason and I prefer to handle them rather than ignore them — Eric F, Jul 14 '16 at 19:06
`On Error Resume Next` means that the code continue as though nothing happens. Add `Err_Handler:` to your function and `On Error GoTo Err_Handler`. — Alex Kudryashev, Jul 14 '16 at 19:28
Using `Try Catch` will work. Do *not* use `On Error Resume Next`. The dialog comes from Visual Studio, not your program. It's just to notify you that an exception occurred, and it pops up before your `catch` code executes. You can hit continue and your `catch` block will execute. Just uncheck the box if you don't want to see the dialog again. — Blorgbeard, Jul 14 '16 at 20:15
Notice the text of the dialog: "If there is a handler for this exception, the program may be safely continued" - a `catch` block is a handler. — Blorgbeard, Jul 14 '16 at 20:16

score 2 · Answer 1 · answered Jul 14 '16 at 20:22

That is the "first chance" exception dialog, and it is generated by Visual Studio when an exception occurs, before any other error handling gets to happen.

It's completely separate from your program's error-handling, and it won't happen when running your program outside Visual Studio (since it's generated by Visual Studio).

You can turn off the dialog by unchecking the box. This does not ignore the error, it just stops Visual Studio from pausing when it occurs.

Then, implement proper exception handling, which in this day and age is Try .. Catch (stay far away from On Error Resume Next).

If you put a message box in your Catch block and run your code, you will see that the error is caught properly and not just ignored.

How to error handle OLEDBB connection to Excel if SQL statement is bad?

1 Answers1