Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: ASP.NET VB.NET
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:
 
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:
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 8:17am
Edited 10-May-13 8:21am
v2
Comments
ryanb31 at 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 at 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
ryanb31 at 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 at 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
richcb at 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 at 10-May-13 14:08pm
   
@richcb: can you please provide me with those codes
richcb at 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

Rate this: bad
good
Please Sign up or sign in to vote.

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.
  Permalink  
v3
Comments
OsoJames at 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.
richcb at 13-May-13 10:02am
   
No problem, nice job figuring it out on your own.

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

  Print Answers RSS
0 OriginalGriff 390
1 Sergey Alexandrovich Kryukov 329
2 BillWoodruff 210
3 Afzaal Ahmad Zeeshan 204
4 CPallini 185
0 OriginalGriff 5,515
1 DamithSL 4,451
2 Maciej Los 3,902
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,175


Advertise | Privacy | Mobile
Web04 | 2.8.141216.1 | Last Updated 10 May 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100