Error while uploading MS excel file into my database in SQL server 2005

Question

4.00/5 (1 vote)

See more:

when i specify the file directory in the code direct, it uploads into the specified table in my database correctly,the code below is saving with the file address placed directly in the code:

VB

Dim strConnection As String = "Data Source=Jamie-PC\SQLSERVER2005; initial catalog=StudentDB; persist security info=true;user id=sa;password=nash"
            
            Dim FullPath As String = "C:\Users\Narsh\Desktop\Excel Files\english pasco\english.xls"
            Dim MyPath As String = System.IO.Path.GetDirectoryName(FullPath)
            Dim myFilename As String = System.IO.Path.GetFileName(FullPath)
            
            Dim excelConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FullPath + ";Extended Properties='Excel 8.0;';"
            Dim excelConnection As New OleDbConnection(excelConnectionString)
            Dim cmd As New OleDbCommand("Select * from [Sheet1$]", excelConnection)
            excelConnection.Open()
            Dim dReader As OleDbDataReader
            dReader = cmd.ExecuteReader()
            Dim sqlBulk As New SqlBulkCopy(strConnection)
            sqlBulk.DestinationTableName = "English2000"
            sqlBulk.WriteToServer(dReader)
            excelConnection.Close()

But when i use the <asp:FileUpload ID="fileuploadExcel" runat="server"/> to upload the file into the fileupload control before uploading it into the database, it gives me this error below:

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

The code below is giving the error:

VB

Dim strConnection As String = "Data Source=Jamie-PC\SQLSERVER2005; initial catalog=StudentDB; persist security info=true;user id=sa;password=nash"
            
            Dim path As String = fileuploadExcel.PostedFile.FileName
            
            Dim excelConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties='Excel 8.0;';"
            Dim excelConnection As New OleDbConnection(excelConnectionString)
            Dim cmd As New OleDbCommand("Select * from [Sheet1$]", excelConnection)
            excelConnection.Open()
            Dim dReader As OleDbDataReader
            dReader = cmd.ExecuteReader()
            Dim sqlBulk As New SqlBulkCopy(strConnection)
            sqlBulk.DestinationTableName = "English2000"
            sqlBulk.WriteToServer(dReader)
            excelConnection.Close()

Please i need urgent help on why? i am using excel 2003-2007 format

Posted 10-May-13 7:17am

OsoJames

Updated 10-May-13 7:21am

v2

Add a Solution

Comments

ZurdoDev 10-May-13 13:36pm

I believe it is because IIS still has a lock on the file. You need to let the thread finish and then try accessing it.

OsoJames 10-May-13 13:51pm

And can the lock me removed or how do i know that the thread has finished because the program halts because of the error

ZurdoDev 10-May-13 13:55pm

I'm not sure entirely. Does Jet have a way to say ignore file locks? If not, the thread finishes when the upload is done and control returns back to the JS. I think the upload control has an after upload event at which point you could call back in to a webservice. I think you'll just have to find a way around the lock.

OsoJames 10-May-13 13:58pm

hahaa, sorry i forgot to tell you that i'm new to ASP.net, so don't really know what Jet can do. I only know that it's different from ACE.OLEDB

Richard C Bishop 10-May-13 14:01pm

If you are using Exel 2003 and 2007, you will have to have two connection strings. One with ACE(Excel 2003) and on with Jet(Excel 2007). You will need to check the file extension and apply the connection accordingly.

OsoJames 10-May-13 14:08pm

@richcb: can you please provide me with those codes

Richard C Bishop 10-May-13 14:19pm

See my solution. You have the first connection string, just add the one I gave you with some functionality that checks the file extension and there you go.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Richard C Bishop · Accepted Answer · 2013-05-10T08:18:00

Solution 1

Here is the other connection string you will want to use for .xlsx files:

"Provider=Microsoft.Ace.Oledb.12.0;Data Source=" + filepath + ";Extended Properties=" + "\"" + "Excel 12.0;" + "\"";

You already have the connection string for .xls files. Just check the extension as I said above and then apply the appropriate connection string.

Also, be sure you are using the full file path and not just the name in your connection string.

Posted 10-May-13 8:18am

Richard C Bishop

Updated 10-May-13 8:25am

v3

Comments

OsoJames 12-May-13 20:07pm

In a way, we swayed from the actual question i asked but thanks for the help all the same, i have found a way to go about the problem i was experiencing.
The fileupload control does not show the whole file directory address, but just the filename and its extension. That is how it has been programmed for security reasons.

Richard C Bishop 13-May-13 10:02am

No problem, nice job figuring it out on your own.