Excel using ado.net - operation must use an updateable query
Posted on 2004-11-18
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"