CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2009
    Posts
    3

    excel & oledb insert

    I'v write the following code to insert into excel 2007 file, but excel doesn't open *.xlsx file....
    after that i change file extension to *.xls ...exel opens file...but warn about "Number stored as text" for all my data....


    OleDbConnection baglanti = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Excel 12.0;HDR=YES;");

    baglanti.Open();

    string createTableScript = "CREATE TABLE newTable(a1 INT,a2 INT,a3 INT)";

    OleDbCommand cmd = new OleDbCommand(createTableScript, baglanti);

    cmd.ExecuteNonQuery();
    .
    .
    .

    string insertScript = "insert into [newTable$] (a1,a2,a3) values (" + (i + 1).ToString() + "," + (j + 1).ToString() + "," + ((int)p).ToString() + ")";

    cmd = new OleDbCommand(insertScript, baglanti);
    cmd.ExecuteNonQuery();

  2. #2
    Join Date
    Aug 2009
    Posts
    3

    Re: excel & oledb insert

    excume me

    i'v resolved problem with xlsx file...with this connection string

    baglanti = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0 xml;HDR=YES;\"");

    but warn about "Number stored as text" still persist...

  3. #3
    Join Date
    Jan 2002
    Location
    Scaro, UK
    Posts
    5,940

    Re: excel & oledb insert

    Try using parameterized queries.

    Darwen.
    www.pinvoker.com - PInvoker - the .NET PInvoke Interface Exporter for C++ Dlls.

  4. #4
    Join Date
    Aug 2009
    Posts
    3

    Re: excel & oledb insert

    I'v tried that but same problem exist...

    I found that setting IMEX=1 in connection string will resolved this problem....but this leads to

    "file doesnot exist" error...


    is there a way to create a new excel file by oledb when setting IMEX=1?

    i tried creating excel file using ExcelApplication...but this leads to incompatibility with above code
    and doesnt work too...

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  





Click Here to Expand Forum to Full Width

Featured