Classic ASP File Lock on Excel Files - how to close them

I have a page where a person will upload an excel file.  The code connects to that excel file as a data source, pulls the data, then closes the source and does a bunch of other code to import the data into our system.  This all works great.

However, the person may upload 3 files within a 2-3 minute span, and 90% of the time a 2nd upload is completed, we receive errors saying the file is in use.  Browsing to the actual file itself, I see a lock file created, and I can not delete the file or manipulate it any way.

To summarize, person uploads excel, system connects to it, grabs data, then closes it.  Person uploads another file, system attempts to save the file to a folder with same name, but can't delete / overwrite existing file due to it being locked.

I do an iisreset on the server 2008 server and it clears the issue.

Here is the code:

Set objConn = Server.CreateObject("ADODB.Connection") 
objConn.Open "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=d:\filepath\file.xlsx;HDR=yes;" 
Set objCat = Server.CreateObject("ADOX.Catalog") 
Set objCat.ActiveConnection = objConn 

for each x in objCat.Tables
	strSheetName = x.Name
next
			
objConn.Close 
Set objCat = Nothing 
Set objConn = Nothing 

strSheetName = replace(strSheetName, "'", "")
strSheetName = "["&strSheetName&"]"

Set dbc = Server.CreateObject("ADODB.Connection")
dbc.open "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=d:\filepath\file.xlsx;HDR=yes"

set rs = Server.CreateObject ("adodb.RecordSet")
ssql = "SELECT * FROM "&strSheetName
rs.Open ssql, dbc, adOpenForwardOnly, adLockReadOnly

if not rs.eof then
	arrayRS = rs.getRows
end if

rs.close
set rs=nothing

dbc.close
set dbc=nothing

Open in new window

dzirkelbAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dan McFaddenSystems EngineerCommented:
You could try to specifically close the ActiveConnection on the ADOX object.

reference:  https://msdn.microsoft.com/en-us/library/windows/desktop/ms681562(v=vs.85).aspx

I am not so convinced that this is the issue though...

In your code, after the "for each" ends but before the objCat statement, insert this:

Set objCat.ActiveConnection = Nothing

Open in new window


Also, just a comment on the organization of the code:

You have a ADODB.Connection object instantiated, no need to close it only to open the same connection with a different name.  You're doing double work.

objConn and dbc are the same code with different names, you should reuse the objConn and get rid of the dbc object.

1. open objConn
2. instantiate objCat
3. activate the objCat
3. for each thru the object info
4. nothing the objCat
5. work on the strings
6. instantiate the RecordSet
7. open the RecordSet with the objConn object
8. loop thru the RS
9. close objects
9a. close rs
9b. close objConn
10. nothing both rs & objconn

Dan
dzirkelbAuthor Commented:
I tried the following:

objConn.Close
Set objCat.ActiveConnection = Nothing
Set objCat = Nothing 
Set objConn = Nothing 

Open in new window


And I receive the same error.  I then tried:

objConn.Close
objCat.close
Set objCat.ActiveConnection = Nothing
Set objCat = Nothing 
Set objConn = Nothing 

Open in new window


And I received the following error:

Object doesn't support this property or method: 'objCat.close'

the error I receive is on my .aspx page, and it happens when trying to save the file itself:

The process cannot access the file 'location\file.xlsx' because it is being used by another process.

I tried adding some delete code:

Set objFSO = Server.CreateObject("Scripting.FileSystemObject")
objFSO.DeleteFile("location\.xlsx")
Set objFSO = Nothing

Open in new window


and I received the following error:

Permission denied

I get the permission error because it is locked.
Dan McFaddenSystems EngineerCommented:
Have you tried modifying the permissions on the directory where this file is being uploaded/stored?

As a test, I would grant everyone modify on this location and then retry the operation.

And yes, the ADOX.Catalog object does not support a close method.  But for ADO objects, it has been a long standing practice to set the instantiated objects to Nothing, just to make sure they were destroyed.  Whether is works or not has been a discussion for a while.  But in MS's documentation, you will find many recommendations to set the object to nothing.

Dan
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

dzirkelbAuthor Commented:
I already set it to nothing though:

Set objConn = Server.CreateObject("ADODB.Connection") 
objConn.Open "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=d:\inetpub\webecis\images\Trane.xlsx;HDR=yes;" 
Set objCat = Server.CreateObject("ADOX.Catalog") 
Set objCat.ActiveConnection = objConn 

for each x in objCat.Tables
	strSheetName = x.Name
next
			
objConn.Close
Set objCat = Nothing 
Set objConn = Nothing 

Open in new window


And the permission error only happens due to the lock, if I tell it to delete any other file in the same directory, it deletes fine.
dzirkelbAuthor Commented:
Anyone have any ideas what is causing the file to not unlock?
Dan McFaddenSystems EngineerCommented:
The AppPool is holding a lock because it hasn't been told to release the lock.  Meaning that somewhere in the code that is executing inside the AppPool, is a missing close or clear on the process of saving the file.

Can you post the code that uploads and saves the file?

Dan
dzirkelbAuthor Commented:
Here is the upload code:

<script runat="server">
	Sub Button_Click(ByVal s As Object, ByVal e As EventArgs)
		inpFileUp.PostedFile.SaveAs("File.xlsx")
		Response.Redirect("Process.asp")
	End Sub
</script>

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
 	<title>Trane Pueblo Order Entry Upload</title>
	<link rel="stylesheet" type="text/css" href="xxx.css">
	<script type="text/javascript" src="yyy.js"></script>	
</head>
<body>
<table class="body"><tr><td class="TableData">
	<h1 class="header">Trane Pueblo Order Entry Upload</h1>
	
	1) Highlight rows 1-12, right click and select delete.  This will remove rows 1-12, and the new row 1 should include the column headers.
	<br>2) Save the file anywhere you wish named anything you like.
	<br>3) Click browse on this web page to find the file you saved and upload into our system.
	<form enctype="multipart/form-data" runat="server">
		<b>File Upload</b>
		<input id="inpFileUp" type="file" runat="server" size="20" />
		<asp:Button Text="Upload File and Update System" OnClick="Button_Click" runat="server" />
	</form>
</td></tr></table>
</body>
</html>

Open in new window

Dan McFaddenSystems EngineerCommented:
Have you tried to use the full path to the file in the PostedFile.SaveAs call, instead of just the file name?

Dan
dzirkelbAuthor Commented:
Ya, I've used this code:

inpFileUp.PostedFile.SaveAs("d:\location\file.xlsx")

same result.
Dan McFaddenSystems EngineerCommented:
Is the "D:\location" contained within the scope of the web content directory as defined in IIS?

Dan
dzirkelbAuthor Commented:
The IIS scope is d:\inetpub\webecis
The place I am saving it is d:\inetpub\webecis\images\
dzirkelbAuthor Commented:
Any other ideas?
Dan McFaddenSystems EngineerCommented:
When there is a file locking issue, have you tried to determine what in the AppPool is holding the file locked?

If you are familiar with Process Explorer from SysInternals (https://technet.microsoft.com/en-us/sysinternals/bb896653.aspx), you should be able to look into the process and find the thread that is holding the lock.  That may give you a better idea of where in your code to look.

Since the obvious solutions are not working, we'll need to do a little deeper troubleshooting.

Dan
dzirkelbAuthor Commented:
Thanks, I'll check that out soon, on vacation today and I have to prepare for a couple audits next week, so I'll respond as soon as I can.
dzirkelbAuthor Commented:
From what I can tell the file lock is under the process w3wp.exe, the specific one for my app pool / iis web site, which makes sense.

I'm able to see the file in the bottom pane of the Process Explorer, and it sits there.  While it is sitting, I can't upload the file again, when it is removed, then I can upload fine.  I can kill the process and then upload.

What I'm not able to tell is why it is locked, or not being released.
dzirkelbAuthor Commented:
Any ideas?  This continues to happen on a consistent basis.
Dan McFaddenSystems EngineerCommented:
OK, so after a little bit more digging, I am of the opinion that is may have to do with permissions on the location of the saved file.

Are you using the default configuration on the AppPool for the Application Pool Identity?

Here's what I think may be happening; permissions (ACLs) at the NTFS level are being inherited by the saved filed.  But the LOCK file is not getting the ACL that allows the AppPool Identity to delete the file.

Of course, recycling the AppPool will release the lock.  Which is the temp solution.

You code ( "inpFileUp.PostedFile.SaveAs" is VB.NET not Classic ASP ) is fine but I would recommend a test of reconfiguring the AppPool Identity to a domain account that has been granted Modify permissions to the Share & NTFS levels.  Then test the functionality.

AppPool Identity link:  http://www.iis.net/learn/manage/configuring-security/application-pool-identities

Dan
dzirkelbAuthor Commented:
I went into the application pool for my test account and assigned a custom account, the domain administrator for our network, and it still fails.  I also tried my own account, as I see many other application pools have my username as the identity, still no go.  I finally tried the local computer administrator account and still nothing.

I checked the permissions of the folder in question and it is wide open, even including everyone with full access.
Dan McFaddenSystems EngineerCommented:
Have you tried to generate a random filename during the save process?  How big are these files?

Dan

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dzirkelbAuthor Commented:
I could generate a random name file; however, then they would fill up the directory. That is a decent work around though to this problem.

The files are not large, generally containing around 10-15 rows of data on an excel sheet.
Dan McFaddenSystems EngineerCommented:
You could then run a nightly task to recycle the AppPool and then delete the daily files.

Dan
dzirkelbAuthor Commented:
What would be the command to recycle the app pool in a command prompt?  Or, how would I recycle it nightly?

I'm going to work on assigning a random name now, or some sort of counter system.
Dan McFaddenSystems EngineerCommented:
If you want to recycle an AppPool at the command line, the command is:

appcmd recycle apppool /apppool.name: <PutYourAppPoolNameHere>

Open in new window


If your AppPool name has spaces in it, enclose the name in double quotes.

Your nightly scheduled job should do the following tasks:

1. Recycle the necessary AppPool
2. Delete the temp files created since the last job executed

Dan
dzirkelbAuthor Commented:
This solution will work for my situation, thanks for the help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.