none
Querytables Oracle RRS feed

  • Question

  • Good night!

    Please forgive my English.

    I'm trying to develop an Excel Workbook application with Visual Studio 2010.

    I worked with Sql Server and I don't know a lot about Oracle, but this Excel Workbook must run on Oracle.

    I installed Oracle in my laptop for testing and only could connect whit a string using TSNames File format but no with ODBC.

    I'm trying to put data into a worksheet using Query Tables but I can't.

    My code in this moment work fine like this:

    Imports Oracle.DataAccess.Client
    Public Class ThisWorkbook
      Public Sub Test()
        Dim Filas As Integer = 0  
        Try
          Dim oradb As String = "Data Source=(DESCRIPTION=" & vbCrLf &
                 "(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))" & vbCrLf &
                 "(CONNECT_DATA=(SERVER = DEDICATED)(SERVICE_NAME=ORCL)));" & vbCrLf &
                 "User Id=C##SOMEUSER;Password=SOMEPASSWORD;"
          Dim conn As New OracleConnection(oradb)
          conn.Open()
          Dim sql As String =
            "SELECT" & vbCrLf &
            " *" & vbCrLf &
            "FROM" & vbCrLf &
            " Any_Table"
          Dim cmd As New OracleCommand(sql, conn)
          cmd.CommandType = CommandType.Text
          Dim da As OracleDataAdapter = New OracleDataAdapter
          Dim dt As System.Data.DataTable = New System.Data.DataTable()
          da.SelectCommand = cmd
          da.Fill(dt)
          For Each dr As System.Data.DataRow In dt.Rows
            Filas += 1
            For Veces As Integer = 1 To dt.Columns.Count
              If Filas = 1 Then
                Globals.Hoja1.Cells(1, Veces) = dt.Columns(Veces - 1).ColumnName
              End If
              Globals.Hoja1.Cells(Filas + 1, Veces) = dr(Veces - 1).ToString()
            Next
          Next
          cmd.Dispose()
          da.Dispose()
          dt.Dispose()
          conn.Close()
          conn.Dispose()
        Catch ex As Exception
          MsgBox(ex.Message, MsgBoxStyle.Critical, "Some Error Message")
        Finally
        End Try
    End Class

    As I said my code works fine but the SELECT sentence returns about 1500 rows and it takes about 4 or 5 minutes to put data into de Excel WorkSheet cell by cell.

    I read this article:

    QueryTables.Add

    But I don't know how to connect Visual Studio to Oracle using "A string containing an OLE DB or ODBC connection string. The ODBC connection string has the form "ODBC;<connection string>".", because I always have been used SQL Server with a string connection something like this ("Server=" + txt_Servidor.Text + ";Database=" + txt_BD.Text + ";Integrated Security=SSPI;)

    May anybody help me saying me what am I doing wrong or what must I do put all data at once or faster in the Excel Worksheet. Please remember that I don't know much about Oracle.

    Thanks a lot for your valuable help.


    Jamesit0

    Wednesday, August 6, 2014 2:28 AM

Answers

  • Hi,

    >>But I don't know how to connect Visual Studio to Oracle using "A string containing an OLE DB or ODBC connection string.<<

    I think you could use QueryTables.Add method to import data form Oracle into the worksheet.

    To get an OLE DB or ODBC connection string, an easier way is to import data from Oracle manually and record a macro about the process. Then you could refer to the connection string in the macro and use it into the sample provided in QueryTables.Add to achieve the goal.

    To import the data from Oracle manully, you could resort to Data-> From Other Sources as followed. You could import data From Data Connection Wizard or From Microsoft Query, which could import data from an unlisted format by using Data Connection Wizard and OLEDB or ODBC.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Jamesit0 Saturday, August 9, 2014 1:46 AM
    Thursday, August 7, 2014 5:33 AM
    Moderator

All replies

  • Hi,

    >>But I don't know how to connect Visual Studio to Oracle using "A string containing an OLE DB or ODBC connection string.<<

    I think you could use QueryTables.Add method to import data form Oracle into the worksheet.

    To get an OLE DB or ODBC connection string, an easier way is to import data from Oracle manually and record a macro about the process. Then you could refer to the connection string in the macro and use it into the sample provided in QueryTables.Add to achieve the goal.

    To import the data from Oracle manully, you could resort to Data-> From Other Sources as followed. You could import data From Data Connection Wizard or From Microsoft Query, which could import data from an unlisted format by using Data Connection Wizard and OLEDB or ODBC.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Jamesit0 Saturday, August 9, 2014 1:46 AM
    Thursday, August 7, 2014 5:33 AM
    Moderator
  • Good Night!

    Thanks a lot for your tip. I tried that before but Excel doesn't let me connect to Oracle. After trying different ways to connect I could do it.

    My final working code was:

      Public Sub Test()
        Dim sql As String =
          "SELECT" & vbCrLf &
          " *" & vbCrLf &
          "FROM" & vbCrLf &
          " Any_Table"
        Dim connstring As String =
          "OLEDB;" & vbCrLf &
          "Provider=OraOLEDB.Oracle.1;" & vbCrLf &
          "Data Source=" & vbCrLf &
          """(DESCRIPTION=" & vbCrLf &
          "   (ADDRESS_LIST=" & vbCrLf &
          "    (ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))" & vbCrLf &
          "   )" & vbCrLf &
          "   (CONNECT_DATA=" & vbCrLf &
          "    (SERVER=DEDICATED)" & vbCrLf &
          "    (SERVICE_NAME=orcl)" & vbCrLf &
          "   )" & vbCrLf &
          "  )"";" & vbCrLf &
          "Persist Security Info=True;" & vbCrLf &
          "Extended Properties="""";" & vbCrLf &
          "User ID=C##SOMEUSER;" & vbCrLf &
          "Password=SOMEPASSWORD;"
        With Globals.Hoja1.QueryTables.Add(connstring, Globals.Hoja1.Range("A1"), sql)
          .Refresh()
        End With
      End Sub

    This link helped me alot because I was using a ODBC and it didn't work I had to use a OLEDB.

    Query Tables

    Thanks a lot again.


    Jamesit0


    • Edited by Jamesit0 Saturday, August 9, 2014 1:52 AM
    Saturday, August 9, 2014 1:46 AM