How do I insert cells using INSERT INTO & SET? (Excel oledb)

updating cell works:

    oledbconnect = new system.data.oledb.oledbconnection("provider=microsoft.ace.oledb.12.0;data source=c:\\__\\test.xls;extended properties='excel 12.0 xml;hdr=no;'");
    oledbconnect.open();
    oledbcmd.connection = oledbconnect;
    string stsheetname = "sheet2";
    string sql;
    sql = "update [" + stsheetname + "$a1:b1] set f1=1";
    oledbcmd.commandtext = sql;
    oledbcmd.executenonquery();

inserting cell not:

    sql = "insert [" + stsheetname + "$a2:b2] set f1=2'";
    oledbcmd.commandtext = sql;
    oledbcmd.executenonquery();

i understand inserting add new row ... doesn't work either.

    sql = "insert [" + stsheetname + "$] set f1=3";
    oledbcmd.commandtext = sql;
    oledbcmd.executenonquery();

---

bhs67

this works:

    oledbcmd.commandtext = "select * [" + stsheetname + "$]";
    datatable dtdatatable = new datatable();
    using (oledbdataadapter oledbadapter = new oledbdataadapter(oledbcmd))
        oledbadapter.fill(dtdatatable);

    string strownumber = (dtdatatable.rows.count + 1).tostring();
    string strowid = "a" + strownumber + ":b" + strownumber;
    oledbcmd.commandtext = "update [" + stsheetname + "$" + strowid + "] set f1=3, f2='z'";
    oledbcmd.executenonquery();

the dtdatatable fills worksheet contents ... number of rows plus 1 indicates next row add using update.

---

bhs67

Visual Studio Languages  ,  .NET Framework  >  Visual C#