read from excel work*** and load it into Oracle
- From: constantin <constantin@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Mon, 7 Apr 2008 09:05:00 -0700
I am new to VB, I am woking on a task to read from excel work*** and
dispaly it in excel format, then load it into Oracle repository my table name
is employee and has 2 columns:employee_id pk, employee_name not null. So far
I tried to use ADO but my code isn't displaying what I mentioned above,this
is my code, I am using windowns XP proffesional. Thanks
Imports System
Imports System.Data ' VB.NET
Imports Oracle.DataAccess.Client ' ODP.NET Oracle data provider
'Connecting to an Excel Workbook with ADO
Public Class ConnectToExcelUsingSheetName
Public Shared Sub Main()
Sub ConnectToExcelUsingSheetName()
On Error GoTo ConnectToExcelUsingSheetName_Err
' Declare variables
Dim con1 As New ADODB.Connection
Dim rst As New ADODB.Recordset
'Step 1 define an object con of type OracleConnection
Dim con As New Oracle.DataAccess.Client.OracleConnection("Data
Source=dprod;User Id=smughrabi; Password=Sul99666")
Dim ConString As String
ConString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\employee.xls;" & _
"Extended Properties=""Excel 8.0;HDR=Yes"";"
con.ConnectionString = ConString
con.Open()
Dim cmdQuery As String = "SELECT * FROM employee"
'create command object to work with select
Dim dbCommand As New OracleCommand(cmdQuery, Sql)
'specifying the name of the work*** you must add a dollar
'sign ($) to the end of the name
rst.Open("SELECT * FROM [Sheet1$];", con1,
ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly)
' Loop through the recordset and send data to the Immediate Window
rst.MoveFirst()
Do
Debug.Print(rst![Entity Name] & " " & rst![Entity/Table
Definition] & " " & rst! _
[Attribute Name]"(" & rst![Attribute/Column Definition] & ")")
rst.MoveNext()
Loop Until rst.EOF
Tidy(up)
ConnectToExcelUsingSheetName_Exit:
On Error Resume Next
rst.Close()
con1.Close()
rst = Nothing
con1 = Nothing
Exit Sub
ConnectToExcelUsingSheetName_Err:
MsgBox(Err.Number & vbCrLf & Err.Description, vbCritical, "Error!")
Resume ConnectToExcelUsingSheetName_Exit
End Sub
End Class
.
- Follow-Ups:
- Re: read from excel work*** and load it into Oracle
- From: Jan Hyde (VB MVP)
- Re: read from excel work*** and load it into Oracle
- Prev by Date: Problems with ADODB.Command and SQLServer 2005
- Next by Date: Re: read from excel work*** and load it into Oracle
- Previous by thread: Problems with ADODB.Command and SQLServer 2005
- Next by thread: Re: read from excel work*** and load it into Oracle
- Index(es):
Loading