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

Posted on 2006-03-22
Last Modified: 2012-05-05
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
Question by:anand_007
    LVL 12

    Expert Comment

    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.

    LVL 12

    Expert Comment

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

    Considerations for server-side Automation of Office

    LVL 2

    Author Comment

    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!!
    LVL 2

    Author Comment

    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 :(
    LVL 12

    Expert Comment


    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.

    LVL 2

    Author Comment

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

    Author Comment

    How do I PAQ???
    LVL 12

    Expert Comment


    Accepted Solution

    PAQed with points refunded (500)

    Community Support Moderator

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    This article discusses the ASP.NET AJAX ModalPopupExtender control. In this article we will show how to use the ModalPopupExtender control, how to display/show/call the ASP.NET AJAX ModalPopupExtender control from javascript, how to show/display/cal…
    Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now