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.

Expand|Select|Wrap|Line Numbers

  
 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....

Expand|Select|Wrap|Line Numbers

 
    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

insertAlias

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

--insertAlias
MODERATOR

insertAlias

Try using a OleDbCommandBuilder to generate your update statement.

Update excel file with oledb

re: Update excel file with oledb

re: Update excel file with oledb

re: Update excel file with oledb

re: Update excel file with oledb

Similar topics

More channels