Excel using - operation must use an updateable query

Posted on 2004-11-18
Last Modified: 2012-07-11
This is driving me nuts.  I'm trying to update an Excel spreadsheet using ADO.Net and Oledb in VB.Net.  The connection is open, the adapter is connected and the dataset is loaded.  Here's the code in question:

        myDataRow = myDataSet.Tables(0).Rows(RowNum)
        myDataRow(ColumnCount) = Ailment
        Adapter.UpdateCommand = New OleDbCommand("UPDATE [" & SheetName & "] SET F" & ColumnCount & " = '" & Ailment & "' where F1 = " & RowNum & "", Conn)
        Adapter.Update(myDataSet, "[" & SheetName & "]")

The query sent is:

"UPDATE [Drugs cleaned up$] SET F6 = 'Test Ailment' where F1 = 1"

In the spreadsheet, F1 = 1.  It's a uniquely numbered column and I assume, the primary key.  F6 is also a valid column.  The Sheet name is also correct cause it's open.  I figured, and am told from MS documents, that a unique column is required to make it an updateable query.  That's what I've done but I can't get anything but:

"operation must use an updateable query"

Any ideas?
Question by:MIKEV
    LVL 28

    Accepted Solution

    Hi MIKEV,

    do you have rights on the directory where the xls files is ? ( you have to set to the aspnet user, not to the group Everyone )


    Author Comment

    Yes, thanks, I'm logged in as the domain administrator and have full control to the file and directory.

    I'm stumped, everything I do or try returns the same annoying error.
    LVL 28

    Expert Comment

    is this windows or web application ?


    Author Comment

    Windows; VS.Net 2003, using VB.Net, ADO.Net, Oledb.  Fyi, the code that opens it:

            Dim strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & XLSName & ";Extended Properties=""Excel 8.0;HDR=No;IMEX=1"""
            Dim x As Integer

            Conn = New OleDbConnection(strConnect)
            Adapter = New OleDbDataAdapter
            Adapter.SelectCommand = New OleDbCommand("select * from [" & SheetName & "]", Conn)

            myDataSet = New DataSet
            Adapter.Fill(myDataSet, "[" & SheetName & "]")
            RecordCount = myDataSet.Tables(0).Rows.Count
            ColumnCount = myDataSet.Tables(0).Columns.Count
            myDataSet.Tables(0).Columns.Add("Ailment", System.Type.GetType("System.String"))

    I append a new column to take the data I'm trying to insert.  I can't see the new column being the problem, a string type should take anything I throw at it.  I did try "where F1 = '1'" but get a datatype error so I know it's looking at the right column for lookup.  Every web article I find relates to permission errors using ASP.Net but this is VB.Net in an Administrator environment.


    Author Comment

    There is no answer to this question.  As a matter of fact, it's not possible using ADO.Net.  I can read a spreadsheet and create a new spreadsheet but updating...  There's nothing, anywhwere on it.  I've been to at least 15 different message boards, newsgroups and the like and the best answer I can get is that it's not supported by MS.  I suppose I could spend $245 to get the answer but I refuse to pay for what should be documented.  Rather than update, I'm going to import the entire SS into an Access table, do the processing there and re-write it to a completely new SS.  It's a bit more work but it'll get the job done.

    Thanks MM, I much appreciate your input.
    LVL 28

    Expert Comment

    It is strange approach but it is interesting and the more important is that it is working for you. another thing is that this feature is not allowded for MS :(

    sorry that could not suggest you a working solution


    Expert Comment

    I have come to the same conclusion.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Flash ( has evolved over the years to where it has become a masterful tool for displaying content screen.  It has excellent layout placement, UI precision as well as rendering capabilities. This, along with t…
    Recently while returning home from work my wife (another .NET developer) was murmuring something. On further poking she said that she has been assigned a task where she has to serialize and deserialize objects and she is afraid of serialization. Wha…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now