Excel ADO.Net Hell :S

  • Thread starter Thread starter Kevin Harrison
  • Start date Start date
K

Kevin Harrison

I'm having problems populating an Excel spreadsheet using ADO.Net's OLEDB
provider. The issue is that inserting strings with full stops in causes the
next subsequent insert to fail.



E.g.

INSERT INTO [Template$A8:A8] VALUES ('This sentence has a full stop.')

INSERT INTO [Template$A9:A9] VALUES ('This sentence does not have a full
stop')



Executing either one of these statements does not cause an error.



Executing both in a row, causes an error to be reported at the location of
the full stop (although it is then purported as being a # character).



Closing the OLE DB Connection between the INSERTs does not resolve the
issue - even if I rename the Excel spreadsheet and then reopen it.



My code uses OleDbConnection and OleDbCommand objects to perform the
inserts.



Any ideas?
 
My problem with implementing other methodologies is that:
- the component needs to be authored in .Net
- I'm using an excel template, and only need to populate a subsection -
so cannot use an implicit a schema for a dataset to be able to determine the
update and insert command syntax. I cannot use an explicit schema, because
I cannot ascertain the datatypes of the cells (!).

The issue with the fullstop does appear related to the cell format.

Thanks.
Kev.

keepitcool said:
I think there's more efficient ways of populating Excel with ADO

Erlandsen Data Consulting
http://www.erlandsendata.no/english/vba/adodao/


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >


Kevin Harrison said:
I'm having problems populating an Excel spreadsheet using ADO.Net's
OLEDB provider. The issue is that inserting strings with full stops in
causes the next subsequent insert to fail.



E.g.

INSERT INTO [Template$A8:A8] VALUES ('This sentence has a full stop.')

INSERT INTO [Template$A9:A9] VALUES ('This sentence does not have a
full stop')



Executing either one of these statements does not cause an error.



Executing both in a row, causes an error to be reported at the
location of the full stop (although it is then purported as being a #
character).



Closing the OLE DB Connection between the INSERTs does not resolve the
issue - even if I rename the Excel spreadsheet and then reopen it.



My code uses OleDbConnection and OleDbCommand objects to perform the
inserts.



Any ideas?
Click to expand...
Click to expand...
 
Whlist I haven't got any further, I've noted that the same problem also
occurs with #s.

Is there any way of escaping #s or full-stops in strings in OLEDB insert
statements?



keepitcool said:
I think there's more efficient ways of populating Excel with ADO

Erlandsen Data Consulting
http://www.erlandsendata.no/english/vba/adodao/


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >


Kevin Harrison said:
I'm having problems populating an Excel spreadsheet using ADO.Net's
OLEDB provider. The issue is that inserting strings with full stops in
causes the next subsequent insert to fail.



E.g.

INSERT INTO [Template$A8:A8] VALUES ('This sentence has a full stop.')

INSERT INTO [Template$A9:A9] VALUES ('This sentence does not have a
full stop')



Executing either one of these statements does not cause an error.



Executing both in a row, causes an error to be reported at the
location of the full stop (although it is then purported as being a #
character).



Closing the OLE DB Connection between the INSERTs does not resolve the
issue - even if I rename the Excel spreadsheet and then reopen it.



My code uses OleDbConnection and OleDbCommand objects to perform the
inserts.



Any ideas?
Click to expand...
Click to expand...
 
Finally sorted my problem, had to specify the schema for ado.net explicitly,
and interrogate the datatable to extract the default column names, but I'm
finally cooking with gas :D

Kevin Harrison said:
Whlist I haven't got any further, I've noted that the same problem also
occurs with #s.

Is there any way of escaping #s or full-stops in strings in OLEDB insert
statements?



keepitcool said:
I think there's more efficient ways of populating Excel with ADO

Erlandsen Data Consulting
http://www.erlandsendata.no/english/vba/adodao/


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >


Kevin Harrison said:
I'm having problems populating an Excel spreadsheet using ADO.Net's
OLEDB provider. The issue is that inserting strings with full stops in
causes the next subsequent insert to fail.



E.g.

INSERT INTO [Template$A8:A8] VALUES ('This sentence has a full stop.')

INSERT INTO [Template$A9:A9] VALUES ('This sentence does not have a
full stop')



Executing either one of these statements does not cause an error.



Executing both in a row, causes an error to be reported at the
location of the full stop (although it is then purported as being a #
character).



Closing the OLE DB Connection between the INSERTs does not resolve the
issue - even if I rename the Excel spreadsheet and then reopen it.



My code uses OleDbConnection and OleDbCommand objects to perform the
inserts.



Any ideas?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top