[ADVANCED-DOTNET] Re 1. ERROR [HY000] [Microsoft][ODBC Excel Driver] Operation must use a

[SteveC]

Yan Ping, the MS Jet OLEDB driver has been able to read/write Excel for
a "long time" now, you will get better mileage if you use that instead
of ODBC:

        string excelConn =
                @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=  "
                +
"E:\\src\\projects\\DbMiniLoader\\SampleSourceData\\sampleexcel01.xls"
                + ";Mode=Read;Extended Properties=\"Excel 8.0;HDR=YES\""
;

        string excelCmd1 =
                @"Select * From [Sheet1$]";

        string excelCmd2 =
                @"Create Table NuWorkSheet (col1 char(255), col2
char(255), col3 date)"

    string excelCmd3 = "Insert Into NuWorkSheet " +
        " ( col1 , col2 , col3 ) " +
        :values ('Steve', 'Demo', '2003.06.16')" //YMMV on the date
format

The second extended property HDR=YES in the connection string means that
row1 contains column names.





---orig

>Hi all,
>
>I have a very simple Excel 2002 file called "Target.xls". The file has
just
>1 worksheet called "Target". Inside the worksheet, I have defined 3
columns