SqlBulkCopy & problem: The process cannot access the file '' because it is being used by another process.

I use the SQLBulkCopy class to insert data from a excel file to SQL Server table.

            Dim OleDbConn As OleDbConnection = New OleDbConnection(sExcelConnectionString)
            Dim OleDbCmd As OleDbCommand = New OleDbCommand(("SELECT * FROM " & sWorkbook), OleDbConn)

            OleDbConn.Open()
            Dim dr As OleDbDataReader = OleDbCmd.ExecuteReader()
            Dim bulkCopy As SqlBulkCopy = New SqlBulkCopy(sSqlConnectionString)
            bulkCopy.DestinationTableName = sSQLTable
            bulkCopy.WriteToServer(dr)
            OleDbConn.Close()

After a successful insertion of data, I delete the excel file from the server using the "File.Delete(path)" command. Exceptions do arise at certain situations.

Situation (1) : Exception after a successful insertion of data:

I got the exception "The process cannot access the file '<path/filename.xls>' because it is being used by another process". Searching this issue, I found that it can be avoided by calling the excel file using a "StreamWriter" object, doing some read() and write(), and then closing this file explicitly using the "StreamWriter" object. This worked well.

Situation (2) : Exception after a unsuccessful insertion of data:

Due to a datatype mismatch in the database, the insertion using the SqlBulkCopy can fail. It gives message something like "The given value of type String from the data source cannot be converted to type nchar of the specified target column."

When this situation arises, the user understands that the data in the excel file is not correct. Now, the next step is to delete this unwanted excel file. Now "File.Delete(path)" - gives the same exception that "The process cannot access the file '<path/filename.xls>' because it is being used by another process". I used the StreamWriter (like above), StreamReader, FileStream ....everything hoping to overcome this exception. But it still shows the same error or exception.

If anyone has come across this situation, pls do help me in solving this Situation (2) mentioned above.

Thanks

0
terminator
7/13/2007 8:12:59 AM
asp.net.visual-studio-2005 7760 articles. 0 followers. Follow

8 Replies
1483 Views

Similar Articles

[PageSpeed] 16
Get it on Google Play
Get it on Apple App Store

terminator:
"The process cannot access the file '<path/filename.xls>' because it is being used by another process".

pls make sure that whatever dataset, datareader, connection, command, streamreader, streamwriter, filestream etc., which you have used to access the file, must be CLOSED or DISPOSED before you try to delete the File; specially streams and connection.

hope it helps./.


Thanx,
[KaushaL] || BloG || Profile || Microsoft MVP

"I would love to change the world, but they won’t give me the source code"


Don't forget to click "Mark as Answer" on the post that helped you.
This credits that member, earns you a point and mark your thread as Resolved for the sake of Future Readers.
0
kaushalparik27
7/13/2007 8:27:48 AM

Thanks for that fast reply.

In my case, I dont really need to use any of these objects (StreamWriter, StreamReader or anything). In fact, I am only using "SqlBulkCopy" which raises that exception "The process cannot access the file '' because it is being used by another process." in Situation (2).

I used StreamWriter class (declared, instantiated, read(), write() and finally closed) inorder to overcome this exception in Situation (1) above. BUT that did not work in Situation (2).

I need a solution for that...

Thanks again.

0
terminator
7/13/2007 9:04:04 AM

can you post your complete code pls?


Thanx,
[KaushaL] || BloG || Profile || Microsoft MVP

"I would love to change the world, but they won’t give me the source code"


Don't forget to click "Mark as Answer" on the post that helped you.
This credits that member, earns you a point and mark your thread as Resolved for the sake of Future Readers.
0
kaushalparik27
7/13/2007 9:37:37 AM

sorry for the delay....was not around during the weekend...

this is the code: 

Protected Sub btn_InsertData_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btn_InsertData.Click
         Try
            ' Declarations for SQLBulkCopy
            Dim sSQLTable As String = "SqlServer_tablename"
            Dim sExcelFileName As String = ddlFileName.SelectedValue.ToString()
            Dim sWorkbook As String = "[" + Session("sheetname") + "$]"

            Dim sExcelConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Session("currentpath") & sExcelFileName & ";Extended Properties=""Excel 8.0;HDR=NO;"""
            Dim sSqlConnectionString As String = WebConfigurationManager.ConnectionStrings("LocalSqlServer").ToString


            'Commands to insert data to SQL table from excel file
            Dim OleDbConn As OleDbConnection = New OleDbConnection(sExcelConnectionString)
            Dim OleDbCmd As OleDbCommand = New OleDbCommand(("SELECT * FROM " & sWorkbook), OleDbConn)

            OleDbConn.Open()
            Dim dr As OleDbDataReader = OleDbCmd.ExecuteReader()
            Dim bulkCopy As SqlBulkCopy = New SqlBulkCopy(sSqlConnectionString)
            bulkCopy.DestinationTableName = sSQLTable
            bulkCopy.WriteToServer(dr)
            OleDbConn.Close()
            
            Dim path As String = Session("currentpath") + sExcelFileName
	    
            ''The below block is to overcome a bug: 'File is in USE'
            Dim sw As StreamWriter = New StreamWriter(Path)
            sw.Write("write garbage ")
            sw.WriteLine("all garbage")
            sw.Close()
            sw = Nothing
            ''bug handling ends here...
	    	    
            '' THE ABOVE STREAMWRITER CODE HELPS TO DELETE THE EXCEL FILE, ELSE IT GIVES THE EXCEPTION. The process cannot access the file 'path/filename.xls' because it is being used by another process. ''

            File.Delete(path)
            Label3.Text = "Excel File Deleted"

        Catch ex As Exception
            Label3.Text = "Exception raised : " + ex.Message
        
        End Try
    End Sub

   
    Protected Sub btnDeleteFile_Click(ByVal sender As Object, ByVal e As System.EventArgs)

        'To manually delete the excel file, if it was not deleted by the Sub function above.

        '' When the above Sub btn_InsertData_Click fails, ie no excel data have been inserted due to datatype mismatch, 
        '' and when I try to delete the file using this event, I get the exception: ''
        '' The process cannot access the file 'path/filename.xls' because it is being used by another process. ''

        Try
            If Not ddlDeleteFile.SelectedValue = "" Then
                Dim path As String = Session("currentpath") + ddlDeleteFile.SelectedValue

                ''The below block is to overcome a bug: 'File is in USE'
                Dim sw As StreamWriter = New StreamWriter(path)
                sw.Write("write garbage ")
                sw.WriteLine("all garbage")
                sw.Close()
                sw = Nothing
                ''bug handling ends here...

                '' THE ABOVE STREAMWRITER ALSO DIDN'T HELP ''

                File.Delete(path)
                Label1.Text = "File deleted"

            Else
                Label1.Text = "No files exists"
            End If

        Catch ex As Exception
            Label1.Text = ex.Message
        End Try

    End Sub
 thanks again....

 

0
terminator
7/16/2007 7:57:09 AM

Hi terminator,

For the second situation, please try to move “New SqlBulkCopy” to Using, End Using section like the following.

Using bulkCopy As New SqlBulkCopy(sSqlConnectionString)
……….
End Using

Just for your reference, here is a thread with the similar issue.

FileStream doesn't release resources
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=656321&SiteID=1

 


Sincerely,
Benson Yu
Microsoft Online Community Support

Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.
0
Benson
7/17/2007 2:59:07 AM

terminator:

OleDbConn.Open()
Dim dr As OleDbDataReader = OleDbCmd.ExecuteReader()
Dim
bulkCopy As SqlBulkCopy = New SqlBulkCopy(sSqlConnectionString)
bulkCopy.DestinationTableName = sSQLTable
bulkCopy.WriteToServer(dr)
OleDbConn.Close()
 

i think you have to close bulkCopy too, as.

bulkCopy.Close();


Thanx,
[KaushaL] || BloG || Profile || Microsoft MVP

"I would love to change the world, but they won’t give me the source code"


Don't forget to click "Mark as Answer" on the post that helped you.
This credits that member, earns you a point and mark your thread as Resolved for the sake of Future Readers.
0
kaushalparik27
7/17/2007 5:40:21 AM

thanks benson & kaushik. I have tried both without any success...

Just a thought, pls correct me if I am wrong.

At this point of code: bulkCopy.WriteToServer(dr) , the program jumps into the exception block, and terminates, showing the exception message. So will this "End Using" or "bukCopy.Close()" really execute ??

Is there any separate method or function in ASP.NET, that release all the resources it holds??

thanks.

0
terminator
7/18/2007 3:42:31 AM

This also gets around the "The process cannot access teh file because it is being used by another process. "

FileStream fs = new FileStream("c:\inetpub\logs\logfile.log", FileMode.Open, FileAccess.Read, FileShare.ReadWrite);

StreamReader streamLogFile = new StreamReader(fs);

Found this on here.

http://social.msdn.microsoft.com/Forums/en-US/netfxbcl/thread/d693d050-2a05-4ff6-9422-bdcc051f58bb/

0
TravisUdd
12/4/2008 5:23:57 PM
Reply:

Similar Artilces:

Cannot copy assembly 'DotNetNuke' to file '<assemly>'. The process cannot access the file because it is being used by another process.
I am in the process of creating a new module and I keep running into this problem. This happens every time I try to build the project. SOMETIMES I can get it working again by deleting the previously generated assemblies and reopening the project, but that doesn't always work and I'd like for the problem not to be there in the first place. My new module project is located at \DotNetNuke\DesktopModules\NewModule The build path for the assembly is set to ..\..\bin which is \DotNetNuke\Bin I am also using a project reference rather than an assembly reference so that shouldn't be the p...

The process cannot access the file '.....' because it's being used by another process
In VB6.0 I was using API calls for checking that a process has finished and then continue with other steps:       var_process_number = Shell("wzunzip.exe -o " & """" & fld_ZIP_file_name & """" & " " & """" & fld_import_path & """", vbMinimizedNoFocus)       'wait until the uncompressing is over         var_process_handle = OpenProcess(&H100000, True, var_process_number)         WaitForSingleObject var_process_handle, -1         CloseHandle var_process_handle In ASP....

Could not write to output file '...\obj\Debug\abc.dll" -- 'The process cannot access the file because it is being used by another process'
I use VS2003. Recently I keep getting this error when building the solution. Any idea how to fix it?? THANKS!  Could not write to output file '...\obj\Debug\abc.dll" -- 'The process cannot access the file because it is being used by another process' Close any other projects that link to this dll (i.e., any extra Visual Studio instances).  Also, try closing the app-- If you are running/debugging the project that references this dll, then you may be using that dll.  ---------------------------------------MCP - Web Based Client Development .NET 2.0 Thanks f...

The process cannot access the file '' because it is being used by another process.
 Hai guys..Write now i am designing an event calendar . I am having an error message while trying to delete an event with images.. The event detail page is view with event details and the image under the event (i.e) photogallery is shown. while i am trying to delete the event with image it show an error message like "The process cannot access the file '<filepathname>' because it is being used by another process." . I have listed my code here.. 1 protected void gvAdminEventDetail_RowDeleting(object sender, GridViewDeleteEventArgs e) 2 { 3 ...

(urgent) The process cannot access the file '[filename]' because it is being used by another process.
For some reason my users are getting the following error in production: "System.IO.IOException: The process cannot access the file 'D:\[omitted]\TestingCenter\Xml\TestPlans\dbeckett\20071204_4' because it is being used by another process." This is occurring in my production environment, but did not occur in development or staging environments. Here is my code:string path = ConfigurationManager.AppSettings["xmlPath"];_modules = new Modules(); ClearLineItems();if (Directory.Exists(path + CurrentUser.Username)) { path += CurrentUser.Username + "\\";...

IOException: The process cannot access the file 'filename' because it is being used by another process
Hi everyone,I'm having some difficulty with my File upload and File delete processes.For the Upload, I do like:sTempName = path + FileUpload1.FileName;FileUpload1.PostedFile.SaveAs(sTempName); //sTempName = C:\Inetpub\vhosts\myfile.jpgIt works fine on my local machine, but once I upload it to my remote server, I sometimes (about 1 in 8 times) hit this exception:System.IO.IOException: The process cannot access the file 'C:\Inetpub\vhosts\myfile.jpg' because it is being used by another process.   at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)   at Sy...

System.IO.IOException: The process cannot access the file '' because it is being used by another process.
I am getting the above mentioned error. My code is as below and the stack trace also follows. I am not trying to open the file or copy or move, i just want to attach it to an email. Dim _today As Date Dim _dayNumber As Integer Dim _wcDayNumber As Integer Dim _stepDownVal As Integer Dim _negated As Integer Dim _wcDay As Date Dim wc As String Dim _filename As String Dim _foldername As String _today = Date.Today _dayNumber = Date.Today.DayOfWeek _wcDayNumber = 1 _stepDown...

System.IO.IOException: The process cannot access the file 'xxxx.pdf' because it is being used by another process.
I have a basic vb.net form that collects some data, allows users to add attachments, and when they click submit, generates an email that includes the data and the attachments that were uploaded to the server. To keep my server clean, after the email is sent, I want to remove the attachments... However, I keep getting an error that says it can't access the file because it is being used by another process.  I can either send the email or delete the file, but can't get the two to work together... Any suggestions? my code behind...        Protected S...

(VB) The process cannot access the file 'C:something/something.jpg' because it is being used by another process.
I found articles about this error but none mentioned the situation where the very first action the code takes (or so I believe) shuts down the program as if something else had previously happened to tie up the file. The code below is very short, two controls on the ASPX page and only two lines of code behind.  The first one causes the error: PictureUploadControl.SaveAs(Server.MapPath("~/images/ORIGINAL") & PictureUploadControl.FileName) Please find, if possible, in the code below what error is causing a denial of file access.  Thank you for your help. The an...

PLease help getting error as The process cannot access the file 'D:\Inventracksys\Inventorytracking Vendorwisereport_1.emf' because it is being used by another process
Dear All, I am making web application using Asp.net C#(Visual Studio2005). And Sql server 2005 as a back End  I generated local mode report but as there was no printing option available . I assign printing feature to button click by exporting the data to EMF format and print Now when I run it through IDE VS2005 then it does not show any error. But when I run in through IIS in intranet it showing me this error   The process cannot access the file 'D:\Inventracksys\Inventorytracking Vendorwisereport_1.emf' because it is being used by another process Where I am going wrong pl...

PLease help getting error as The process cannot access the file 'D:\Inventracksys\Inventorytracking Vendorwisereport_1.emf' because it is being used by another process #2
Dear All, I am making web application using Asp.net C#(Visual Studio2005). And Sql server 2005 as a back End  I generated local mode report but as there was no printing option available . I assign printing feature to button click by exporting the data to EMF format and print Now when I run it through IDE VS2005 then it does not show any error. But when I run in through IIS in intranet it showing me this error   The process cannot access the file 'D:\Inventracksys\Inventorytracking Vendorwisereport_1.emf' because it is being used by another process Where I am going wrong pl...

Process can't access file because it is being used by another process
Hi I've to extract a zip file and delete that zip file. When i extract zip file, the file is extracted successfully, but when i try to delete the file, it throws the Exception "Process can't access file because it is being used by another process". Can some one solve my problem. Thanks in advance.ZIA UR REHMAN how are you extracting the file? if you're using a library, remember to close the zipfile before you try deleting it. yeah this was the problems... thanks alot for the help. i really appreciate. ThaksZIA UR REHMAN...

Can not access file 'filename' because it is being used by another process
I'm getting the following error on my web servers occassionally and the site is inaccessible until I reboot. It only happens occassionally and I can't force the duplication of it. The file is one of the DLLs that the app is trying to access. Is anyone familiar with this? Server Error in '/' Application. -------------------------------------------------------------------------------- Configuration Error Description: An error occurred during the processing of a configuration file required to service this request. Please review the specific error details below and modify you...

! Unable to write to output file 'C:\Documents and Settings\Administrator\VSWebCache\ELEAD\DotNetNuke\obj\Debug\DotNetNuke.dll': The process cannot access the file because it is being used by another
I have trouble with DotNetNuke version 3.0.11 I put folder DotNetNuke in C:\Inetpub\wwwroot When I build its solution, I usually get this error: " Unable to write to output file 'C:\Documents and Settings\Administrator\VSWebCache\ELEAD\DotNetNuke\obj\Debug\DotNetNuke.dll': The process cannot access the file because it is being used by another process. Unable to write to output file 'C:\Documents and Settings\Administrator\VSWebCache\ELEAD\DotNetNuke\obj\Debug\DotNetNuke.pdb': Access is denied.  " I can't repair it. If I want to continue, I must close all solution, and delete fold...

Web resources about - SqlBulkCopy & problem: The process cannot access the file '' because it is being used by another process. - asp.net.visual-studio-2005

SqlBulkCopy for Generic List<T>
... and it was taking a very long time (I gave up after 5 minutes). I recalled this post a while back from Mikael Eliasson demonstrating SqlBulkCopy ...

Languages -> C# Articles, Tutorials, Examples: ASP Alliance
.NET,ASP,ASP.NET,SQL,XML,HTML,ADO,ADO.NET,JavaScript,JScript,CSharp,VBScript and VB tutorials from AspAlliance.

Latest Updates
Latest Updates - Free source code and tutorials for Software developers and Architects.; Updated: 15 Feb 2013

Archive
Archive - Infinite Codex

Archive
Articles Blog Videos Archive The Good, The Bad, and Everything In Between May 2014 Building Multiple Filters with Lo-Dash and AngularJS Compile, ...

Home - Technical Taco
Technical Taco I'm just some guy, you know? View otac0n on GitHub 21 Nov 2012 » Range header, I choose you (for pagination)! 03 Sep 2011 » Getting ...


Learn SQL Server programming - developer Fusion
Get up to speed with the latest on SQL Server and learn SQL Server programming with our in-depth articles, tips and tricks and sample code. Get ...

Latest Updates
Latest Updates - Free source code and tutorials for Software developers and Architects.; Updated: 15 Feb 2013

Top Ranked Articles
Top Ranked Articles - Free source code and tutorials for Software developers and Architects.; Updated: 23 Feb 2013

Resources last updated: 12/18/2015 1:45:17 PM