VB.net error "OleDbException was unhandled" When connected to Databse.

Hello,

I'm trying to write a vb.net application and I'm getting an annoying "OleDbException was unhandled" error when my application connects to my database. The database path is fine and all the data is there. this was working a few days ago but I'm not sure why it stopped.  Please see my code below.. Any help would be greatly appreciated.

Imports System.Data.OleDb

Public Class Form1

    Private Function CreateConnString(ByVal Str As String) As String
                Return "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Str + ";Extended Properties=""Excel 8.0;HDR=YES;"""

    End Function

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim Conn As New OleDbConnection
        Dim Comm As New OleDbCommand
        Dim DReader As OleDbDataReader
        Dim Str As String = ""
        Dim Ename = EmpName.Text
        Conn.ConnectionString = CreateConnString("c:\Data\TimeData.xlsx")
        Conn.Open()
        Comm.Connection = Conn
        Comm.CommandText = "SELECT * FROM [Einfo$] where Ename= """ & Ename & """"
        DReader = Comm.ExecuteReader(CommandBehavior.CloseConnection)

        Try
            While (DReader.Read)
                TextBox4.Text = DReader.Item(1).ToString
                TextBox5.Text = DReader.Item(2).ToString
                TextBox6.Text = DReader.Item(3).ToString
                MsgBox(DReader.Item(4).ToString)
                TextBox3.Text = EmpName.Text
            End While
        Finally
            DReader.Close()
        End Try
    End Sub

End Class

Open in new window

LVL 1
AnthonySmithMCPAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jacques Bourgeois (James Burger)PresidentCommented:
First of all, you should have a Try around the call that opens the connection and calls ExecuteReader. This is where and OleDBException happens the most often.

Also, a Try should have a Catch in order to handle any possible Exception:

Do the following, you should get a more precise message that will tell you more.

Try
        Conn.Open()
        Comm.Connection = Conn
        Comm.CommandText = "SELECT * FROM [Einfo$] where Ename= """ & Ename & """"
        DReader = Comm.ExecuteReader(CommandBehavior.CloseConnection)
Catch ex As OleDBException
         MessageBox.Show(ex.Message)
End Try

Open in new window


You might also want to check If DReader.HasRows before going into the loop, just in case your query does not return anything, something that could easily happen if the user types nothing in EmpName.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AnthonySmithMCPAuthor Commented:
@JamesBurger,

The message i got is "External table is not in the excpected format" What does this mean? My execl database is in the right format...
0
AnthonySmithMCPAuthor Commented:
@JamesBurger,

With that message i was able to google the error. I got it working... I got the answer from here.

http://stackoverflow.com/questions/1139390/excel-external-table-is-not-in-the-expected-format

Thanks for your help!!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.