weird problem with an excel file, asp.net and IIS

[Ben]

Hi,

i have a weird problem and i don't know who is responsible for this: IIS,
excel or asp.net.
My problem:

we use an asp.net 2.0 application under IIS 6.0 (server 2003 sp2) which must
write data from a form into a table in excel files (i know excel is not
really recommended for that, but it's excel).There are a lot of excel files,
all in the same directory with the same privileges (Network service has
r/w).

There is only a problem with ONE excel file, where it's not possible to
write data (Operation must use an updateable query) and that file is locked
(can't open it) unless i delete the w3wp.exe process.

Can that excel file be 'corrupt' or something? When i open it, there is
nothing abnormal.
Where have i to seatch?
Thanks for advices
Ben

The whole message i get in the Event log is here:

Application information:
Application domain: /LM/W3SVC/1/Root/enquete-2-128275414437394 676
Trust level: Full
Application Virtual Path: /enquete
Application Path: C:\Inetpub\wwwr oot\enquete\
Machine name: SRV754

Process information:
Process ID: 3716
Process name: w3wp.exe
Account name: NT AUTHORITY\NETWO RK SERVICE

Exception information:
Exception type: OleDbException
Exception message: Operation must use an updateable query.

Request information:
Request URL: http://srv754/enquete/ros2rp0607.aspx
Request path: /enquete/ros2rp0607.aspx
User host address: 10.135.181.130
User: CAMPUS\L0386
Is authenticated: True
Authentication Type: Negotiate
Thread account name: NT AUTHORITY\NETWO RK SERVICE

Thread information:
Thread ID: 1
Thread account name: NT AUTHORITY\NETWO RK SERVICE
Is impersonating: False
Stack trace: at
System.Data.Ole Db.OleDbCommand .ExecuteCommand TextForSingleRe sult(tagDBPARAM S
dbParams, Object& executeResult)
at System.Data.Ole Db.OleDbCommand .ExecuteCommand Text(Object&
executeResult)
at System.Data.Ole Db.OleDbCommand .ExecuteCommand (CommandBehavio r
behavior, Object& executeResult)
at System.Data.Ole Db.OleDbCommand .ExecuteReaderI nternal(Command Behavior
behavior, String method)
at System.Data.Ole Db.OleDbCommand .ExecuteNonQuer y()
at enquete.Page_Lo ad(Object sender, EventArgs e) in
C:\Inetpub\wwwr oot\enquete\enq ueteG2.aspx.vb: line 20
at System.Web.UI.C ontrol.OnLoad(E ventArgs e)
at System.Web.UI.C ontrol.LoadRecu rsive()
at System.Web.UI.P age.ProcessRequ estMain(Boolean
includeStagesBe foreAsyncPoint, Boolean includeStagesAf terAsyncPoint)

[Mark S. Milley, MCAD (BinarySwitch)]

Hi Ben -

My experience with Excel is limited, but I've had a similar problem
with using Access. The real problem here is that, even after you
dispose of your connection to the file, it remains open in the
background (possibly due to connection pooling?). That means the lock
will remain on the file until garbage collection occurs (I've tried
forcing GC to no avail) or the process/web application itself is
stopped.

The typical response from MS is that Excel is not recommended for
this, which you already knew.

You can try to override or bypass the connection pool, but this is
trickier than it sounds; I've heard of some people having success with
that, but I am not one of them. Another (horrible) option is to use
ADO, not ADO.NET, which should really close the file when you tell it
to close.

But the best advice I can give you is that it might be better to
rethink your approach; sometimes not taking the client's requirements
so literally leads to a more stable solution.

Is there any way you can instead use a database server for data
storage, and export the excel files as required from the database?

Good Luck,

-Mark

[Ben]

Hi, thanks for replying ...

Unfortunately, excel is the only option for this application, because
eveybody can type data into it ...

Could you please explain me how to bypass the connection pool?

"Mark S. Milley, MCAD (BinarySwitch)" <ma*********@bi naryswitch.coms chreef
in bericht news:11******** **************@ k29g2000hsd.goo glegroups.com.. .

Hi Ben -

My experience with Excel is limited, but I've had a similar problem
with using Access. The real problem here is that, even after you
dispose of your connection to the file, it remains open in the
background (possibly due to connection pooling?). That means the lock
will remain on the file until garbage collection occurs (I've tried
forcing GC to no avail) or the process/web application itself is
stopped.

The typical response from MS is that Excel is not recommended for
this, which you already knew.

You can try to override or bypass the connection pool, but this is
trickier than it sounds; I've heard of some people having success with
that, but I am not one of them. Another (horrible) option is to use
ADO, not ADO.NET, which should really close the file when you tell it
to close.

But the best advice I can give you is that it might be better to
rethink your approach; sometimes not taking the client's requirements
so literally leads to a more stable solution.

Is there any way you can instead use a database server for data
storage, and export the excel files as required from the database?

Good Luck,

-Mark

[Mark Rae]

"Ben" <b@bnwrote in message news:Od******** ******@TK2MSFTN GP02.phx.gbl...

Unfortunately, excel is the only option for this application, because
eveybody can type data into it ...

Wow! Who on earth designed this system...???
--
Mark Rae
ASP.NET MVP
http://www.markrae.net

[Ben]

You should better to give an answer otherwise keep your comments for
yourself ...
Those newsgroups are not intented for making your own publicity.

"Mark Rae" <ma**@markNOSPA Mrae.netschreef in bericht
news:Ou******** ******@TK2MSFTN GP03.phx.gbl...

"Ben" <b@bnwrote in message
news:Od******** ******@TK2MSFTN GP02.phx.gbl...

>Unfortunatel y, excel is the only option for this application, because
eveybody can type data into it ...

Wow! Who on earth designed this system...???
--
Mark Rae
ASP.NET MVP
http://www.markrae.net

[Mark Rae]

"Ben" <b@bnwrote in message news:Ot******** ******@TK2MSFTN GP04.phx.gbl...

You should better to give an answer otherwise keep your comments for
yourself ...
Those newsgroups are not intented for making your own publicity.

OK, then - explain to me how Excel is the "only option" for a data entry
system...
--
Mark Rae
ASP.NET MVP
http://www.markrae.net

[ace_away]

Or elect themselves forum police.

"Ben" <b@bnwrote in message news:Ot******** ******@TK2MSFTN GP04.phx.gbl...

You should better to give an answer otherwise keep your comments for
yourself ...
Those newsgroups are not intented for making your own publicity.

[Mark S. Milley, MCSD (BinarySwitch)]

Hi Ben -

I'd use this with caution; I can't express enough how much of a bad
design this is. This flies in the face of all the progress that has
been made over the last six years.

That said, here's the old school way to access excel data.

(insert flashback noise and wavy lines here)

For this to work, you'll need to add a reference to ADO (Microsoft
ActiveX Data Objects (any ver))

Dim xl As New ADODB.Connectio n()
'Connection string is different for Excel 2007. Check
http://www.connectionstrings.com for details.
xl.Open("Driver ={Microsoft Excel Driver
(*.xls)};Driver Id=790;Dbq=C:\M yExcel.xls;Defa ultDir=c:\mypat h;")
Dim rs As ADODB.Recordset = xl.Execute("Sel ect * from
[Sheet1$]") 'I.e. excel worksheet name followed by a "$" and wrapped
in "[" "]" brackets.
Do While Not rs.EOF
Debug.WriteLine (rs(0)) 'Example. rs() will take columnname
or column number.
rs.MoveNext()
Loop
rs.Close()
rs = Nothing

'Insert Example. Use the Execute() method for Insert,Update,
and Delete as well.
xl.Execute("ins ert into [Sheet1$] values(""test"" )") 'You may
have to experiement with the exact SQL syntax. I'm pretty sure it
mirrors MS Access.
xl.Close() 'This will really close the file.
xl = Nothing
(/flashback)

Okay; that said, again, I must stress that you're probably better off
coming up with a better data storage solution. You realize, that if
someone has the excel file open on the server, and your code tries to
modify it, you will recieve an error, right?

My job is done here... you know enough to be dangerous now.

Good Luck,

-Mark

[Mark S. Milley, MCSD (BinarySwitch)]

Did I say 6? 8 years. I haven't used this method since 1999.

Man, I'm getting old.

[Mark Rae [MVP]]

"Mark S. Milley, MCSD (BinarySwitch)" <ma*********@bi naryswitch.comw rote
in message news:11******** *************@k 79g2000hse.goog legroups.com...

Did I say 6? 8 years. I haven't used this method since 1999.

Wow! That brought back some (very painful!) memories!
--
Mark Rae
ASP.NET MVP
http://www.markrae.net