none
Problem with Excel data source and i think permissions RRS feed

  • Question

  • My Error:

    The Microsoft Jet database engine could not find the object 'OklahomaCity$'. Make sure the object exists and that you spell its name and the path name correctly.

     

    My Background:

    VB.Net 2.0 + ASP.NET

    I have a file upload with a submit button.

    If the file is on the root of c: then everything works fine but if i use My documents or another directory such as c:\Temp I will get the above error. The File.postedfile.filename shows the correct path. the filename is CCRReport.xls

     

    My Code:

    Dim ExcelData As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & File.PostedFile.FileName & ";Extended Properties=""Excel 8.0;"""

    Dim okcExcelDataAdapter As New OleDb.OleDbDataAdapter("SELECT startdate,maxtimerange,class,maxseats,taken,avail,standby,custportables,'Oklahoma City' As Center FROM [OklahomaCity$]", ExcelData)

    okcExcelDataAdapter.Fill(ExcelDS, "ExcelData")

     

    Wednesday, November 7, 2007 3:44 PM

Answers

  • If you're using the HTMLInputFile server control can you call the SaveAs method of the PostedFile property and save it to the Sharepoint server first. I don't think you're going to be able to open it directly from the client w/o encountering an invalid path. As far as I'm concerned this would be a security issue if this was possible. 

     

    I'm surprised that this works when using the root, which leads me to believe that somehow the file is actually being downloaded (perhaps temporarily) to the Sharepoint server. You may want to check the root of your Sharepoint server to see if such a file exists. The FileName property, as interpreted by the Jet OLEDB Provider, would be a file path on the system where the app (or web part) is executing, and that of course would be the Sharepoint server.

    Thursday, November 8, 2007 3:03 PM

All replies

  • If simply moving the file around can cause the problem to disappear and reappear then I would suspect a permissions issue as you mentioned.

     

    An ASP.NET application can execute under different accounts depending upon what type of authentication you are using and whether the app is configured for impersonation. You would need to identify that account and provide it with sufficient permissions to the folder where the file is located. By default ASP.NET apps execute under the local ASPNET (Windows XP) or NetworkService (Windows 2003) account, if impersonation is not enabled.

     

    Wednesday, November 7, 2007 10:05 PM
  • Thanks Paul

    This is actually running sharepoint with a webpart, impersonation is not being used, So network service is running but why would Network Service have access to the C: drive but not any other folder? By the way the computer that is importing the excel sheet is not on the domain. This just doesn't make sense to me.

     

    Again Thanks

    Thursday, November 8, 2007 4:15 AM
  • Where is the file with respect to the system where Sharepoint is running? Is it on the same server?

     

    I don't recall what folders the least privileged account (i.e. NetworkService) has access to. That is something you would have to check through the permissions for the account.

     

    You referred to the computer that is importing the Excel sheet. I'm assuming it's the one running the Sharepoint app?

     

    Thursday, November 8, 2007 1:27 PM
  • Tested on the SharePoint server and any location works whether it be My documents or root of C. On an external XP and Vista machine only root of the c drive is available to use, both use default security permissions for directories.

     

    The file will be located at the client machine never on the server.

     

    On the vista machine the network service has access to the directories in question (C C:\temp, My Documents). Tested with Effective Permissions.

     

    The sharepoint app is a custom built webpart that is hosted on my sharepoint site.

     

     

    Thursday, November 8, 2007 1:39 PM
  • Are you uploading the file to the Sharepoint server first? I don't see how the Sharepoint app hosting the webpart is going to be able to access the Excel file from the client machine unless the client machine is on the network and you're using a file share.

     

    Thursday, November 8, 2007 1:57 PM
  • I am not uploading the file and was a little surprised at first that i didn't need to but basically the fileupload control is needed to browse for the excel document and then once a user clicks a button it populates to a dataset that i then insert into a database.

     

    I did some research on the error message and found that the error message basically means that the file cannot be found, but then why will it work with the root of c:.

     

     

    Thursday, November 8, 2007 2:18 PM
  • If you're using the HTMLInputFile server control can you call the SaveAs method of the PostedFile property and save it to the Sharepoint server first. I don't think you're going to be able to open it directly from the client w/o encountering an invalid path. As far as I'm concerned this would be a security issue if this was possible. 

     

    I'm surprised that this works when using the root, which leads me to believe that somehow the file is actually being downloaded (perhaps temporarily) to the Sharepoint server. You may want to check the root of your Sharepoint server to see if such a file exists. The FileName property, as interpreted by the Jet OLEDB Provider, would be a file path on the system where the app (or web part) is executing, and that of course would be the Sharepoint server.

    Thursday, November 8, 2007 3:03 PM
  • Thanks for all your help.

     

    I will play around with it a little bit more and see what i can come up with, most likely saving it to the sharepoint server is what I will have to do, just can't understand why the root works when it shouldn't.

    Friday, November 9, 2007 1:13 PM