none
Problem with Reading Data from Excel using ADO.NET RRS feed

  • Question

  • Hi,

     

    I am trying to read data from Excel and import into my ASP.NET web application. This runs perfect on my local computer (in debug mode), but does not run when I deploy it to the test/production environment. First, I got the Unexpected exception, which I solved by providing access to ASPNET account on the TEMP folders. Now I continue to get this error while trying to run the code on the server. I 've tried with worksheets, ranges, nothing seems to work on the server, but locally there's no problems.

     

    The error is: The Microsoft Jet Database Engine could not find the Object 'ABC$'. Make sure the object exists and you spell its name and the path correctly.

     

    Anyhelp is greatly appreciated. Here's the code Thanks.

     

          

    Code Snippet

      Public Overrides Sub BtnImportExcel_Click(ByVal sender As Object, ByVal args As EventArgs)

                Dim Rins_Datagrid As New DataGrid
                Dim _filename As String = UploadRINFmFile.PostedFile.FileName
                Dim myDataSet As New System.Data.DataSet()
                Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source=" & _filename & ";" & _
                "Extended Properties=""Excel 8.0;"""
                Dim objConn As New System.Data.OleDb.OleDbConnection(strConn)
                objConn.Open()

                Dim objCmdSelect As New System.Data.OleDb.OleDbCommand("SELECT * FROM [RINS$]", objConn)
                Dim myData As New System.Data.OleDb.OleDbDataAdapter
                myData.SelectCommand = objCmdSelect
                Dim Rin_Number As String
                Dim Excel_Row_ID As Integer
                Dim Excel_Rows As Integer
                Dim Rin_Type As String = ""


     

                Try
                    myData.Fill(myDataSet)
                    Rins_Datagrid.DataSource = myDataSet.Tables(0).DefaultView
                    Rins_Datagrid.DataBind()
                    Excel_Row_ID = 0
                    Excel_Rows = Rins_Datagrid.Items.Count

                    If Excel_Rows > 0 Then
                        For Excel_Row_ID = 0 To Excel_Rows - 1
                            Updated_RIN_Number = Remove_Sp_Characters(Rins_Datagrid.Items(Excel_Row_ID).Cells(0).Text)
                            If Len(Updated_RIN_Number) <> 38 Then
                                If Updated_RIN_Number = "&nbsp" Then Exit For
                                Throw New Exception("Not all the Lines are of 38 digits. Error found on Line #: " & CStr(Excel_Row_ID))
                            End If
                        Next Excel_Row_ID

                        For Excel_Row_ID = 0 To Excel_Rows - 1
                            Rin_Number = ""
                            Rin_Number = Remove_Sp_Characters(Rins_Datagrid.Items(Excel_Row_ID).Cells(0).Text)
                            If Rin_Number = "&nbsp" Then Exit For
                            Rin_Type = Left(Rin_Number, 1)

                            Save_Excel_Data(parameters....)
                        Next Excel_Row_ID
                        myData.Dispose()
                    Else
                        Throw New Exception("No lines found in the Excel sheet. Please check and try again.")
                    End If
                    Me.Page.Response.Redirect("Completed.aspx?a=" & Me.Page.Request.QueryString("abcd"))
                Catch ex As Exception
                    Utils.MiscUtils.RegisterJScriptAlert(Me, "UNIQUE_SCRIPTKEY", ex.Message)
                End Try

     

     

     

    Thursday, March 27, 2008 4:46 PM

Answers

  • I solved the problem.

     

    The issue was that I was trying to access a file from the server that was on my local machine.

     

    I then saved the file to a location on the server and gave access ASPNET account to the folder and the temp directory in the documents and settings. I also had to grant read/write permissions on these folders to my Network ID.

     

    Thanks for all your responses.

    Friday, March 28, 2008 4:51 PM

All replies

  • Hi Paras -

     

    Where exactly is this failing? Is it at the line objConn.Open()? What is the value of the variable _filename you are passing in, is it ABC$?

    Thursday, March 27, 2008 8:00 PM
  •  

    Paras,

     

    Not sure about 2.0, but I found that working with Office applications in 1.14 from ASP.NET required an elevation in permission wtihin the Machine.Config. 

     

      <processModel enable="true" timeout="Infinite" idleTimeout="Infinite" shutdownTimeout="0:00:05" requestLimit="Infinite" requestQueueLimit="5000" restartQueueLimit="10" memoryLimit="60" webGarden="false" cpuMask="0xffffffff" userName="machine" password="AutoGenerate" logLevel="Errors" clientConnectedCheck="0:00:05" comAuthenticationLevel="Connect" comImpersonationLevel="Impersonate" responseDeadlockInterval="00:03:00" maxWorkerThreads="20" maxIoThreads="20"/>

     

    The processModel username by default is set to machine.  Setting this value to System might help resolve your issue. 

     

     

    Friday, March 28, 2008 12:37 AM
  • Make sure that application has permissions to access the path specified in _filename variable. Keep in mind that IIS uses virtual path in your case it could be physical path, so you need to be sure that you refer same forlder.

    Friday, March 28, 2008 9:58 AM
    Moderator
  • Is ABC$ the actual name of the Worksheet? I don't see it referenced anywhere in your code.

    If it's not a Worksheet name issue then I suspect it's probably a file permissions problem as Val mentioned.

    Friday, March 28, 2008 12:12 PM
  • Hi..Thanks for all your responses. Unfortunately the problem isn't solved as yet.

     

    RINS$ is the name of the sheet. I am referring it correctly. As I said, this program runs perfect when run from my local machine. Only when I deploy it to another server (test/production), it comes up with this error message.

     

    I checked the machine.config, there is no problem over there.

     

    Please help.

    Friday, March 28, 2008 1:44 PM
  • What is the path to the Excel Workbook?

    Is your web application configured for impersonation? If so, what type of authentication are you using (Anonymous, Basic, Integrated NT)?

    Friday, March 28, 2008 2:27 PM
  • I solved the problem.

     

    The issue was that I was trying to access a file from the server that was on my local machine.

     

    I then saved the file to a location on the server and gave access ASPNET account to the folder and the temp directory in the documents and settings. I also had to grant read/write permissions on these folders to my Network ID.

     

    Thanks for all your responses.

    Friday, March 28, 2008 4:51 PM