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

Discussion in 'ASP .Net' started by Ben, Jul 3, 2007.

  1. Ben

    Ben Guest

    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)
     
    Ben, Jul 3, 2007
    #1
    1. Advertising

  2. 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 S. Milley, MCAD (BinarySwitch), Jul 3, 2007
    #2
    1. Advertising

  3. Ben

    Ben Guest

    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)" <> schreef
    in bericht news:...
    > 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, Jul 3, 2007
    #3
  4. Ben

    Mark Rae Guest

    "Ben" <b@bn> wrote in message news:...

    > 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
     
    Mark Rae, Jul 3, 2007
    #4
  5. Ben

    Ben Guest

    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" <> schreef in bericht
    news:OuwDy$...
    > "Ben" <b@bn> wrote in message
    > news:...
    >
    >> 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, Jul 3, 2007
    #5
  6. Ben

    Mark Rae Guest

    "Ben" <b@bn> wrote in message news:...

    > 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
     
    Mark Rae, Jul 3, 2007
    #6
  7. Ben

    ace_away Guest

    Or elect themselves forum police.

    "Ben" <b@bn> wrote in message news:...
    > 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, Jul 3, 2007
    #7
  8. 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), Jul 5, 2007
    #8
  9. Did I say 6? 8 years. I haven't used this method since 1999.

    Man, I'm getting old.
     
    Mark S. Milley, MCSD (BinarySwitch), Jul 5, 2007
    #9
  10. "Mark S. Milley, MCSD (BinarySwitch)" <> wrote
    in message news:...

    > 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
     
    Mark Rae [MVP], Jul 5, 2007
    #10
  11. Ben

    Ben Guest

    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)" <> schreef
    in bericht news:...
    >
    > Did I say 6? 8 years. I haven't used this method since 1999.
    >
    > Man, I'm getting old.
    >
     
    Ben, Jul 7, 2007
    #11
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Don Munroe
    Replies:
    2
    Views:
    336
    Don Munroe
    Jul 28, 2004
  2. Steven Cheng[MSFT]
    Replies:
    4
    Views:
    409
    Steven Cheng[MSFT]
    Aug 25, 2004
  3. =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?=

    Problem with Excel reports ::::Excel 2003 Migration To Excel 2007

    =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?=, Oct 5, 2007, in forum: ASP .Net
    Replies:
    15
    Views:
    1,529
    =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?=
    Oct 24, 2007
  4. fniles
    Replies:
    6
    Views:
    656
    Karl E. Peterson
    Apr 27, 2009
  5. will
    Replies:
    6
    Views:
    413
    Phrogz
    Dec 27, 2006
Loading...

Share This Page