CODEWITHSUNDEEP
sqlvb.netvisual-studio
Eric F
Eric F

Reputation: 948

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

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:

enter image description here

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.

Upvotes: 0

Views: 263

Answers (1)

Blorgbeard
Blorgbeard

Reputation: 103467

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.

Upvotes: 2

Related Questions