My web page hangs while trying to fetch data into my Pivot Cache

Hi I am trying to import some data into an Excel Pivot Cache. The codes are in VB.NET. As soon as the PivotTables.Add() is encountered, my browser just hangs!! Nothing happens, no error nothing! Can someone help??

Imports System.Data.OracleClient
Imports System.Runtime.InteropServices
Imports System.IO

Public Class ASP_Pivot_Table
    Dim strConnection As String
    Dim strSQL As String
    Dim xlApp As Excel.Application
    Dim m_oExcelApp As Excel.ApplicationClass  'method
    Dim m_oBooks As Excel.Workbooks       'interface - collection of all Workbook objects that are currently open in the Microsoft Excel Application
    Dim m_oBook As Excel._Workbook    'interface
    Dim m_oSheet As Excel._Worksheet 'interface
    Dim m_oPt As Excel.PivotTable
    Dim m_sReportTemplate As String
    Dim m_nReportIndex As Int32
    Dim m_sDataTemplate As String
    Dim m_sExportFormat As String
    Dim m_sOutputCache As String

    Public Property setQuery()
            Return strSQL
        End Get
        Set(ByVal Value)
            strSQL = Value
        End Set
    End Property

    Public Sub OpenReportTemplate()

        If (Not (m_oExcelApp Is Nothing)) Then
        End If

        m_oExcelApp = New Excel.ApplicationClass
        m_oExcelApp.Visible = True 'False
        m_oBooks = m_oExcelApp.Workbooks

        ' EXCEL VERSION 10.0 or higher
        m_oBook = m_oBooks.Open(m_sReportTemplate)

        ' END EXCEL VERSION 10.0 or higher
        m_oSheet = CType(m_oBook.Worksheets(m_nReportIndex), Excel._Worksheet)

    End Sub

    Public Sub CreatePivot()

        Dim sReportFile As String
        Dim sMessage As String
        Dim sDeleteFile As String

          ' Get temporary file name

        ' Get valid report tempate

        Dim xlRange As Excel.Range = m_oSheet.Range("B2")

        'Create the Pivot Cache
        Dim ptCache As Excel.PivotCache = m_oBook.PivotCaches.Add(Excel.XlPivotTableSourceType.xlExternal)


            'Set up the Pivot Cache
            With ptCache
                .CommandText = strSQL
                .CommandType = Excel.XlCmdType.xlCmdSql
                .Connection = "ODBC;DRIVER={Oracle in Ora_home};UID=abcd;PWD=123;SERVER=MyServer;"
            End With

        End Try
        m_oSheet.Name = "PT_Report"

            'Create the Pivot Table
            Dim ptTable As Excel.PivotTables = CType(m_oSheet, Excel.PivotTables).PivotTables.Add(ptCache, xlRange, "PT_Report")  ' -> This line hangs!!!!!!!
   Thanks in advance
Who is Participating?
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.

One of the biggest problems with using Excel like this is that it is not a server application, it runs in a user workspace. That means that it will try and show dialogs, etc etc and you won't ever see them as it will show them in ASPNETs user workspace, not yours.

If this is your problem, then there is no way around it other than try and guess what the dialog says and find a way not to show it. Office Web Components are the server-based component from MS that will do a lot of Excel's functionality without the user workspace problems.

Here's the original MSDN KB article I read when I discovered this, much to my disappointment:

Considerations for server-side Automation of Office

anand_007Author Commented:
I have seen this article before :). What I am doing is to create the Excel sheet on the Server and then export it to the user end. This is not a truly network environement but my users are loyal Excel followers .. they can't have enough of Excel!

As for your first comment, I had made that assumption initially. So I tested the code in a standlaone VB.NET application. I created a dll out of the Pivot table bit, created properties to set the SQL string and the connection from the calling VB.NET program and ran it. It runs beautifully there. But somehow it won't create the Pivot Table here .. I am at my wit's end!!
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

anand_007Author Commented:
Also, I commented out the Pivot Table bit where the code is hanging. This is followed by the Orientation for the elements. I commented out that bit as well and ran - the rest of the code runs and produces an output - its just this one bit that is obstinate :(

Did you have any luck with this? As no-one else has posted here, why don't you request this question be closed and refunded in the admin area, and open a new one in, unless you found a solution, in which case you could PAQ and refund instead.

anand_007Author Commented:
Hi, no I've had no luck on this!! I don't suppose this is an area ventured into, unless there is a specific requirement on those lines. There is a problem while trying to populate the cache. On a plain vanilla VB.NET, you can see an MS Query connection opening and a query being fired to Oracle from Excel. However, this doesn't seem to happen on ASP.NET. The crux I feel lies in the ODBC connection - this seems to fail when you want to connect from Excel. If there were any other way to connect to Oracle, my hunch is this would succeed. But I haven't found out any such method online yet. I'm exploring OWC and third party tools
anand_007Author Commented:
How do I PAQ???
PAQed with points refunded (500)

Community Support Moderator

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
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

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.