read from Excel worksheet into dataset

[=?Utf-8?B?Y29uc3RhbnRpbg==?=]

I have problem with my code I need to read from Excel worksheet into dataset
the problem is in this line oleda.Fill(ds). The data adapter should fill
dataSet with data from Excel worksheet but isn't doing so. Below is my
code,thanks

Imports System.Data.Ole Db
Imports System.Data
Imports Oracle.DataAcce ss.Client

Public Class Form1

Private Sub Button1_Click(B yVal sender As System.Object, ByVal e As
System.EventArg s) Handles Button1.Click

Dim connString As String = "provider=Micro soft.Jet.OLEDB. 4.0;" & _
"Data Source=C:\emplo yeetable.xls;Ex tended Properties=Exce l 8.0;"
' Create the connection object

Dim oledbConn As OleDbConnection = New OleDbConnection (connString)

Try
' Open connection
oledbConn.Open( )

' Create OleDbCommand object and select data from worksheet Sheet1
Dim cmd As OleDbCommand = New OleDbCommand("S ELECT * FROM
[Sheet1$]", oledbConn)

' Create new OleDbDataAdapte r
Dim oleda As OleDbDataAdapte r = New OleDbDataAdapte r()

' Create a DataSet which will hold the data extracted from the
worksheet.
Dim ds As DataSet = New DataSet()

' Fill the DataSet from the data extracted from the worksheet.
oleda.Fill(ds)

Catch
Finally
' Close connection
oledbConn.Close ()
End Try

'1.Create connection object to Oracle database
Dim con As OracleConnectio n = New OracleConnectio n()
Try
'2.Specify connection string
con.ConnectionS tring = ("Data Source=dprod;Us er Id=mtr;
Password=lar777 7")

'3. Open the connection through ODP.NET
con.Open()
Catch ex As Exception
'4.display if any error occurs
MsgBox(ex.Messa ge, Microsoft.Visua lBasic.MsgBoxSt yle.Exclamation ,
"OraScan")

'3.Create command object to perform a query against the database:

Dim cmdQuery As String = "SELECT * FROM employeetable"

' Create the OracleCommand object to work with select
Dim cmd As OracleCommand = New OracleCommand(c mdQuery)
cmd.Connection = con
cmd.CommandType = CommandType.Tex t

' Dispose OracleCommand object
cmd.Dispose()
Finally

' Close and Dispose OracleConnectio n object
con.Close()
con.Dispose()

End Try
End Sub
End Class

[Rich P]

Re: read from Excel worksheet into dataset

Greetings,

Try it like this:

----------------------------------------------------
Imports System
Imports System.Data.Ole Db

Public Class frmExcelStuff

Dim conOle As OleDbConnection
Dim daOle As OleDbDataAdapte r, ds As DataSet

Private Sub frmExcelStuff2_ Load(...) Handles MyBase.Load

conOle = New OleDbConnection
conOle.Connecti onString
= "provider=Micro soft.Jet.OLEDB. 4.0;data
source=C:\1A\te st1.xls;Extende d Properties=Exce l 8.0"

daOle = New OleDbDataAdapte r
daOle.SelectCom mand = New OleDbCommand
daOle.SelectCom mand.Connection = conOle

ds = New DataSet

End Sub

Private Sub btnReadFromExce l_Click(...) Handles ...

daOle.SelectCom mand.CommandTex t = "Select * From [Sheet1$]"
daOle.Fill(ds, "tbl1")

'--this is a datagridview I added to the form
dgrv1.DataSourc e = ds.Tables("tbl1 ")
End Sub
End Class
---------------------------------------------------

Note: you should do a Sheet1.UsedRang e.Select in Excel to see if there
are any empty rows/columns that are part of the sheet's used range. Go
into Tools/Macro/VisualBasic Editor -- add a code module and type

Sub xxxx()
Sheet1.UsedRang e.Select
End Sub

Then in Tools/Macros -- run the macro xxxx to select the Sheet's
usedRange.

You should delete these extra rows/columns and then save the Excel file.
Otherwise, you will import a bunch of empty rows/columns into your
dataset which could make it look like you are not retrieving any data.
The method above will import the entire UsedRange of the Excel Sheet.

Rich

*** Sent via Developersdex http://www.developersdex.com ***