Error when trying to read data from Excel in VB.Net

Question

I've tried many different routes in being able to build a page that allows the user to choose an excel file and then reads data from that file. So far all I've gotten is errors.

My latest error is: "Cannot update. Database or object is read-only."

Here is my code:

Protected Sub Upload_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Upload.Click
    If (testFile.HasFile) Then
        Dim ds As DataSet
        Dim strFileType As String = System.IO.Path.GetExtension(testFile.FileName).ToString().ToLower()

        Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
        Dim MyConnection As System.Data.OleDb.OleDbConnection
        MyConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & testFile.FileName & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=2")

        ' Select the data from Sheet1 ([in-house$]) of the workbook.
        MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection)

        ds = New System.Data.DataSet
        MyCommand.Fill(ds) - __This is where the error points.__
        grvExcelData.DataSource = ds.Tables(0)

    End If
End Sub

Any ideas on why this is being thrown? I'm just trying to output the data to a gridview for now. Later I will need to loop through each cell, but I'm trying one step at a time.

Also, if there's a better way to do this I am completely open to it!

Thanks!

Answer 1

But strFileType is the extension of the file that you wish to open. (I.E. for filename.xls it is just the .xls part)
Probably you want the full file name.

    MyConnection = New System.Data.OleDb.OleDbConnection( _
    "provider=Microsoft.Jet.OLEDB.4.0; " & _
    "data source=" & testFile.FileName & "; " & _
    "Extended Properties=Excel 8.0")

Now, for the 'better part':
You don't close the connection, and this should never happen. A simple Using block will save you

    Using MyConnection = New OleDbConnection( _
        "provider=Microsoft.Jet.OLEDB.4.0; " & _
        "data source=" & strFileType & "; " & _
        "Extended Properties=Excel 8.0")

        ' Select the data from Sheet1 ([in-house$]) of the workbook.
        Using MyCommand = New OleDbDataAdapter("select * from [Sheet1$]", MyConnection)
            Dim ds = New System.Data.DataSet
            MyCommand.Fill(ds) 
        End Using
    End Using

And I suggest to add the Import directive to avoid the lenghty namespace prefix for every single OleDb variable