Importing Excel data

[USMC2542]

I am currently finishing up an application that uses grids to allow users to view, modify and add data to Oracle tables. I am using VB.NET 2003. I was told by the users that they want to do bulk loads into the grid. I am trying to import the excel data into the a dataset and then merging it with the original and then refreshing the grid.

I have been having a tough time getting the Excel data into the system. Here is the code I have been using or I should say, TRYING!!!!

strSQL = "Select * from [Sheet1$]"
Dim ds As New DataSet("NewExcelData")
Dim dt As New DataTable("NewExcelData")
Dim ConnectionString As String
ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=c:\myExcel.xls;" & "Extended Properties=""Excel 11.0;HDR=NO"""
Dim Conn As New OleDb.OleDbConnection(ConnectionString)
Dim exCmd As OleDb.OleDbCommand = New OleDb.OleDbCommand(strSQL, Conn)
Dim da As New OleDb.OleDbDataAdapter(exCmd)

Try
exCmd.Transaction = myTrans
exCmd.CommandTimeout = 30
da.SelectCommand = exCmd
ds.Clear()
ds = New DataSet("NewExcelData")
Catch ex As Exception
style = MsgBoxStyle.OKOnly Or MsgBoxStyle.Critical
title = "xxxxxx"
response = MsgBox(msg, style, title)
Error_IND = True
End Try

da.Fill(ds, "NewExcelData")


I works up to the part where I try to fill the dataset. I get the following message:

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll

Driving me nuts!!!!!! Any help would be appreciated. This is the only thing on this project I have left and I can turn it over to the test person.

Thanks

 

[beanman101283]

Instead of connecting to the spreadsheet like a database, can't you instead read directly from the sheet, loop through the cells, and fill your dataset that way?