Update command to update single row in Excel using OLEdb and dataaset. (C#)

Question

Hello,
I am importing data from Excel an spreadsheet. I am only using OLEDB to read. But i am having trouble saving the result back to the original file. Below is my code so far.

string ExcelConnectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Mode=ReadWrite;" +
        "Extended Properties=\"Excel 8.0;HDR=No;IMEX=1\";",
        Server.MapPath("./Uploads/" + excelName));
    try
    {
        OleDbDataAdapter ExcelDataAdapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$A8:AZ67]", ExcelConnectionString);
        DataSet objDataSet = new DataSet();
        ExcelDataAdapter.Fill(objDataSet, "ExcelTable");
        for (int i = 0; i < objDataSet.Tables[0].Rows.Count; i++ )
        {
            Slab slb = new Slab();
            slb.ReceiveDate = objDataSet.Tables[0].Rows[i].Field<DateTime>(0);
            slb.VesselName = objDataSet.Tables[0].Rows[i].Field<string>(1);
            slb.Lot = Convert.ToInt16(objDataSet.Tables[0].Rows[i].Field<double>(2));
            slb.SlabSource = objDataSet.Tables[0].Rows[i].Field<string>(3);
            slb.CertificateNumber = objDataSet.Tables[0].Rows[i].Field<string>(4);

            //I think the next two lines do the same thing.
            objDataSet.Tables[0].Rows[i][4] = "message here";
            objDataSet.Tables[0].Rows[i].SetField<string>(51, "message here");
            ExcelDataAdapter.Update(objDataSet, "ExcelTable");
                  .............
        }

I get the following error :

Update requires a valid UpdateCommand when passed DataRow collection with modified rows.

The documentation shows update commands syntax similar to :

UPDATE table SET fieldID = 1, Description = 'nice' WHERE RegionID = 1 But that wont work for me, i need to update the current row in the for loop.

Any help apprecaited, Thanks

Answer 1

Wow.

I mean... wow.

I don't think that particular method of self-flagellation is supported past XP.

I'd highly suggest you drop OLEDB and pick up one of the many API's for dealing with excel files. You can find a bunch of them here: http://www.codeplex.com/site/search?projectSearchText=excel

I've used a couple and they worked pretty damn good. Alernatively, check out the OpenXML sdk if you plan on just supporting Office 2007+.