Exporting data to excel not working.....

[Izzy]

Here is the code.

Dim conExcel As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" & ExcelPath & ";Extended Properties=""Excel 8.0;HDR=NO;"""
Dim cmdOLE As OleDb.OleDbCommand
Dim conOLE As OleDb.OleDbConnection
Dim strOLE As String


cmdOLE = New OleDb.OleDbCommand
conOLE = New OleDb.OleDbConnection(conExcel)
conOLE.Open()


With cmdOLE
.CommandText = "INSERT INTO [InvoiceAgingByCompany$] (F1,
F2) VALUES('HI','by')"
.CommandType = CommandType.Text
.Connection = conOLE
.ExecuteNonQuery()
End With


I'm trying to insert into a blank sheet and the sheet name is correct.
I read somewhere that if you specify "HDR=NO" in the connection string
it would automatically name your columns "F1" , "F2" and so on.


The error I get is "The INSERT INTO statement contains the following
unknown field name: 'F2'. Make sure you have typed the name correctly,

and try the operation again."


If I remove the "F2" field and value the statement completes just fine.



Any ideas would be greatly appreciated.

 

[Guest]

Any ideas would be greatly appreciated.

I usually export to CSV - much more compatible and easier to do

Take a look at FileHelpers project on SourceForge.

 

[scorpion53061]

see

http://www.kjmsolutions.com/datasetarray.htm

 

[Izzy]

Thank you Scorpion, that will work great.

Spam Catcher - I usually write to CSV also when a power user will be
manipulating the data. In this case however it needs to be
pre-formatted. Thanks for the response.

see

http://www.kjmsolutions.com/datasetarray.htm

Here is the code.

Dim conExcel As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" & ExcelPath & ";Extended Properties=""Excel 8.0;HDR=NO;"""
Dim cmdOLE As OleDb.OleDbCommand
Dim conOLE As OleDb.OleDbConnection
Dim strOLE As String


cmdOLE = New OleDb.OleDbCommand
conOLE = New OleDb.OleDbConnection(conExcel)
conOLE.Open()


With cmdOLE
.CommandText = "INSERT INTO [InvoiceAgingByCompany$] (F1,
F2) VALUES('HI','by')"
.CommandType = CommandType.Text
.Connection = conOLE
.ExecuteNonQuery()
End With


I'm trying to insert into a blank sheet and the sheet name is correct.
I read somewhere that if you specify "HDR=NO" in the connection string
it would automatically name your columns "F1" , "F2" and so on.


The error I get is "The INSERT INTO statement contains the following
unknown field name: 'F2'. Make sure you have typed the name correctly,

and try the operation again."


If I remove the "F2" field and value the statement completes just fine.



Any ideas would be greatly appreciated.