> 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?

On 2006-04-17 14:59:08 -0400, "Chris" <chris.marti***@cns-service.com> said:
>
> 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

> On Mon, 17 Apr 2006 14:20:15 -0400, jef <trinig***@mac.com> wrote:
>
> ¤ 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)

> On Wed, 19 Apr 2006 00:08:34 -0400, jef <trinig***@mac.com> wrote:
>
> ¤ On 2006-04-18 14:41:06 -0400, Paul Clement ¤
> <UseAdddressAtEndofMess***@swspectrum.com> 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)