Excel ADO.Net Hell :S
 

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?

Excel ADO.Net Hell :S
 

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" wrote:

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?

Excel ADO.Net Hell :S
 

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" wrote in message
...
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" wrote:

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?

Excel ADO.Net Hell :S
 

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" wrote in message
...
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" wrote:

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?

Excel ADO.Net Hell :S
 

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" wrote in message
...
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" wrote in message
...
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" wrote:

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?