Member Avatar for hforte

I have a project where I have to send data from an Excel spreadsheet to an Oracle database.

Here is the code I am using and I need some help. It stops at the second string statement. I am trying to use an Excel range name to Insert or Update into an Oracle database. Any help would be appreciated.

Const hostName = "host"
Const portNo = "port"
Const srvSID = "SID"
Const usrID = "username"
Const usrPwd = "password"

Sub ADOInsert()
Dim cnAdo As ADODB.Connection
Dim strConnString As String
Dim strSQL As String
Dim ws1 As Excel.Worksheet
'set the worksheet:
Set ws1 = ThisWorkbook.Worksheets("IMPDATA")
Dim Imprange As Range
Set Imprange = ws1.Range("a2:d6")

    strDriver = "Driver={Microsoft ODBC for Oracle};"
    strParams = "CONNECTSTRING=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=" + hostName + ")(PORT=" + portNo + "))(CONNECT_DATA=(SID=" + srvSID + ")));"
    strUser = "UID=" + usrID + ";PWD=" + usrPwd + ";"

    strConnString = strDriver + strParams + strUser

    Set cnAdo = New ADODB.Connection
    cnAdo.Open strConnString

    '/ this is hardcoded for number of rows and columns.
    For lngRow = 1 To 10
        strSQL = "Insert Into TESTDATA1 Select "
        strSQL = strSQL & "'" & Range(Imprange, 1).Value & "', "   '/ REQUEST_NUMBER
        strSQL = strSQL & "'" & Range(Imprange, 2).Value & "', "   '/ CID
        strSQL = strSQL & "'" & Range(Imprange, 3).Value & "', "   '/ SERVER_TYPE
        strSQL = strSQL & "'" & Range(Imprange, 4).Value & "' "    '/ COST
        cnAdo.Execute strSQL
    Next
    cnAdo.Close
    Set cnAdo = Nothing
End Sub
Edited by Reverend Jim because: Moved to programming forum
  • 2 Contributors
  • 1 Reply
  • 2K Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by rproffitt

Dani AI

Generated

Agree with : the failure is almost certainly on the line where you build strSQL from the range. Range(Imprange, 1) is not a valid way to index into a block; use the Cells indexer on your block and iterate only the rows you actually have. Also, if you keep the INSERT...SELECT pattern in Oracle, you must add FROM dual at the end, or just switch to VALUES, which is clearer. See Oracle’s note on selecting from DUAL. Selecting from the DUAL table. For indexing a range, see Range.Cells. Range.Cells property (Excel).

Here is a compact pattern that avoids string concatenation and uses parameters (assumes Imprange is set to your A2:D6 block):

Dim cn As New ADODB.Connection, cmd As New ADODB.Command, r As Long
cn.Open "Provider=OraOLEDB.Oracle;Data Source=host:port/service;User Id=...;Password=..."  ' OraOLEDB
With cmd
  .ActiveConnection = cn
  .CommandType = adCmdText
  .CommandText = "insert into TESTDATA1 (REQUEST_NUMBER,CID,SERVER_TYPE,COST) values (:p1,:p2,:p3,:p4)"
  .Parameters.Append .CreateParameter("p1", adVarChar, adParamInput, 50)
  .Parameters.Append .CreateParameter("p2", adVarChar, adParamInput, 50)
  .Parameters.Append .CreateParameter("p3", adVarChar, adParamInput, 50)
  .Parameters.Append .CreateParameter("p4", adDouble,  adParamInput)
End With
For r = 1 To Imprange.Rows.Count
  cmd(0).Value = Imprange.Cells(r, 1).Value
  cmd(1).Value = Imprange.Cells(r, 2).Value
  cmd(2).Value = Imprange.Cells(r, 3).Value
  cmd(3).Value = Imprange.Cells(r, 4).Value
  cmd.Execute
Next

Two extra tips:

Member Avatar for rproffitt

It's been a week so help others help you and call out what line number you see it fail at. My reading of your problem statement has me looking at line 18. Is that it?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.