By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,490 Members | 1,417 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,490 IT Pros & Developers. It's quick & easy.

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

P: n/a
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.

May 10 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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:
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.

May 10 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.