need help with array in VB

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

I know my question seems easy,how to build an array to read the data from
Excel worksheet table then load the data into oracle.The table(employee)
looks like this have 4 columns:1-entity name with two
rows(employee,e mployee),2-entity/table
Definition(no rows),3-attribute name has 2
rows(employee_i d,employee_name ),4-Attribute/Column Definition with no rows.
My code is below:

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 DS As System.Data.Dat aSet
Dim MyCommand As System.Data.Ole Db.OleDbDataAda pter
Dim MyConnection As System.Data.Ole Db.OleDbConnect ion

MyConnection = New System.Data.Ole Db.OleDbConnect ion( _
"provider=Micro soft.Jet.OLEDB. 4.0; " & _
"data source=C:\emplo yee.XLS; " & _
"Extended Properties=Exce l 8.0;")
' Select the data from Sheet1 of the workbook.
' Create OleDbCommand object and select data from worksheet Sheet1
MyCommand = New System.Data.Ole Db.OleDbDataAda pter( _
"select * from [Sheet1$]", MyConnection)

DS = New System.Data.Dat aSet()

' Fill the DataSet from the data extracted from the worksheet.
MyCommand.Fill( DS)
MyConnection.Cl ose()
Debug.Print(DS. Tables(0).Rows( 0).Item(0))
Debug.Print(DS. Tables(0).Rows( 0).Item(2))
Debug.Print(DS. Tables(0).Rows( 1).Item(0))
Debug.Print(DS. Tables(0).Rows( 1).Item(2))
'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=damn;Use r Id=gms;
Password=rts123 ")

'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 employee"

' 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

[Lloyd Sheen]

"constantin " <co********@dis cussions.micros oft.comwrote in message
news:E0******** *************** ***********@mic rosoft.com...

>I know my question seems easy,how to build an array to read the data from
Excel worksheet table then load the data into oracle.The table(employee)
looks like this have 4 columns:1-entity name with two
rows(employee,e mployee),2-entity/table
Definition(no rows),3-attribute name has 2
rows(employee_i d,employee_name ),4-Attribute/Column Definition with no
rows.
My code is below:

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 DS As System.Data.Dat aSet
Dim MyCommand As System.Data.Ole Db.OleDbDataAda pter
Dim MyConnection As System.Data.Ole Db.OleDbConnect ion

MyConnection = New System.Data.Ole Db.OleDbConnect ion( _
"provider=Micro soft.Jet.OLEDB. 4.0; " & _
"data source=C:\emplo yee.XLS; " & _
"Extended Properties=Exce l 8.0;")
' Select the data from Sheet1 of the workbook.
' Create OleDbCommand object and select data from worksheet Sheet1
MyCommand = New System.Data.Ole Db.OleDbDataAda pter( _
"select * from [Sheet1$]", MyConnection)

DS = New System.Data.Dat aSet()

' Fill the DataSet from the data extracted from the worksheet.
MyCommand.Fill( DS)
MyConnection.Cl ose()
Debug.Print(DS. Tables(0).Rows( 0).Item(0))
Debug.Print(DS. Tables(0).Rows( 0).Item(2))
Debug.Print(DS. Tables(0).Rows( 1).Item(0))
Debug.Print(DS. Tables(0).Rows( 1).Item(2))
'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=damn;Use r Id=gms;
Password=rts123 ")

'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 employee"

' 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

You should have an "array" after the MyCommand.Fill( DS). The problem I see
is your Oracle. You need to insert data not select it. Where exactly is
the problem?
LS

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

The problem is I need to read data from excel worksheet then load the data to
Oracle. It isn't doning so.plz refer to the first posting for excel worksheet
detail.
Thanks

[Armin Zingler]

"constantin " <co********@dis cussions.micros oft.comschrieb

The problem is I need to read data from excel worksheet then load
the data to Oracle. It isn't doning so.plz refer to the first
posting for excel worksheet detail.
Thanks

In your code, you don't write the data into the Oracle database. Do you
want to know how to do it or what is your question? As Lloyd wrote, you
need to execute an Insert SQL to write the data. However, only
changed/deleted/added rows in the dataset are written. It depends on the
Rowstate of the Datarows which is "unchanged" after reading fromt the
Excel file, no row is written even if you'd execute the Insert.
Armin

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

thanks I need to know how to write data to oracle using the information I
provided in my last email. Can someone correct my code.