Click here to Skip to main content
11,567,926 members (46,054 online)
Rate this: bad
good
Please Sign up or sign in to vote.
See more: ASP.NET VB.NET
Hi, I have got the the following error when i'm trying to upload an excel file and store data into SQL SERVER.

The Microsoft 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.

when i run on Visual Studio everything works without any errors. Once i deploy up to IIS 7 this error came out. But I can login using the userID and password stored in the database. What should I do to resolve this error? Thanks!
 ' Read the file and convert it to Byte Array 

        Dim filePath As String = FileUpload1.PostedFile.FileName
 
        Dim filename As String = Path.GetFileName(filePath)
 
        Dim ext As String = Path.GetExtension(filename)
 
        Dim contenttype As String = String.Empty
 

 
        'Set the contenttype based on File Extension

        Select Case ext
 
            Case ".xls"
 
                contenttype = "application/vnd.ms-excel"
 
                Exit Select
 
            Case ".xlsx"
 
                contenttype = "application/vnd.ms-excel"
 
                Exit Select
 
        End Select
 

        If contenttype <> String.Empty Then
 
            Dim fs As Stream = FileUpload1.PostedFile.InputStream
 
            Dim br As New BinaryReader(fs)
 
            Dim bytes As Byte() = br.ReadBytes(fs.Length)
 
            Dim ExcelConnection As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=""Excel 12.0 Xml;HDR=Yes""")
            ExcelConnection.Open()
            Dim objCmdSelect As New OleDbCommand("Select AcadYr, Sem, Status, TUTGRP, ADM_NO, NAME, Assigned, Ward, Module1, Posting1, Start_Date1, End_Date1, Assigned2, Ward2, Module2, Posting2, Start_Date2, End_Date2, Assigned3, Ward3, Module3, Posting3, Start_Date3, End_Date3, Assigned4, Ward4, Module4, Posting4, Start_Date4, End_Date4, Assigned5, Ward5, Module5, Posting5, Start_Date5, End_Date5, Assigned6, Ward6, Module6, Posting6, Start_Date6, End_Date6, Assigned7, Ward7, Module7, Posting7, Start_Date7, End_Date7 FROM [Main$]", ExcelConnection)
            Dim objDR As OleDbDataReader
            Dim strConnString As String = System.Configuration.ConfigurationManager.ConnectionStrings("database").ConnectionString()
            Dim con As New SqlConnection(strConnString)
            Dim SQLconn As New SqlConnection(strConnString)
            SQLconn.Open()
 

            Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(SQLconn)
                bulkCopy.DestinationTableName = "studentdata"
 

                Try
                    objDR = objCmdSelect.ExecuteReader
                    bulkCopy.WriteToServer(objDR)
                    objDR.Close()
                    SQLconn.Close()
 
                Catch ex As Exception
                    MsgBox(ex.ToString)
                Finally
                    con.Close()
                End Try
 
                lblMessage.ForeColor = System.Drawing.Color.Green
 
                lblMessage.Text = "File Uploaded Successfully"
            End Using
        Else
 
            lblMessage.ForeColor = System.Drawing.Color.Red
 
            lblMessage.Text = "File format not recognised." & " Please Upload Excel formats"
 
        End If
Posted 22-Feb-11 17:10pm
lowpw229
Edited 22-Feb-11 18:16pm
v3
Comments
Prerak Patel at 22-Feb-11 23:15pm
   
where is the code?
lowpw at 23-Feb-11 0:16am
   
i have posted the codes. Tks!

1 solution

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

Solution 1

this[^] might help you to solve your problem.
  Permalink  
Comments
lowpw at 22-Feb-11 23:44pm
   
I forgot to add on actually when i run on Visual Studio everything works without any errors. Once i deploy up to IIS 7 this error came out. Something weird is i could login using the userID and password stored in the database.

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

  Print Answers RSS


Advertise | Privacy | Mobile
Web04 | 2.8.150624.2 | Last Updated 23 Feb 2011
Copyright © CodeProject, 1999-2015
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