none
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 http://support.microsoft.com/kb/306023/en-us#12.
    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