By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,631 Members | 892 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,631 IT Pros & Developers. It's quick & easy.

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

P: n/a
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.ExecuteCommandTextF orSingleResult(tagDBPARAMS
dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText( Object&
executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(Comm andBehavior
behavior, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderIntern al(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)
Jul 3 '07 #1
Share this Question
Share on Google+
10 Replies


P: n/a
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

Jul 3 '07 #2

P: n/a
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*********@binaryswitch.comschreef
in bericht news:11**********************@k29g2000hsd.googlegr oups.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

Jul 3 '07 #3

P: n/a
"Ben" <b@bnwrote in message news:Od**************@TK2MSFTNGP02.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
Jul 3 '07 #4

P: n/a
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**@markNOSPAMrae.netschreef in bericht
news:Ou**************@TK2MSFTNGP03.phx.gbl...
"Ben" <b@bnwrote in message
news:Od**************@TK2MSFTNGP02.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

Jul 3 '07 #5

P: n/a
"Ben" <b@bnwrote in message news:Ot**************@TK2MSFTNGP04.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

Jul 3 '07 #6

P: n/a
Or elect themselves forum police.

"Ben" <b@bnwrote in message news:Ot**************@TK2MSFTNGP04.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.

Jul 3 '07 #7

P: n/a
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;DefaultDi r=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

Jul 5 '07 #8

P: n/a

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

Man, I'm getting old.

Jul 5 '07 #9

P: n/a
"Mark S. Milley, MCSD (BinarySwitch)" <ma*********@binaryswitch.comwrote
in message news:11*********************@k79g2000hse.googlegro ups.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

Jul 5 '07 #10

P: n/a
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.
"Mark S. Milley, MCSD (BinarySwitch)" <ma*********@binaryswitch.comschreef
in bericht news:11*********************@k79g2000hse.googlegro ups.com...
>
Did I say 6? 8 years. I haven't used this method since 1999.

Man, I'm getting old.

Jul 7 '07 #11

This discussion thread is closed

Replies have been disabled for this discussion.