[dba-VB] ASP.NET Excel file locked after reading

Hi Gustav,

Thanks for your thoughts. I am not sure when the actual object is released
after you explicitly close it, but experience seems to indicate that some
times the connection isn't actually released for a signicicant time, enough
time for me to select the column values and then hit the import button. This
is not repeatable which is the troubling part. Most of the time the app.
Works, but every once in a while the file is left in a locked state. I can
handle that on my dev. Machine but not on the server. Your idea of the temp
table may be the way to go. I am trying to understand the underlying
process.

Doug

-----Original Message-----
From: dba-vb-bounces at databaseadvisors.com
[mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Tuesday, June 15, 2010 8:28 AM
To: dba-vb at databaseadvisors.com
Subject: Re: [dba-VB] ASP.NET Excel file locked after reading

Hi Doug

You are right, don't hold the connection for a time not known. But then you
have two asynchronous connections and it may happen that the system is not
that fast at releasing the first.

Could you either:

(Pre)fill a temp table with the data from the worksheet or just make a copy
of the worksheet? Then read and import from that table, or read from the
copy and import from the original worksheet?

Or:

Prepare a temp table or worksheet with the columns to view when the
worksheet is uploaded? Then, later, import the original worksheet as to the
user's choice of columns etc.

/gustav


>>> dw-murphy at cox.net 15-06-2010 17:08 >>>
Maybe I am doing this wrong, but the first time I read the file I grab the
column headers and a few rows and then show post the page to the user so
they can correctly match which column goes into which field. After the user
selects the columns the re-submit the page and the connection to the file is
opened again and the contents read into the appropriate database table. I
dont' like to leave connections open as the user may not complete the
process. Bottom line is that there are a couple of steps to the process with
web pages going back and forth.


-----Original Message-----
From: dba-vb-bounces at databaseadvisors.com
[mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Monday, June 14, 2010 10:36 PM
To: dba-vb at databaseadvisors.com
Subject: Re: [dba-VB] ASP.NET Excel file locked after reading

Hi Doug

Stupid question perhaps, but why do you need to open it more than once? Or
to be more precise: Can't you reuse the connection from the view for the
import?

/gustav


>>> dw-murphy at cox.net 15-06-2010 00:41 >>>
Folks,

I have an application on a web site that allows users to upload excel files
and then import the data into their records. After the file is uploaded
there is a routine that reads the header row and then reads the first ten
records and then presents a page where the user selects which excel columns
go into which field in the table we import into. All that works fine. The
problem is that intermittently the excel file is locked, by an open
connection I assume, and I can't get access to read in the data for the
final import, I just get a error that the file is in use. All connections
are closed after use so I can't figure out where the lock is coming from.
Some similar postings I have read on the web indicate that forced garbage
collection is required for this problem.

Any one have similar experience or suggestions to get rid of the locking?

Thanks in advance.

Doug



_______________________________________________
dba-VB mailing list
dba-VB at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-vb
http://www.databaseadvisors.com