Invalid File path error in asp.net

Question

I use a simple FileUpload control to select a excel file, retrieve the data and store it to db. When i try to upload after selecting the file i get this error. But file path is correct.

"FilePath" is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides

Code used is:

  <add key="OleDbConnection" value="Provider=Microsoft.Jet.OLEDB.4.0;Data Source= FilePath ;Extended Properties=&quot;Excel 8.0;HDR=Yes;IMEX=1&quot;"/>

     string OleDbConnection = 
         ConfigurationManager.AppSettings["OleDbConnection"].Replace("FilePath",
             fileUpload.PostedFile.FileName).Trim();

       Excel.ApplicationClass xlApp = new Excel.ApplicationClass();
       Excel.Workbooks xlWorkBooks = (Excel.Workbooks)xlApp.Workbooks;
    Excel.Workbook wb = xlWorkBooks._Open(fileUpload.PostedFile.FileName, Type.Missing, false, Type.Missing, "", "", true, Excel.XlPlatform.xlWindows, "\t", true, false, Type.Missing, true);

        string strSheetName = ((Excel.Worksheet)wb.Sheets[1]).Name.ToString();
        xlWorkBooks.Close();
        xlApp.Quit();

        oledbCommand = new OleDbCommand();
        oledbAdapter = new OleDbDataAdapter();

        DataSet dsExcellData = new DataSet();
        oledbConnection = new OleDbConnection(OleDbConnection); 
        oledbConnection.Open();

        oledbCommand.Connection = oledbConnection;
        oledbCommand.CommandText = "Select * from [" + strSheetName + "$]"; 

        oledbAdapter.SelectCommand = oledbCommand;
        oledbAdapter.Fill(dsExcellData);

        return dsExcellData;

Answer 1

Did you try wrapping the file path with a Server.MapPath(FileName)?

What does your connection string look like if you Reponse.Write it to the page?

Answer 2

Change the line to

string OleDbConnection = ConfigurationManager.AppSettings["OleDbConnection"].ToString().Replace("FilePath", Server.MapPath(fileUpload.PostedFile.FileName)).Trim();

You prob have the relative path, you need the physical path with Server.MapPath

Try the following code, Ive successfully run a query on a sheet in a hosted enviroment using your idea above.

private OleDbConnectionStringBuilder BuildXLConnString(string DSource)
{
    OleDbConnectionStringBuilder connBuild = new OleDbConnectionStringBuilder();
    connBuild.Provider = "Microsoft.Jet.OLEDB.4.0";
    connBuild.DataSource = DSource;
    connBuild.Add("Extended Properties", "Excel 8.0;IMEX=1;HDR=Yes;");
    return connBuild;
}

Answer 3

You're missing "using" blocks:

var xlApp = new Excel.ApplicationClass();
var xlWorkBooks = (Excel.Workbooks) xlApp.Workbooks;
Excel.Workbook wb = xlWorkBooks._Open(
    fileUpload.PostedFile.FileName, Type.Missing, false,
    Type.Missing, "", "", true, Excel.XlPlatform.xlWindows, "\t",
    true, false, Type.Missing, true);

string strSheetName =
    ((Excel.Worksheet) wb.Sheets[1]).Name.ToString();
xlWorkBooks.Close();
xlApp.Quit();

var dsExcellData = new DataSet();

var oleDbConnectionString =
    ConfigurationManager.AppSettings["OleDbConnection"].Replace(
        "FilePath", fileUpload.PostedFile.FileName).Trim();
var commandText = "Select * from [" + strSheetName + "$]";
using (
    var oledbConnection = new OleDbConnection(oleDbConnectionString)
    )
{
    oledbConnection.Open();
    using (var oledbCommand = new OleDbCommand())
    {
        oledbCommand.Connection = oledbConnection;
        oledbCommand.CommandText = commandText;
        {
            using (var oledbAdapter = new OleDbDataAdapter())
            {
                oledbAdapter.SelectCommand = oledbCommand;
                oledbAdapter.Fill(dsExcellData);
                return dsExcellData;
            }
        }
    }
}