Exporting data to excel not working.....

19 Dec 2006 9:42 PM

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.

20 Dec 2006 12:08 AM

Spam Catcher

"Izzy" <israel.rich***@gmail.com> wrote in news:1166564570.886639.286220

@a3g2000cwd.googlegroups.com:

> 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.

20 Dec 2006 1:33 AM

scorpion53061

see

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

Izzy wrote:
Show quoteHide quote

> 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.

20 Dec 2006 3:00 PM

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.

scorpion53061 wrote:
Show quoteHide quote

> see
>
> http://www.kjmsolutions.com/datasetarray.htm
>
>
> Izzy wrote:
> > 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.