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

[Tom wilson]

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"

Every article I find with a solution says it's a permissions issue
with ASP.Net. But this is VB.Net, everything running from a machine
with Administrator privs. The C drive and the file have Full Access
to Everyone and the sheet(s) are not protected.

Any ideas?
Thanks!

[vvenk]

I don't think you can update an Excel sheet using OLEDB (also a text file).
That's why yo get the message "operation must use an updateable query"

Venki

"Tom wilson" wrote:

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"

Every article I find with a solution says it's a permissions issue
with ASP.Net. But this is VB.Net, everything running from a machine
with Administrator privs. The C drive and the file have Full Access
to Everyone and the sheet(s) are not protected.

Any ideas?
Thanks!

[Thug Passion]

> Any ideas?

Excel isn't a database. It makes a great presentation tier, if you
want to keep/update your DB and then output an Excel sheet.

[Jamie Collins]

"vvenk" <vv***@discussions.microsoft.com> wrote ...

I don't think you can update an Excel sheet using OLEDB (also a text file).
That's why yo get the message "operation must use an updateable query"

You are mistaken: it is indeed possible to update an Excel sheets and
a text files using OLE DB.

Jamie.

--

[Jamie Collins]

Tom wilson <ye*******@nospam.com> wrote ...

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"

You SQL works for me using this code:

Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source='C:\Tempo\db.xls';Extended Properties='Excel 8.0;HDR=NO'"
Dim conn As New OleDbConnection(strConn)
Dim strSQL As String = "UPDATE [Drugs cleaned up$] SET F6 = 'Test
Ailment' where F1 = 1"
Dim cmd As New OleDbCommand(strSQL)
cmd.Connection = conn
conn.Open()
cmd.ExecuteNonQuery()
cmd.Connection.Close()

As regards the error you are getting, Jet doesn't like in an update
e.g.

UPDATE MyTable SET MyCol = (
SELECT Col1 FROM OneRowTable
);

The above fails with the same error as your even thought the table in
the subquery isn't itself being updated. I'm wondering if one of your
..NET objects is using additional SQL under the covers that is
upsetting the provider. My code, on the other hand, simply sends the
SQL unaltered to the provider for execution. Just a guess.

Jamie.

--

[Paul Clement]

On 22 Nov 2004 01:43:55 -0800, ja**********@xsmail.com (Jamie Collins) wrote:

¤ "vvenk" <vv***@discussions.microsoft.com> wrote ...
¤
¤ > I don't think you can update an Excel sheet using OLEDB (also a text file).
¤ > That's why yo get the message "operation must use an updateable query"
¤
¤ You are mistaken: it is indeed possible to update an Excel sheets and
¤ a text files using OLE DB.
¤
¤ Jamie.

Actually that's half right. You can update data in Excel Worksheets but the Text ISAM driver does
not support the capability to update data in a text file.
Paul ~~~ pc******@ameritech.net
Microsoft MVP (Visual Basic)

[Tom wilson]

THANKS!!!!

I'm given a spreadsheet where any one of the columns can contain a
list of drug names. I have an Access database with a massive list of
drug names and the ailments those drugs treat. I'm to insert a new
column in the spreadsheet, lookup the drug and populate a new appended
column with the referenced ailment. That's why I have to 'modify' an
Excel SS. (That should answer Slugg's q)

However, if I can't get the code you supplied to work directly I'll
first import the SS data into an Access table (I can read the SS all I
want no problem) , do the lookups and the rest of the work within the
Access table and then write it out to a brand new .xls file. (I can
write new stuff all I want, just not modify)

Thank you very much for the input, any advice on this topic is
non-existent and has been frustrating to find. I'll try your example
and expand from there.

Tom
On 22 Nov 2004 01:45:41 -0800, ja**********@xsmail.com (Jamie Collins)
wrote:

Tom wilson <ye*******@nospam.com> wrote ...

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"

You SQL works for me using this code:

Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source='C:\Tempo\db.xls';Extended Properties='Excel 8.0;HDR=NO'"
Dim conn As New OleDbConnection(strConn)
Dim strSQL As String = "UPDATE [Drugs cleaned up$] SET F6 = 'Test
Ailment' where F1 = 1"
Dim cmd As New OleDbCommand(strSQL)
cmd.Connection = conn
conn.Open()
cmd.ExecuteNonQuery()
cmd.Connection.Close()

As regards the error you are getting, Jet doesn't like in an update
e.g.

UPDATE MyTable SET MyCol = (
SELECT Col1 FROM OneRowTable
);

The above fails with the same error as your even thought the table in
the subquery isn't itself being updated. I'm wondering if one of your
.NET objects is using additional SQL under the covers that is
upsetting the provider. My code, on the other hand, simply sends the
SQL unaltered to the provider for execution. Just a guess.

Jamie.

[Jamie Collins]

Paul Clement <Us***********************@swspectrum.com> wrote ...

¤ it is indeed possible to update an Excel sheets and
¤ a text files using OLE DB.

Actually that's half right. You can update data in Excel Worksheets but the
Text ISAM driver does
not support the capability to update data in a text file.

Yes, I admit I was half wrong. I was thinking of INSERT INTO which is
supported for Text. Apologies to vvenk.

Jamie.

--

[Jamie Collins]

Tom wilson <ye*******@nospam.com> wrote ...

I'm given a spreadsheet where any one of the columns can contain a
list of drug names. I have an Access database with a massive list of
drug names and the ailments those drugs treat. I'm to insert a new
column in the spreadsheet, lookup the drug and populate a new appended
column with the referenced ailment. That's why I have to 'modify' an
Excel SS. (That should answer Slugg's q)
Rather than updating data, you are talking about altering the
worksheet's *schema* i.e.

ALTER TABLE ['Drugs cleaned up$'] ADD MyNewCol FLOAT NULL;

This is not supported for Excel. However, because you are using a
worksheet rather than a 'named range' (workbook level defined Name),
there is a workaround: if you put HDR=NO (no column header row) in the
extended properties of the connection string, you can add a new column
header e.g. say you already have column headers in range A1:C1 so the
new column header goes in D1:

UPDATE [Drugs cleaned up$D1:D1] SET F1='MyNewCol';

That said, you may well not be using headers at all which makes things
a bit tricky. While you can specify the address of the range to query,
you will only get columns and data rows for those the intersection
between the address specified and the UsedRange e.g.

SELECT * FROM [Drugs cleaned up$A1:D65535];

may only return, say, three columns if column D had not contained
data.

Another workaround is to use SELECT..INTO to create a new temp table
in your Jet (MS Access) database:

SELECT *
INTO [MS Access;Database=C:\MyDB.mdb;].NewTempTable
FROM ['Drugs cleaned up$'];

and you could additionally do any JOINs to get your final result set
at the same time, then DROP the Excel table:

DROP TABLE ['Drugs cleaned up$'];

then export the temp table back to Excel:

SELECT *
INTO [Drugs cleaned up]
FROM [MS Access;Database=C:\MyDB.mdb;].NewTempTable;

although when I tested this just now, because you sheet name includes
space characters, the new Excel table was created on a new sheet named
Drugs_cleaned_up.
Thank you very much for the input, any advice on this topic is
non-existent and has been frustrating to find.

I agree. Most of what I've gleaned comes from these newsgroups and
practical experimentation. There is little info on MSDN e.g. I have
yet to find the Jet 4.0 help files (Jet 3 help is there). Post back if
you need further help (I even used to develop hospital prescribing
software but I longer have the required multi-million dollar insurance
cover <g>).

Jamie.

--