Insert new rows from excel to oracle

Guys, I need some help.
I have a sample vba script which successfully gets data from oracle and places it into excel.

What I want to do is take information from excel sheet and insert into oracle

here is the script but please note I have just used static values but the idea is to use data from an excel sheet

Sub Insert_Oracle()

Dim cn As ADODB.Connection, rs As ADODB.Recordset, intColIndex As Integer
Dim source As String
Dim user As String
Dim password As String
Dim rg As Range
Source = Login.TextBox1.Value
user = Login.TextBox2.Value
password = Login.TextBox3.Value

' open the database

Set cn = New ADODB.Connection
    cn.Open "Provider=OraOLEDB.Oracle;" & _
           "Data Source=" & Source & ";" & _
           "User Id=" & user & ";" & _
           "Password=" & password & ""
Set rs = New ADODB.Recordset

    With rs
' open the recordset
   
'    sSQL = "SELECT p.PROJECT_ID, p.SUB_PROJECT_ID, p.ACTIVITY_NO, p.ACTIVITY_SEQ, p.DESCRIPTION, p.PROJECT_ID||p.ACTIVITY_NO as ID " & _
'           "FROM IFSAPP.PROJECT_ACTIVITY_ALL p WHERE (p.SUB_PROJECT_ID='50') "

    sSQL = "INSERT INTO IFSAPP.TESTTEST (OBJECT_ID, WO_NO, SITE, CRAFT, CRAFT_RATE, QTY_HOURS, AMOUNT, DAY, OBJID)" & _
           "values ( 10007, 11111, 'B0101', 'PAINT100', 99, 10, 1000, 12/03/2015, SYSDATE); "

    rs.Open sSQL, cn, adOpenStatic, adLockReadOnly, adCmdText
       
For intColIndex = 0 To rs.Fields.Count - 1 ' the field names
            TargetRange.Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name
  Next
    TargetRange.Offset(1, 0).CopyFromRecordset rs ' the recordset data

  End With
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
   
   
End Sub

Can someone please modify this so that I can insert records from SHEET1 in excel into a table called TESTTEST.

Your help is very much appreciated
DarrenJacksonAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Alex [***Alex140181***]Software DeveloperCommented:
This is quite straightforward ;-) You may start with some examples here: https://www.nsbasic.com/desktop/info/technotes/TN22.htm
More can be found with the help of Dr. Google :-))

When you want to perform DMLs using ADODB, you'll have to work with transactions explicitly, e.g.:
'SQL
'...
strSQL="INSERT INTO MY_TABLE(COL1,COL2) VALUES('" & myVAR1 & "','" & myVAR2 & "')"

'Begin Transaction
objADO.BeginTrans

objADO.Execute strSQL

'Commit Transaction
objADO.CommitTrans
'...

Open in new window

DarrenJacksonAuthor Commented:
Hi Alexander, Thanks for getting back to me.

I have viewed that website but couldn't get to work there solutions.

I have tried your solution but it errors but I am thinking that this isn't correct

Sub Insert_Oracle()

Dim cn As ADODB.Connection, rs As ADODB.Recordset, intColIndex As Integer
Dim source As String
Dim user As String
Dim password As String
Dim rg As Range
    source = Login.TextBox1.Value
    user = Login.TextBox2.Value
    password = Login.TextBox3.Value
       
    ' open the database
Set cn = New ADODB.Connection
    cn.Open "Provider=OraOLEDB.Oracle;" & _
           "Data Source=" & source & ";" & _
           "User Id=" & user & ";" & _
           "Password=" & password & ""
Set rs = New ADODB.Recordset
    With rs
    ' open the recordset
   
    sSQL = "INSERT INTO IFSAPP.TESTTEST (OBJECT_ID, WO_NO, SITE, CRAFT, CRAFT_RATE, QTY_HOURS, AMOUNT, DAY, OBJID)" & _
           "values ( 10007, 11111, 'B0101', 'PAINT100', 99, 10, 1000, 12/03/2015, SYSDATE); "
           
    'Begin Transaction
objADO.BeginTrans

objADO.Execute strSQL

'Commit Transaction
objADO.CommitTrans
           
    rs.Open sSQL, cn, adOpenStatic, adLockReadOnly, adCmdText
       
For intColIndex = 0 To rs.Fields.Count - 1 ' the field names
            TargetRange.Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name
  Next
    TargetRange.Offset(1, 0).CopyFromRecordset rs ' the recordset data

  End With
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
   
   
   
End Sub



For now I have hard coded values to get the insert working once this is working I will attempt the method I want which is to take the information from an excel sheet.

Can you help with this the error is OBJECT REQUIRED

Thanks
DarrenJacksonAuthor Commented:
Alexander, I have this working where it is now inserting static data.

Sub Insert_Oracle()

Dim cn As ADODB.Connection, rs As ADODB.Recordset, intColIndex As Integer
Dim source As String
Dim user As String
Dim password As String
Dim rg As Range
    source = Login.TextBox1.Value
    user = Login.TextBox2.Value
    password = Login.TextBox3.Value
       
' open the database
Set cn = New ADODB.Connection
    cn.Open "Provider=OraOLEDB.Oracle;" & _
           "Data Source=" & source & ";" & _
           "User Id=" & user & ";" & _
           "Password=" & password & ""
Set rs = New ADODB.Recordset
    With rs
    ' open the recordset
   
sSQL = "INSERT INTO IFSAPP.TESTTEST (OBJECT_ID, WO_NO, SITE, CRAFT, CRAFT_RATE, QTY_HOURS, AMOUNT, DAY, OBJID)" & _
       " values ( 10007, 1111111, 'B0101', 'PAINT100', 99, 10, 1000, to_date('12/03/2015','dd/mm/yyyy'), sysdate) "
           
'Begin Transaction
cn.BeginTrans

'Commit Transaction
cn.CommitTrans
           
    rs.Open sSQL, cn, adOpenStatic, adLockReadOnly, adCmdText
       
For intColIndex = 0 To rs.Fields.Count - 1 ' the field names
            TargetRange.Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name
  Next

  End With
     
   
End Sub


So how would I now get this to look at SHEET1 and starting from row 2 column A look down and insert all records it finds.

Thanks for Helping
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Alex [***Alex140181***]Software DeveloperCommented:
First of all, I suggest you put a "Option Explicit" above all your code ;-) This helps a lot, believe me...
E.g. the variable "sSQL" is NOT defined, plus, you're trying to execute an "empty"/NULL statement:
objADO.Execute strSQL

Open in new window

Where gets "strSQL" populated?!

Where exactly does this error occur?! Does this object exists / can you access its properties: "Login.TextBox1.Value"?!
DarrenJacksonAuthor Commented:
Alexander I have updated the code to allow for OPTION EXPLICIT as recommended plus added some error checking


Option Explicit

Sub Insert_Oracle()

Dim cn As ADODB.Connection, rs As ADODB.Recordset, intColIndex As Integer
Dim TargetRange As Range
Dim sSQL As String
Dim lngRecAFF
Dim source As String
Dim user As String
Dim password As String
Dim rg As Range
    source = Login.TextBox1.Value
    user = Login.TextBox2.Value
    password = Login.TextBox3.Value
Set TargetRange = Sheets("Main").Cells(1, 1)
' open the database
Set cn = New ADODB.Connection
    cn.Open "Provider=OraOLEDB.Oracle;" & _
           "Data Source=" & source & ";" & _
           "User Id=" & user & ";" & _
           "Password=" & password & ""
Set rs = New ADODB.Recordset
    With rs
    ' open the recordset
   
sSQL = "INSERT INTO IFSAPP.TESTTEST (OBJECT_ID, WO_NO, SITE, CRAFT, CRAFT_RATE, QTY_HOURS, AMOUNT, DAY, OBJID)" & _
       " values ( 10007, 1111111, 'B0101', 'PAINT100', 99, 10, 1000, to_date('12/03/2015','dd/mm/yyyy'), sysdate) "
           
'Begin Transaction
cn.BeginTrans

rs.Open sSQL, cn, adOpenStatic, adLockReadOnly, adCmdText
       
'Check the error
If cn.Errors.Count = 0 Then

         cn.CommitTrans
    Else
         cn.RollbackTrans
    End If

'Close ADO
cn.Close
       
For intColIndex = 0 To rs.Fields.Count - 1 ' the field names
        TargetRange.Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name
  Next

  End With
     
   
End Sub

So now how would I now get this to look at SHEET1 and starting from row 2 column A look down and insert all records it finds.

 Thanks for Helping
DarrenJacksonAuthor Commented:
Alexander I will attach the file that I am working on to help make this easier to understand
insert-into-oracle.xlsm
Alex [***Alex140181***]Software DeveloperCommented:
My suggestion would look something like this (needs to be adjusted to suite your needs):
Option Explicit


Sub Insert_Oracle()

Dim cn As ADODB.Connection
Dim source As String
Dim user As String
Dim password As String
Dim baseSQL As String
Dim strSQL As String
Dim rg As Range
Dim row As Long
    
    source = "bla" 'Login.TextBox1.Value
    user = "usr" 'Login.TextBox2.Value
    password = "pw" ' Login.TextBox3.Value
           
        ' open the database
    Set cn = New ADODB.Connection
        cn.Open "Provider=OraOLEDB.Oracle;" & _
               "Data Source=" & source & ";" & _
               "User Id=" & user & ";" & _
               "Password=" & password & ""
       
    baseSQL = "INSERT INTO IFSAPP.TESTTEST (OBJECT_ID, WO_NO, SITE, CRAFT, CRAFT_RATE, QTY_HOURS, AMOUNT, DAY, OBJID)" & _
             "values ( $P1, $P2, '$P3', '$P4', $P5, $P6, $P7, $P8, SYSDATE); "
               
    cn.BeginTrans
    
    row = 2
    
    Do While Sheets("SHEET1").Cells(row, 1) <> "" ' compare with Excel column that is filled until the end ;-)
    
        strSQL = Replace(baseSQL, "$P1", Sheets("SHEET1").Cells(row, 1))
        strSQL = Replace(baseSQL, "$P2", Sheets("SHEET1").Cells(row, 2))
        ' and so on... (depends on what you need and how many columns need to be filled
    
        cn.Execute strSQL
        
        cn.CommitTrans
    
    Loop
               
    cn.Close
    Set cn = Nothing
   
End Sub

Open in new window

DarrenJacksonAuthor Commented:
Alexander, When I run this it seems to ignore the first columns information and tries to insert the value $P1 instead of the actual value of 10007 then errors saying invalid character
DarrenJacksonAuthor Commented:
If it helps use the excel file I attached
Alex [***Alex140181***]Software DeveloperCommented:
Option Explicit

Public Sub Insert_Oracle()

Dim cn As ADODB.Connection
Dim baseSQL, exSQL As String
Dim source As String
Dim user As String
Dim password As String
Dim row, i As Long
    
    source = Login.TextBox1.Value
    user = Login.TextBox2.Value
    password = Login.TextBox3.Value

    Set cn = New ADODB.Connection
    cn.Open "Provider=OraOLEDB.Oracle; Data Source=" & source & "; User Id=" & user & "; Password=" & password & ""
        
    baseSQL = "INSERT INTO IFSAPP.TESTTEST (OBJECT_ID, WO_NO, SITE, CRAFT, CRAFT_RATE, QTY_HOURS, AMOUNT, DAY, OBJID) " & _
              "VALUES ( $col1, $col2, '$col3', '$col4', $col5, $col6, $col7, to_date('$col8','dd/mm/yyyy'), SYSDATE) "
               
    cn.BeginTrans
    
    row = 2

    Do While Cells(row, 1) <> ""
    
        exSQL = baseSQL
        
        For i = 1 To 9
            exSQL = Replace(exSQL, "$col" & i, Cells(row, i))
        Next i
        
        cn.Execute exSQL
    
        row = row + 1
    
    Loop
    
    If cn.Errors.Count = 0 Then
        cn.CommitTrans
    Else
        cn.RollbackTrans
    End If
    
    cn.Close
           
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DarrenJacksonAuthor Commented:
Perfect thankyou for all your help :)
Alex [***Alex140181***]Software DeveloperCommented:
You're welcome, no problem ;-)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.