Excel using ado.net - operation must use an updateable query

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?
Thanks!
MIKEVAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mmarinovCommented:
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 )

Regards!
B..M
mmarinov
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MIKEVAuthor Commented:
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.
0
mmarinovCommented:
is this windows or web application ?

B..M
mmarinov
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

MIKEVAuthor Commented:
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)

        Conn.Open()
        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.

0
MIKEVAuthor Commented:
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.
0
mmarinovCommented:
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

B..M
mmarinov
0
LezlyPrimeCommented:
I have come to the same conclusion.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.