• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2789
  • Last Modified:

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!
0
MIKEV
Asked:
MIKEV
  • 3
  • 3
1 Solution
 
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
 
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now