OLEDB Jet writing to Excel clobbers all text cells and other sheet

[Guest]

I'm developing a Provider Independent Data Access program using ADO.NET and
Ole DB Providers in VS2005.

I am using OLE DB Provider for Visual FoxPro for the VFP data source, and
the Jet 4.0 OLE DB Provider fot the Excel source.

The program works fine with the VFP data source.

However, I am getting lots of problems with my Excel version of the source.

Basically, I am working with three spreadsheets in an Excel workbook. My
program reads items from the first two sheets, and writes results into the
third sheet. (It gets a patient ID from the first sheet, uses that to get a
list of visits to the lab from the 2nd sheet. The user selects a vist, and
the patientid and visitid get included in the row of data added to the third
sheet.)

I created these sheets by using Excel to import the data from the VFP
database using the OLE DB Provider for Visual FoxPro.

The spreadsheets are Excel 97-2003 format.

I have created named ranges for the three sheets to allow them to work as
data sources.

I am having two problems that seem unrelated:

1. I am adding one row to the third sheet. The program seems to read the
data correctly from the first two sheets, and write the new row in the third
sheet without any runtime errors. However, when I close the program and look
at the Excel sheet I find that all of the cells in the sheet, including the
header line (line 1) that contain text now contaim #VALUE!. Cells that
contain numbers, dates, or boolean values appear to be OK. The row that I was
adding is there too.

2. The other two sheets have gotten corrupted. The header line of the first
sheet is intact, but the rest of the sheet has the data scattered about. The
second sheet has the cells of its header line replaced with #VALUE! Cells
containing numbers, dates or booleans are OK, but cells containing text now
all contain #VALUE!

The code for the dataset, datatables, tableadapters, etc was all generated
by the VS2005 Data Sources wizard. There is over 10,000 lines of that code,
so can't replicate it here.

In the dataset, the fields that will contain text are declared of type
System.String.

The fact that the update of the 3rd sheet is somehow clobbering the first
sheet sounds like an errant pointer. But, maybe it has something to do with a
mismatch in character width 8 vs 16?

I would appreciate any suggestions about what is causing this mess.

Thanks,
Jim

 

[Guest]

A follow-up to my original posting:

1. Here is the connection string I am using:

(note that since for testing I am hardwiring this in the code, I have
included escape sequences for the embedded quote characters).

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\\Program
Development\\FootAnalNovel\\Data\\XL
Database\\FOOTANALNOVEL-WoundDB.xls;Extended Properties=\"Excel
8.0;HDR=Yes\"";

2. I modified my program so that it doesn't fill the first two data tables
from the two worksheets. That means that I don't actually use those two
sheets at all. The program still appends a record to the third sheet. I get
the same corruption of the three sheets that I described in the initial
posting.

3. When I originally posted this, the names of the sheets and the names of
the Excel regions were the same. I went back and renamed the sheets with
different names than the regions defined on them. The regions still have
their original names. The program is using the names on the regions. When I
run the program I observed the same corruption of all three sheets that I
described in my initial posting.

Thanks again for any help.

Jim