Loading an Excel file into a DataSet

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello eveyone,

I have the below code which is trying to load an Excel file into a DataSet,
however it errors at the oAdapter.Fill(ds); line with An unhandled exception
of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll. Can
anyone spot the a reason why. or help with things that I could look at?

Thamks all,

OleDbConnection objConn=new OleDbConnection(sConnectionString);
objConn.Open();
OleDbCommand oCmd=new OleDbCommand("SELECT * FROM [ZREPSLET$]", objConn);
OleDbDataAdapter oAdapter=new OleDbDataAdapter();
oAdapter.SelectCommand = oCmd;

DataSet ds=new DataSet();
oAdapter.Fill(ds);
 
Użytkownik "Jon said:
Hello eveyone,

I have the below code which is trying to load an Excel file into a
DataSet,
however it errors at the oAdapter.Fill(ds); (...)
Click to expand...
oAdapter.SelectCommand = oCmd;

DataSet ds=new DataSet();
oAdapter.Fill(ds);
Click to expand...

Check details of the exception, for example add try and catch:

try
{
oAdapter.Fill(ds);
}
catch(OleDbException ex)
{
MessageBox.Show(ex.Message);
}

Regards,
Grzegorz
 
Hi Jon,

First of all, what does sconnectionstring look like?

You might want to try something like this, or modify your connection string
to replicate it:
Dim oconnex As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=c:\my documents\test_.xls;" & _

"Extended Properties=""Excel 8.0;HDR=Yes""")

Try

oconnex.Open()

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try

Dim ocmd As New OleDbCommand("select * from [sheet1$]", oconnex)

Dim oda As New OleDbDataAdapter(ocmd)

Dim ods As New DataSet("test file")

oda.Fill(ods, "test file")

Dim irow As DataRow

For Each irow In ods.Tables(0).Rows

If Not IsDBNull(irow(0)) Then

MessageBox.Show(irow(0))

Else

MessageBox.Show("null value")

End If

If Not IsDBNull(irow(1)) Then

MessageBox.Show(irow(1))

Else

MessageBox.Show("null value")

End If

Next

oconnex.Close()

HTH,

Bernie Yaeger
 
Hi,

Like for me code looks fine unless your code did not instantiate DS
variable. Did you do this using New?
 
All,

Thanks for your replies. I now have it working. The excel file had 2 empty
columns in, I remove these and it works great.

Thanks again,

Jon
 
Back
Top