Click here to Skip to main content
15,901,426 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello,
I'm using the following code for reading an excel file and save it to database, based on this example (Import data from Excel to SQL Server 2008)
C#
string FileName = lblFileName.Text;
string Extension = Path.GetExtension(FileName);
string FolderPath = Server.MapPath(ConfigurationManager.AppSettings["FolderPath"]);
string CommandText = "";

switch (Extension)
{
    case ".xls": //Excel 97-03
        CommandText = "Questionnaire.spx_ImportFromExcel03";
        break;
    case ".xlsx": //Excel 07
        CommandText = "Questionnaire.spx_ImportFromExcel07";
        break;
}

//Read Excel Sheet using Stored Procedure
//And import the data into Database Table
String strConnString = ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString;
SqlConnection con = new SqlConnection(strConnString);
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = CommandText;
cmd.Parameters.Add("@SheetName", SqlDbType.VarChar).Value =  ddlSheets.SelectedItem.Text;
cmd.Parameters.Add("@FilePath", SqlDbType.VarChar).Value = FolderPath + FileName;
cmd.Parameters.Add("@HDR", SqlDbType.VarChar).Value = rbHDR.SelectedItem.Text;
cmd.Parameters.Add("@TableName", SqlDbType.VarChar).Value = txtTable.Text;
cmd.Connection = con;

try
{
    con.Open();
    object count = cmd.ExecuteNonQuery();
}

catch (Exception ex)
{
    lblMessage.Text = ex.Message;
}
finally
{
    con.Close();
    con.Dispose();
}

I noticed that the error I get is because the FolderPath is not correct. When printing the FolderPath + FileName I get the following:
C:Visual StudioTestTestTestAdminFilesExcel07.xlsx
instead of
C:\Visual Studio\Test\Test\Test\Admin\Files\Excel07.xlsx


Any kind of help would be appreciated, thank you.

What I have tried:

Tried adding the path manual to see if it works
C#
string FolderPath = "C:\Visual Studio\Test\Test\Test\Admin\Files\Excel07.xlsx";
and it does.
Posted
Updated 5-Jul-18 1:39am
Comments
F-ES Sitecore 5-Jul-18 4:33am    
Not related to your problem, but this might work when developing on your local machine, but only because the client and server are the same box. When you publish this to an actual web server it isn't going to be able to access the client's files.
Chriz12 5-Jul-18 4:44am    
Yes I know, I just used the static physical path in order to verify the problem. I'm using Server.MapPath(ConfigurationManager.AppSettings["FolderPath"]) for getting the path, after uploading the files from client's side to /Files folder on server's side.
Jinto Jacob 5-Jul-18 4:45am    
have you tried using escaping the slash or adding @ with like string somePath = @"C:\blah\blih\bluh.txt";
Chriz12 5-Jul-18 5:42am    
I added @ but it doesn't make any difference.
Richard MacCutchan 5-Jul-18 5:28am    
You need to check the content of your AppSettings to see what is in FolderPath.

I believe Server.MapPath only works within the folder hierarchy of the web site.

If your client is uploading a file that is then imported into a database, you must provide a folder for that clients uploaded files WITHIN the folder hierarchy of the site. At that point, MapPath should work as you expect.

In your web site solution, add a folder called Uploads, and then add a folder in uploads for that client. Change your code to use that folder to store/retrieve new files.
 
Share this answer
 
Instead of using folderpath+Filename use Path.Combine Method[^] e.g.
C#
cmd.Parameters.Add("@FilePath", SqlDbType.VarChar).Value = 
                        Path.Combine(FolderPath,FileName);
It will put in the separators properly and only if required
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900