I'm working on my Provider Independent Data Access program using OLE DB.
I'm using 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