Operation must use an updateable query EXCEPTION when trying to update excel

10 May 2006 8:30 AM

Muskito

HELP!!!

Hello All,

I'm using VB.net 2003 and trying to update data in Excel worksheet.

The program selects data from the excel, updates something in the MSSQL
DB and then tries to update something back to the excel worksheet.

My problem is that i'm having this annoying exception: "Operation must
use an updateable query"
1. The excel worksheet is on the desktop
2. The file has full permission for Everyone
3. The Folder has full permission for Everyone

The code looks like this:

Dim oConnection As System.Data.OleDb.OleDbConnection
oConnection = New System.Data.OleDb.OleDbConnection( _
                  "provider=Microsoft.Jet.OLEDB.4.0; " & _
                  "data source=" & txtFile.Value & ";" & _
                  "Extended Properties='Excel 8.0;IMEX=1'")
oConnection.Open()
Dim sCmd As String = "UPDATE [" & txtSheetName.Value & "$] SET " &
dsExcel.Tables(0).Columns(13).ColumnName & " = '" &
oRow.ClientNumberInServiceGiver & "' WHERE F8 = '1549461305'"
Dim oCommand As New OleDb.OleDbCommand(sCmd)
oCommand.Connection = oConnection
oConnection.Open()
oCommand.ExecuteNonQuery()

the sCmd looks like this when it is running:
"UPDATE [Sheet1$] SET F14 = '7370037530-3' WHERE F8 = '1549461305'"

Does anyone have any idea for why this problem occurs?...

Thanks in advance.

10 May 2006 3:41 PM

tomb

I had this same issue a while back in Access with a linked Excel file.
The damn thing isn't updateable! I don't understand it. Never found a
way to update it, either. I had to build a new spreadsheet with the
new data.

T

Muskito wrote:

Show quoteHide quote

>HELP!!!
>
>Hello All,
>
>I'm using VB.net 2003 and trying to update data in Excel worksheet.
>
>The program selects data from the excel, updates something in the MSSQL
>DB and then tries to update something back to the excel worksheet.
>
>My problem is that i'm having this annoying exception: "Operation must
>use an updateable query"
>1. The excel worksheet is on the desktop
>2. The file has full permission for Everyone
>3. The Folder has full permission for Everyone
>
>The code looks like this:
>
>Dim oConnection As System.Data.OleDb.OleDbConnection
>oConnection = New System.Data.OleDb.OleDbConnection( _
>                  "provider=Microsoft.Jet.OLEDB.4.0; " & _
>                  "data source=" & txtFile.Value & ";" & _
>                  "Extended Properties='Excel 8.0;IMEX=1'")
>oConnection.Open()
>Dim sCmd As String = "UPDATE [" & txtSheetName.Value & "$] SET " &
>dsExcel.Tables(0).Columns(13).ColumnName & " = '" &
>oRow.ClientNumberInServiceGiver & "' WHERE F8 = '1549461305'"
>Dim oCommand As New OleDb.OleDbCommand(sCmd)
>oCommand.Connection = oConnection
>oConnection.Open()
>oCommand.ExecuteNonQuery()
>
>the sCmd looks like this when it is running:
>"UPDATE [Sheet1$] SET F14 = '7370037530-3' WHERE F8 = '1549461305'"
>
>Does anyone have any idea for why this problem occurs?...
>
>Thanks in advance.
>
>
>