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-128275414437394676
Trust level: Full
Application Virtual Path: /enquete
Application Path: C:\Inetpub\wwwroot\enquete\
Machine name: SRV754

Process information:
Process ID: 3716
Process name: w3wp.exe
Account name: NT AUTHORITY\NETWORK 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\NETWORK SERVICE

Thread information:
Thread ID: 1
Thread account name: NT AUTHORITY\NETWORK SERVICE
Is impersonating: False
Stack trace: at
System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS
dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object&
executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior
behavior, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior
behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
at enquete.Page_Load(Object sender, EventArgs e) in
C:\Inetpub\wwwroot\enquete\enqueteG2.aspx.vb:line 20
at System.Web.UI.Control.OnLoad(EventArgs e)
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Page.ProcessRequestMain(Boolean
includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

 

[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 Rae]

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

Wow! Who on earth designed this system...???

 

[Ben]

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

 

[ace_away]

Or elect themselves forum police.

 

[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.Connection()
'Connection string is different for Excel 2007. Check
http://www.connectionstrings.com for details.
xl.Open("Driver={Microsoft Excel Driver
(*.xls)};DriverId=790;Dbq=C:\MyExcel.xls;DefaultDir=c:\mypath;")
Dim rs As ADODB.Recordset = xl.Execute("Select * 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("insert 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]]

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

Wow! That brought back some (very painful!) memories!

 

[Ben]

Thanks very much, not only for the explanation, but also for your friendly
way to do that. You don't feel yourself as God in this newsgroup, you are
not arrogant and you don't decide who put stupid questions or do stupid
things. I call that respect for the others.