Solved

ASP.net - error trying to view an uploaded spreadsheet

Posted on 2011-03-13
3
342 Views
Last Modified: 2012-05-11
JHi. I am getting the following error in the code below on the line marked XXXX
"External table is not in the expected format"
Protected Sub ButtonUpload_Click(ByVal sender As Object, ByVal e As EventArgs) Handles ButtonUpload.Click
        PanelUpload.Visible = True
        PanelView.Visible = False
        PanelImport.Visible = False
    End Sub

    Protected Sub ButtonUploadFile_Click(ByVal sender As Object, ByVal e As EventArgs) Handles ButtonUploadFile.Click
        If FileUploadExcel.HasFile Then
            Try
                ' alter path for your project
                FileUploadExcel.SaveAs(Server.MapPath("~/ExcelImport.xls"))
                LabelUpload.Text = "Upload File Name: " & _
                    FileUploadExcel.PostedFile.FileName & "<br>" & _
                    "Type: " & _
                    FileUploadExcel.PostedFile.ContentType & _
                    " File Size: " & _
                    FileUploadExcel.PostedFile.ContentLength & " kb<br>"
            Catch ex As Exception
                LabelUpload.Text = "Error: " & ex.Message.ToString
            End Try
        Else
            LabelUpload.Text = "Please select a file to upload."
        End If

    End Sub
    Protected Function ExcelConnection() As OleDbCommand

        ' Connect to the Excel Spreadsheet
        Dim xConnStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
              "Data Source=" & Server.MapPath("ExcelImport.xls") & ";" & _
              "Extended Properties=Excel 8.0;"

        ' create your excel connection object using the connection string
        Dim objXConn As New OleDbConnection(xConnStr)
        objXConn.Open() 'XXXX
        ' use a SQL Select command to retrieve the data from the Excel Spreadsheet
        ' the "table name" is the name of the worksheet within the spreadsheet
        ' in this case, the worksheet name is "Members" and is expressed as: [Members$]
        Dim objCommand As New OleDbCommand("SELECT * FROM [Members$]", objXConn)
        Return objCommand

    End Function



    Protected Sub ButtonView_Click(ByVal sender As Object, ByVal e As EventArgs) Handles ButtonView.Click

        'The ButtonView_Click event handler starts by hiding the PanelUpload and PanelImport Panels,
        ' while showing the PanelView interface (which is where the GridView is located). 
        PanelUpload.Visible = False
        PanelView.Visible = True
        PanelImport.Visible = False
        'Next, a new OleDbDataAdapter object is created and its SelectCommand is assigned the OleDbCommand object returned by the ExcelConnection function. 
        ' Create a new Adapter
        Dim objDataAdapter As New OleDbDataAdapter()

        ' retrieve the Select command for the Spreadsheet
        objDataAdapter.SelectCommand = ExcelConnection()
        'The purpose of this OleDbDataAdapter object is to populate a DataSet with the contents of the Excel query; this DataSet is then bound to the GridView.
        ' Create a DataSet
        Dim objDataSet As New DataSet()

        ' Populate the DataSet with the spreadsheet worksheet data
        objDataAdapter.Fill(objDataSet)

        ' Bind the data to the GridView
        GridViewExcel.DataSource = objDataSet.Tables(0).DefaultView
        GridViewExcel.DataBind()
    End Sub

Open in new window

0
Comment
Question by:murbro
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 18

Accepted Solution

by:
Gary Davis earned 500 total points
ID: 35121644
The problem is the connection string or the file (perhaps wrong Excel version).

See these for formats and options that may be included on the connection string:

http://www.connectionstrings.com/excel
http://www.connectionstrings.com/excel-2007

Gary Davis
Webguild
0
 

Author Comment

by:murbro
ID: 35122283
Hi. Could I include code to detect the Excel version. What code would I use for that?
0
 

Author Closing Comment

by:murbro
ID: 35166615
thanks very much
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
In .NET 2.0, Microsoft introduced the Web Site.  This was the default way to create a web Project in Visual Studio 2005.  In Visual Studio 2008, the Web Application has been restored as the default web Project in Visual Studio/.NET 3.x The Web Si…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

752 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