Update excel file with oledb

[msjonathan]

Hej hej,

I am trying to write an SQL update statement where I want to update an Excel file. But my Update statement is not valid, I have to use set .... But I do not know the columns in the Excel file, those can be different each time.
First I read an excel file with this code: http://codehill.com/2009/01/reading-...s-using-oledb/

I change some things in the DataTable and then I want to write to a copy of the first excel file.

 OleDbConnection con;
 
         if (isOpenXMLFormat)
            //read a 2007 file  
            ConnectionsString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                 pFileName + ";Extended Properties=\"Excel 8.0;HDR=YES;\"";
         else
            //read a 97-2003 file  
            ConnectionsString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
                pFileName + ";Extended Properties=Excel 8.0;";
         con = new OleDbConnection(ConnectionsString);
 
         OleDbDataAdapter cmd;
         cmd = new OleDbDataAdapter("Update * [" + pWorksheetName + "$]", con);
         con.Open();
         cmd.Update(pData);
         con.Close();

Has anyone an idea how to solve this?
EDIT: I posted this already but they advised to post it under ASP.NET

Greetz Jonathan

[Frinavale]

No where in this question is there anything related to ASP.NET?

I'm sorry but what type of application are you developing? An ASP.NET application/website? A windows desktop application?


Anyways, it would help if you posted the error message that you're getting.

Have you tried something like....

    String sConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+Server.MapPath("~/Temp/Book1.xls;")+Extended Properties='Excel 8.0;HDR=NO'";
        OleDbConnection objConn = new OleDbConnection(sConnectionString);
        objConn.Open();
        OleDbCommand objCmdSelect = new OleDbCommand("UPDATE [Sheet1$A2:A2] SET F1=123456", objConn);
        objCmdSelect.ExecuteNonQuery();
        objCmdSelect = new OleDbCommand("UPDATE [Sheet1$A4:A4] SET F1='hello'", objConn);
        objCmdSelect.ExecuteNonQuery();
        objCmdSelect = new OleDbCommand("UPDATE [Sheet1$A5:A5] SET F1='goodby'", objConn); 
        objCmdSelect.ExecuteNonQuery();

-Frinny

[msjonathan]

I am using c#, somebody advised me to post it under asp.net first I posted it under SQL.
I retrieve a dataset from an excel file, do something with the data, add columns.
My problem is I can't write a normal update statement because it should update different excel files (the excel file defined by the user).
I am looking for a dynamic update statement, that updates all rows that are filled.
Or does anybody now how to write a dynamic insert statement?

Thanks in advance,

Jonathan

[Curtis Rutland]

I've moved this threat to the General .NET forum. I think it's a better place for it.

--insertAlias
MODERATOR

[Curtis Rutland]

Try using a OleDbCommandBui lder to generate your update statement.