0

Kind of a wordy title but I have a classic ASP application I am trying to write where a user uploads an Excel spreadsheet and then I take that spreadsheet and import the data into SQL.

I have everything working great but the one thing I'm running into is that after I open the spreadsheet using ODBC and close all the objects that reference it, if I try to delete the file, I get a permission denied error.

If I try to sweep the temp directory before uploading and I run into a previously uploaded file (say within the last two minutes), I get the permission denied error.

If I wait a minute or two, it seems like whatever lock was put on the file is released and I can delete it.

Here's some code:

sPath = Server.MapPath("/_temp/") & "\"
sFileName = Request.QueryString("filename")

Set objFile = Server.CreateObject("Scripting.FileSystemObject")

If objFile.FileExists(sPath & sFileName) Then 
    objFile.DeleteFile sPath & sFileName, True
End If

'Upload file occurs here

sConnectionString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & sPath & sFileName & ";"
adLockOptimistic = 3

sSQL = "SELECT * "
sSQL = sSQL & "FROM Range"
Set rsSystem = objExcel.Execute(sSQL)

'Do stuff

rsSystem.Close
Set rsSystem = Nothing
objExcel.Close
Set objExcel = Nothing
Set objFile = Nothing

Doesn't seem to matter if I try to delete the file before or after I do the import, if I try deleting the file right after a successful import, I get the permission denied error but if I wait a minute, I'm then able to delete it.

This is an issue as users are going to be supplied templates and they may make a correction and immediately re-upload.

Any ideas as to why the lock is not getting immediately released when I close all associated objects?

Thanks!

Edit:

Changing the connection string to use a different driver seems to have done the trick, now when I close the objects I can delete the file with no issue

sConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sPath & sFileName & ";Extended Properties=Excel 8.0;"
Tom
  • 4,467
  • 17
  • 59
  • 91
  • Which SQL are we talking about? And if it's SQL Server, then why not use a DTS (2000) or SSIS (2005+)? – Paul Sep 30 '14 at 08:55
  • SQL Server. The spec is for users to upload an Excel spreadsheet from a browser, view what's in the spreadsheet on an intermediate page, verify and then import into a table. Not sure SSIS is best for that, or is it? – Tom Sep 30 '14 at 14:50
  • I don't see why not - you can import from an Excel spreadsheet once the file has been uploaded then delete the file, saving a chunk of space. – Paul Sep 30 '14 at 14:56
  • So you're saying run the SSIS package on these spreadsheets which would reside on networked location? Can you execute an SSIS package from an ASP page? – Tom Sep 30 '14 at 15:08
  • 1
    You can [call a stored procedure which can run an SSIS package](http://code.msdn.microsoft.com/Calling-a-SSIS-Package-a35afefb). – Paul Sep 30 '14 at 15:16

1 Answers1

1

I've actually found a number of ways to do this.

Community
  • 1
  • 1
Paul
  • 4,160
  • 3
  • 30
  • 56
  • I'm not too familiar with SSIS but did some playing around with it and I don't think it's a good fit for what I need to do here. Could use it with some other projects though. I did figure out a way around that permissions denied error, will update the OP and thanks for the locked check, that could come in handy too. – Tom Sep 30 '14 at 17:03
  • No probs, Tom. Good luck. – Paul Oct 01 '14 at 08:08