need help with array in VB

  • Thread starter Thread starter constantin
  • Start date Start date
C

constantin

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,employee),2-entity/table
Definition(no rows),3-attribute name has 2
rows(employee_id,employee_name),4-Attribute/Column Definition with no rows.
My code is below:

Imports System.Data.OleDb
Imports System.Data
Imports Oracle.DataAccess.Client

Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim DS As System.Data.DataSet
Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
Dim MyConnection As System.Data.OleDb.OleDbConnection

MyConnection = New System.Data.OleDb.OleDbConnection( _
"provider=Microsoft.Jet.OLEDB.4.0; " & _
"data source=C:\employee.XLS; " & _
"Extended Properties=Excel 8.0;")
' Select the data from Sheet1 of the workbook.
' Create OleDbCommand object and select data from worksheet Sheet1
MyCommand = New System.Data.OleDb.OleDbDataAdapter( _
"select * from [Sheet1$]", MyConnection)

DS = New System.Data.DataSet()

' Fill the DataSet from the data extracted from the worksheet.
MyCommand.Fill(DS)
MyConnection.Close()
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 OracleConnection = New OracleConnection()
Try
'2.Specify connection string
con.ConnectionString = ("Data Source=damn;User 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.Message, Microsoft.VisualBasic.MsgBoxStyle.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(cmdQuery)
cmd.Connection = con
cmd.CommandType = CommandType.Text

' Dispose OracleCommand object
cmd.Dispose()
Finally

' Close and Dispose OracleConnection object
con.Close()
con.Dispose()

End Try
End Sub
End Class
 
constantin said:
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,employee),2-entity/table
Definition(no rows),3-attribute name has 2
rows(employee_id,employee_name),4-Attribute/Column Definition with no
rows.
My code is below:

Imports System.Data.OleDb
Imports System.Data
Imports Oracle.DataAccess.Client

Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim DS As System.Data.DataSet
Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
Dim MyConnection As System.Data.OleDb.OleDbConnection

MyConnection = New System.Data.OleDb.OleDbConnection( _
"provider=Microsoft.Jet.OLEDB.4.0; " & _
"data source=C:\employee.XLS; " & _
"Extended Properties=Excel 8.0;")
' Select the data from Sheet1 of the workbook.
' Create OleDbCommand object and select data from worksheet Sheet1
MyCommand = New System.Data.OleDb.OleDbDataAdapter( _
"select * from [Sheet1$]", MyConnection)

DS = New System.Data.DataSet()

' Fill the DataSet from the data extracted from the worksheet.
MyCommand.Fill(DS)
MyConnection.Close()
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 OracleConnection = New OracleConnection()
Try
'2.Specify connection string
con.ConnectionString = ("Data Source=damn;User 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.Message, Microsoft.VisualBasic.MsgBoxStyle.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(cmdQuery)
cmd.Connection = con
cmd.CommandType = CommandType.Text

' Dispose OracleCommand object
cmd.Dispose()
Finally

' Close and Dispose OracleConnection object
con.Close()
con.Dispose()

End Try
End Sub
End Class
Click to expand...

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
 
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
 
constantin said:
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
Click to expand...

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
 
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.
 
Back
Top