Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > weird problem with an excel file, asp.net and IIS

Reply
Thread Tools

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

 
 
Ben
Guest
Posts: n/a
 
      07-03-2007
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)


 
Reply With Quote
 
 
 
 
Mark S. Milley, MCAD (BinarySwitch)
Guest
Posts: n/a
 
      07-03-2007
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

 
Reply With Quote
 
 
 
 
Ben
Guest
Posts: n/a
 
      07-03-2007
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)" <(E-Mail Removed)> schreef
in bericht news:(E-Mail Removed) 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
>



 
Reply With Quote
 
Mark Rae
Guest
Posts: n/a
 
      07-03-2007
"Ben" <b@bn> wrote in message news:(E-Mail Removed)...

> 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
 
Reply With Quote
 
Ben
Guest
Posts: n/a
 
      07-03-2007
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" <(E-Mail Removed)> schreef in bericht
news:OuwDy$(E-Mail Removed)...
> "Ben" <b@bn> wrote in message
> news:(E-Mail Removed)...
>
>> 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



 
Reply With Quote
 
Mark Rae
Guest
Posts: n/a
 
      07-03-2007
"Ben" <b@bn> wrote in message news:(E-Mail Removed)...

> 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

 
Reply With Quote
 
ace_away
Guest
Posts: n/a
 
      07-03-2007
Or elect themselves forum police.

"Ben" <b@bn> wrote in message news:(E-Mail Removed)...
> You should better to give an answer otherwise keep your comments for
> yourself ...
> Those newsgroups are not intented for making your own publicity.



 
Reply With Quote
 
Mark S. Milley, MCSD (BinarySwitch)
Guest
Posts: n/a
 
      07-05-2007
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

 
Reply With Quote
 
Mark S. Milley, MCSD (BinarySwitch)
Guest
Posts: n/a
 
      07-05-2007

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

Man, I'm getting old.

 
Reply With Quote
 
Mark Rae [MVP]
Guest
Posts: n/a
 
      07-05-2007
"Mark S. Milley, MCSD (BinarySwitch)" <(E-Mail Removed)> wrote
in message news:(E-Mail Removed) 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

 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
From an ASP page, is there a way write to an EXCEL file without having EXCEL installed on the IIS machine ? fniles ASP General 6 04-27-2009 10:33 PM
Problem with Excel reports ::::Excel 2003 Migration To Excel 2007 =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?= ASP .Net 15 10-24-2007 01:34 PM
newbie with a weird technical problem (@ least I think it's weird) will Ruby 6 12-27-2006 04:46 PM
RE: Weird problem IIS 6, Asp.net, certain users, code behind Steven Cheng[MSFT] ASP .Net 4 08-25-2004 09:42 AM
Weird problem IIS 6, Asp.net, certain users, code behind Don Munroe ASP .Net 2 07-28-2004 02:27 PM



Advertisments