Operation must use an updateable query... while trying to write to Excel file

  • Thread starter Thread starter jef
  • Start date Start date
J

jef

I'm attempting to use OLEDB to write an Excel file. I start with an
existing file that has a single worksheet with one row (the column
headers). Everything seems to be working except when I try to save the
file I get the above mentioned error. I'm running on Windows 2003
Server. I'm able to write a text file to the same folder I'm writing
the Excel file... and the permissions on the XLS file are read/write.
Here's my code:

Dim sConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=ExportFile.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
Dim oConn As New OleDbConnection(sConn)

System.IO.File.CreateText("blah.txt")

'Get the list of tables (worksheets) from the Excel file
oConn.Open()
Dim schemaTable As DataTable =
oConn.GetOleDbSchemaTable(Data.OleDb.OleDbSchemaGuid.Tables, New
Object() {Nothing, Nothing, Nothing, "TABLE"})
Dim sTable As String
'There should only be one table (worksheet) in the file
'If schemaTable.Rows.Count = 1 Then
sTable = schemaTable.Rows(0)!TABLE_NAME.ToString
Dim objCmd As New OleDbCommand("SELECT * FROM [BBSC - SEND$]", oConn)

Dim objDA As New System.Data.OleDb.OleDbDataAdapter()
objDA.SelectCommand = objCmd
' Fill DataSet
Dim objDS As New DataSet()
objDA.Fill(objDS)

Dim objInsCmd As OleDbCommand = New OleDbCommand( _
"INSERT INTO [BBSC - SEND$] (BBSC_TRANSACTION_ID,
BBSC_IMAGE_ID, BBSC_RECEIVED_DATE, BBSC_SEND_DATE) " & _
"VALUES (?, ?, ?, ?)", oConn)

objInsCmd.Parameters.Add( _
"BBSC_TRANSACTION_ID", OleDbType.VarChar, 5, "BBSC_TRANSACTION_ID")
objInsCmd.Parameters.Add( _
"BBSC_IMAGE_ID", OleDbType.VarChar, 10, "BBSC_IMAGE_ID")
objInsCmd.Parameters.Add( _
"BBSC_RECEIVED_DATE", OleDbType.VarChar, 10, "BBSC_RECEIVED_DATE")
objInsCmd.Parameters.Add( _
"BBSC_SEND_DATE", OleDbType.VarChar, 10, "BBSC_SEND_DATE")
objDA.InsertCommand = objInsCmd

Dim Row As DataRow = objDS.Tables(0).NewRow
Row("BBSC_TRANSACTION_ID") = "12345"
Row("BBSC_IMAGE_ID") = "1"
Row("BBSC_RECEIVED_DATE") = "01/01/2001"
Row("BBSC_SEND_DATE") = "02/02/2002"
objDS.Tables(0).Rows.Add(Row)
objDA.Update(objDS)

It fails on the objDA.Update() line... any ideas?
 
jef said:
I'm attempting to use OLEDB to write an Excel file. I start with an
existing file that has a single worksheet with one row (the column
headers). Everything seems to be working except when I try to save the
file I get the above mentioned error. I'm running on Windows 2003
Server. I'm able to write a text file to the same folder I'm writing
the Excel file... and the permissions on the XLS file are read/write.
Here's my code:

Dim sConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=ExportFile.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
Dim oConn As New OleDbConnection(sConn)

System.IO.File.CreateText("blah.txt")

'Get the list of tables (worksheets) from the Excel file
oConn.Open()
Dim schemaTable As DataTable =
oConn.GetOleDbSchemaTable(Data.OleDb.OleDbSchemaGuid.Tables, New
Object() {Nothing, Nothing, Nothing, "TABLE"})
Dim sTable As String
'There should only be one table (worksheet) in the file
'If schemaTable.Rows.Count = 1 Then
sTable = schemaTable.Rows(0)!TABLE_NAME.ToString
Dim objCmd As New OleDbCommand("SELECT * FROM [BBSC - SEND$]", oConn)

Dim objDA As New System.Data.OleDb.OleDbDataAdapter()
objDA.SelectCommand = objCmd
' Fill DataSet
Dim objDS As New DataSet()
objDA.Fill(objDS)

Dim objInsCmd As OleDbCommand = New OleDbCommand( _
"INSERT INTO [BBSC - SEND$] (BBSC_TRANSACTION_ID,
BBSC_IMAGE_ID, BBSC_RECEIVED_DATE, BBSC_SEND_DATE) " & _
"VALUES (?, ?, ?, ?)", oConn)

objInsCmd.Parameters.Add( _
"BBSC_TRANSACTION_ID", OleDbType.VarChar, 5, "BBSC_TRANSACTION_ID")
objInsCmd.Parameters.Add( _
"BBSC_IMAGE_ID", OleDbType.VarChar, 10, "BBSC_IMAGE_ID")
objInsCmd.Parameters.Add( _
"BBSC_RECEIVED_DATE", OleDbType.VarChar, 10, "BBSC_RECEIVED_DATE")
objInsCmd.Parameters.Add( _
"BBSC_SEND_DATE", OleDbType.VarChar, 10, "BBSC_SEND_DATE")
objDA.InsertCommand = objInsCmd

Dim Row As DataRow = objDS.Tables(0).NewRow
Row("BBSC_TRANSACTION_ID") = "12345"
Row("BBSC_IMAGE_ID") = "1"
Row("BBSC_RECEIVED_DATE") = "01/01/2001"
Row("BBSC_SEND_DATE") = "02/02/2002"
objDS.Tables(0).Rows.Add(Row)
objDA.Update(objDS)

It fails on the objDA.Update() line... any ideas?
Click to expand...

I've run into this problem before. Usually it means that you don't
have permission to write to the file. However, I've had a few
instances where that was not the problem.

Oddly, I discovered that although the command built and assigned to the
data adapter doesn't seem to work, if you create a command and execute
it for each row manually, it does work. I'm not sure why, but there
seems to be a problem with using a data adapter in this manner.

So... to overcome your problem:

1) Create a single command
2) Iterate through each row
a) Update the command parameters on each iteration
b) Execute the command on each iteration
3) Dispose of the command

This is supposed to be what the data adapter does, so it doesn't make
much sense why it doesn't work. There may be a more definitive answer
(which I'd love to here), but I've never found it.

Hope this helps.

- Chris
 
¤ I'm attempting to use OLEDB to write an Excel file. I start with an
¤ existing file that has a single worksheet with one row (the column
¤ headers). Everything seems to be working except when I try to save the
¤ file I get the above mentioned error. I'm running on Windows 2003
¤ Server. I'm able to write a text file to the same folder I'm writing
¤ the Excel file... and the permissions on the XLS file are read/write.
¤ Here's my code:
¤

Is this an ASP.NET application?


Paul
~~~~
Microsoft MVP (Visual Basic)
 
¤ I'm attempting to use OLEDB to write an Excel file. I start with an
¤ existing file that has a single worksheet with one row (the column ¤
headers). Everything seems to be working except when I try to save the
¤ file I get the above mentioned error. I'm running on Windows 2003 ¤
Server. I'm able to write a text file to the same folder I'm writing ¤
the Excel file... and the permissions on the XLS file are read/write.
¤ Here's my code:
¤
Is this an ASP.NET application?


Paul
~~~~
Microsoft MVP (Visual Basic)
Click to expand...


No... this is not ASP.NET ... just a simple VB.NET application. I've
confirmed I have read/write access on the XLS file as well as the
parent folder.
 
¤ On 2006-04-18 08:22:49 -0400, Paul Clement
¤ <[email protected]> said:
¤
¤ >
¤ > ¤ I'm attempting to use OLEDB to write an Excel file. I start with an
¤ > ¤ existing file that has a single worksheet with one row (the column ¤
¤ > headers). Everything seems to be working except when I try to save the
¤ > ¤ file I get the above mentioned error. I'm running on Windows 2003 ¤
¤ > Server. I'm able to write a text file to the same folder I'm writing ¤
¤ > the Excel file... and the permissions on the XLS file are read/write.
¤ > ¤ Here's my code:
¤ > ¤
¤ > Is this an ASP.NET application?
¤ >
¤ >
¤ > Paul
¤ > ~~~~
¤ > Microsoft MVP (Visual Basic)
¤
¤
¤ No... this is not ASP.NET ... just a simple VB.NET application. I've
¤ confirmed I have read/write access on the XLS file as well as the
¤ parent folder.

Does anything change if you assign full permissions? One thing to consider is that you're using two
different mechanisms to work with the files. Allowing full permissions should either eliminate or
confirm whether there is a security issue.


Paul
~~~~
Microsoft MVP (Visual Basic)
 
Does anything change if you assign full permissions? One thing to
consider is that you're using two
different mechanisms to work with the files. Allowing full permissions
should either eliminate or
confirm whether there is a security issue.
Click to expand...

Nothing changes if I assign full permission. I've copied the program
to a Windows XP box and have the exact same results. On the XP box I'm
running as Administrator. Permissions shouldn't be an issue.
 
¤ On 2006-04-18 14:41:06 -0400, Paul Clement
¤ <[email protected]> said:
¤ >
¤ > Does anything change if you assign full permissions? One thing to
¤ > consider is that you're using two
¤ > different mechanisms to work with the files. Allowing full permissions
¤ > should either eliminate or
¤ > confirm whether there is a security issue.
¤
¤ Nothing changes if I assign full permission. I've copied the program
¤ to a Windows XP box and have the exact same results. On the XP box I'm
¤ running as Administrator. Permissions shouldn't be an issue.
¤

The Worksheet that you are attempting to update isn't protected is it?


Paul
~~~~
Microsoft MVP (Visual Basic)
 
¤ On 2006-04-18 14:41:06 -0400, Paul Clement ¤
<[email protected]> said:
¤ > ¤ > Does anything change if you assign full permissions? One thing
to ¤ > consider is that you're using two
¤ > different mechanisms to work with the files. Allowing full
permissions ¤ > should either eliminate or
¤ > confirm whether there is a security issue.
¤ ¤ Nothing changes if I assign full permission. I've copied the
program ¤ to a Windows XP box and have the exact same results. On the
XP box I'm ¤ running as Administrator. Permissions shouldn't be an
issue.
¤
The Worksheet that you are attempting to update isn't protected is it?


Paul
~~~~
Microsoft MVP (Visual Basic)
Click to expand...

No... it is not protected.
 
I've run into this problem before. Usually it means that you don't
have permission to write to the file. However, I've had a few
instances where that was not the problem.

Oddly, I discovered that although the command built and assigned to the
data adapter doesn't seem to work, if you create a command and execute
it for each row manually, it does work. I'm not sure why, but there
seems to be a problem with using a data adapter in this manner.

So... to overcome your problem:

1) Create a single command
2) Iterate through each row
a) Update the command parameters on each iteration
b) Execute the command on each iteration
3) Dispose of the command

This is supposed to be what the data adapter does, so it doesn't make
much sense why it doesn't work. There may be a more definitive answer
(which I'd love to here), but I've never found it.

Hope this helps.

- Chris
Click to expand...

Chris,

I tried your suggestion but I get the same error. While debugging, I
can interogate the DataSet and it has the inserted rows. I'm at a
complete loss... this driving me insane.
 
¤ On 2006-04-19 10:23:34 -0400, Paul Clement
¤ <[email protected]> said:
¤
¤ >
¤ > ¤ On 2006-04-18 14:41:06 -0400, Paul Clement ¤
¤ > <[email protected]> said:
¤ > ¤ > ¤ > Does anything change if you assign full permissions? One thing
¤ > to ¤ > consider is that you're using two
¤ > ¤ > different mechanisms to work with the files. Allowing full
¤ > permissions ¤ > should either eliminate or
¤ > ¤ > confirm whether there is a security issue.
¤ > ¤ ¤ Nothing changes if I assign full permission. I've copied the
¤ > program ¤ to a Windows XP box and have the exact same results. On the
¤ > XP box I'm ¤ running as Administrator. Permissions shouldn't be an
¤ > issue.
¤ > ¤
¤ > The Worksheet that you are attempting to update isn't protected is it?
¤ >
¤ >
¤ > Paul
¤ > ~~~~
¤ > Microsoft MVP (Visual Basic)
¤
¤ No... it is not protected.

Try specifying a table name for your DataTable:

objDA.Fill(objDS, "BBSCSend")
...
...
...
objDA.Update(objDS, "BBSCSend")

You may also want to prefix the parameter names w/an "@" symbol to distinguish them from the field
names. For example:

objInsCmd.Parameters.Add( _
"@BBSC_TRANSACTION_ID", OleDbType.VarChar, 5, "BBSC_TRANSACTION_ID")


Paul
~~~~
Microsoft MVP (Visual Basic)
 
Back
Top