Ole DB Jet 4.0 Provider corrupts recipient Excel database predicta

[Guest]

VS2005 C# 2.0

I'm using ADO.NET and the Jet 4.0 OLE DB provider to append a row to an
Excel spreadsheet.

I have a data table of type System.Data.DataTable, and a table adapter of
type System.Data.OleDb.OleDbDataAdapter to fill the data table. These are
generated by the VS2005 dataset designer.


My connection string, created using the Data Links wizard is

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\FootAnalNovel.xls;
Extended Properties="Excel 8.0;HDR=Yes";Persist Security Info=False


1. faAdapter.Fill() to fill the table from the worksheet
2. Create a new empty row for the table.
3. Set values for elements of the row.
4. Add the row to the table.
5. Call faAdapter.Update() to send results back to the spreadsheet

In the target spreadsheet I observe:
1. The row was added. The first column was autoincremented as desired.
2. All numeric columns in the sheet, including dates, are correct.
3. The header line has not been modified - that is correct.
4. All text columns in the table have been corrupted. But, in a very
particular way: they are filled in row by row by text that was originally in
the sheet, in the order that it appeared originally in the sheet. Both
originally empty cells and filled cells are overwritten.

Thanks in advance for your help.
Jim

 

[Guest]

A follow-up to my own posting.

I finally got this working.

My target Excel worksheet had been created by importing a table from a VFP
database. Apparently there was something wrong with that.

I tried creating the Excel worksheet directly, creating the same columns
that were in the VFP worksheet.

This now works without any corruption of the Excel worksheet.

I don't know what was wrong with the Excel sheet created by importing from
VFP.

Any thoughts?

Jim

 

[Paul Nakata]

A follow-up to my own posting.

I finally got this working.

My target Excel worksheet had been created by importing a table from a VFP
database. Apparently there was something wrong with that.

I tried creating the Excel worksheet directly, creating the same columns
that were in the VFP worksheet.

This now works without any corruption of the Excel worksheet.

I don't know what was wrong with the Excel sheet created by importing from
VFP.

Any thoughts?

Jim

I am dealing with almost the exact same problem right now. I'm not
exporting from VFP, but I am experiencing all the other symptoms. I
can't seem to figure out what is wrong with the original spreadsheet
that would cause this. I've tried a ton of different modes in my
connection string, but no luck.

I'd be very curious to find out what is causing your issue if you
happen to find a root cause.

 

[Guest]

Hi Paul,

Did you have the same symptoms: numeric fields were all fine, but all text
fields were overwritten by text from elsewhere in the spreadsheet?

I honestly don't know why this finally works. The original spreadsheet was
created in Excel by importing a table from a vfp database using OLE DB
provider for Visual FoxPro. I did that because my application is intended to
be provider independent, so I needed to have the Excel sheet work the same
was as the VFP version.

Aside from this import question, one thing that is different between the two
is that the sheet derived by importing from the VFP table had some empty
cells. When I manually created the sheet that finally worked, there were no
empty cells. I could go back and delete the content of some of the text cells
and test it again and see if the overwriting happens again.

Also, I was careful in Excel to select each column and indicate the type of
data, e.g. numeric, date, or text. I don't know if this did anything.

Anyone else have any ideas?

Jim