Excel, ADO.NET, INSERT INTO, but do parameters cause problems? RRS feed

  • Question

  • Hi,

    I'm exporting data to an Excel worksheet using ADO.NET, basically following the examples found in
    The data is dynamic so the worksheet layout is only known run-time although it always is a table.


    Everything works fine if I run-time build a query like:

       INSERT INTO [Sheet1$] (F1, F2) values ('111', 'ABC').


    But then I changed to parameters:

       INSERT INTO [Sheet1$] (F1, F2) values (@F1, @F2),

    and of course adding the corresponding parameters to an OleDBCommand.


    Now if there are more than +/- 20 records to be written it only works occasionally as it should. 9 out of 10 times the resulting *.xls file cannot be opened by Excel. Excel gives a message like "acces denied for temp.xls" (the original is in dutch :-)).
    I need to close my application explicitly before Excel can open the file. After that the worksheet is just as it should be.


    I tried deleting the previous Excel file first, waiting a few minutes, changing datatypes, minimizing columncounts, simplifying table contents, rebuilding the situation in a different test application. All to no avail.


    Has anyone got an idea?


    Regards, Tonn



    Friday, July 20, 2007 2:03 PM