0

I have a requirement to upload excel data into a SQL Server 2005 table. Initially I copied Excel data to a temp table and based on condition I have updated and inserted records into the SQL Server table.

Everything works fine in my local system. Same program I moved to quality server there I faced this error

The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.

Then we installed 'Microsoft.ACE.OLEDB.12.0 provider on quality server. Now this error appearing

The Microsoft Office Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data."

Kindly help me out to solve this issue.

SqlConnection con = new SqlConnection();
con = SQLManager.openSQLConnection();

string path = FileUpload1.PostedFile.FileName;
string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0 Xml;Persist Security Info=False";
//Create Connection to Excel work book
OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
OleDbCommand cmd = new OleDbCommand("Select [CM_CODE],[CM_NAME],[CM_ADD1],[CM_ADD2],[CM_ADD3],[CM_ADD4],[CM_CITY],[CM_PHONE],[CM_CG_CODE],[CM_CO_CODE],[CM_EXPIRY_DATE],[CM_PINCODE],[CM_EMAIL] from [Sheet1$]", excelConnection);

excelConnection.Open();
OleDbDataReader dReader;

dReader = cmd.ExecuteReader();

Regards, Sathya

Improve this question
6
  • Does the server process have access to the file path or can it even see it? Commented Aug 27, 2013 at 4:48
  • I am accessing server system through URL which consists of my application. Excel sheet upload is from my system through my application. Commented Aug 27, 2013 at 4:57
  • If you upload an excel file via your website, IIS may still still be processing the upload of the file while your process tries to access the file and therefore giving you the error you see. Commented Aug 27, 2013 at 5:04
  • Thanks @Nathan .. but still i didn't get u? can u pls elaborate and give me the solution to solve this issue. Commented Aug 27, 2013 at 5:24
  • From your current error, it appears that something is already accessing the file that has been uploaded. A couple of questions, 1.How does the Excel File get uploaded to the server, 2. What is the application that is running your code? Is it a website, windows service, winforms app? What happens if you manually copy an excel spreadsheet to the same location and then process it. Does it work then? Commented Aug 27, 2013 at 6:12

2 Answers 2

Reset to default
2

This is most likely a file permissions issue. Does the account that the program runs under have folder level permission to read files from the file location.

Find out which account the program runs under and then add appropriate access permissions to the folder where you upload your excel files.

If it's IIS then whichever account your app pool runs under needs write access to the folder that you upload to. Permissions can usually only be set by web server admin.

Wing

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

Finally i found out the solution. I have created one folder under my application folder in Quality server. Uploaded excel sheets wil get stored under this new folder. and that path i am referring while updating my SQL server database. Everything works fine now... Thank u all for your support

Regards, Sathya

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.