tom 0

tom 0

  • NA
  • 1
  • 0

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

Nov 19 2004 12:14 PM
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" I've scoured every article I can find and the only solution I can get is a permissions issue if I'm using ASP.Net but I'm not. This is a windows application with everything local, logged in as administrator. I've given full access to "Everyone" for the C drive and the xls file. The spreadsheet is not protected. Any ideas? Thanks!