Trying to read this csv file in vb, then response.write the data in the csv out

Question

I'm getting Server Error in '/' Application.

Invalid argument.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: Invalid argument.

when I try to use MyCommand.Fill to read the DataSet

Here's my code

Dim sheets as new List(Of String)(New String(){"po"})

Dim MyConnection As System.Data.OleDb.OleDbConnection
Dim DtSet As System.Data.DataSet
Dim MyCommand As System.Data.OleDb.OleDbDataAdapter

MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft Office 12.0 Access Database Engine OLE DB Provider;Data Source=" & filepath & ";Extended Properties=Excel 8.0;")   

for p as integer = 0 to sheets.count - 1
    dim dt as DataTable
    MyCommand = New System.Data.OleDb.OleDbDataAdapter("Select * from ["& sheets(p) & "$]", MyConnection)
    DtSet = New System.Data.DataSet
    MyCommand.Fill(DtSet)
    dt = DtSet.Tables(0)

    if p > 0
        response.write(sheets(p))
    end if

next
MyConnection.Close()

score 0 · Answer 1 · edited May 23 '17 at 12:27

I have a few concerns:

Is there a reason you are using Microsoft Office 12.0 Access Database Engine OLE DB Provider? How about using Microsoft.Jet.OLEDB.4.0?
Are you sure your DB connection (i.e. MyConnection) is open?
You also dont want your users getting unhandled errors. Look into using Try/Catch in this example.

First try to open the connection by calling MyConnection.Open()

Dim sheets as new List(Of String)(New String(){"po"})

Dim MyConnection As System.Data.OleDb.OleDbConnection
Dim DtSet As System.Data.DataSet
Dim MyCommand As System.Data.OleDb.OleDbDataAdapter

MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft Office 12.0 Access Database Engine OLE DB Provider;Data Source=" & filepath & ";Extended Properties=Excel 8.0;")  

MyConnection.Open() '<-------------------- open the connection here

for p as integer = 0 to sheets.count - 1
    dim dt as DataTable
    MyCommand = New System.Data.OleDb.OleDbDataAdapter("Select * from ["& sheets(p) & "$]", MyConnection)
    DtSet = New System.Data.DataSet
    MyCommand.Fill(DtSet)
    dt = DtSet.Tables(0)

    if p > 0
        response.write(sheets(p))
    end if

next
MyConnection.Close()

For your first point, I was told to go by my a reference and that was it. I was confused on why its office 12.0 but in his reference code thats what he's using. I'm going to have to try that monday since remoting in is weird rigtht now. — Gamerznation Charlotte, Oct 25 '14 at 03:02
I tried, and I get a End of Statement expected after adding the MyConnection.Open() — Gamerznation Charlotte, Oct 25 '14 at 03:14

Trying to read this csv file in vb, then response.write the data in the csv out

1 Answers1