469,360 Members | 1,749 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,360 developers. It's quick & easy.

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
  1.  
  2.  OleDbConnection con;
  3.  
  4.          if (isOpenXMLFormat)
  5.             //read a 2007 file  
  6.             ConnectionsString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
  7.                  pFileName + ";Extended Properties=\"Excel 8.0;HDR=YES;\"";
  8.          else
  9.             //read a 97-2003 file  
  10.             ConnectionsString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
  11.                 pFileName + ";Extended Properties=Excel 8.0;";
  12.          con = new OleDbConnection(ConnectionsString);
  13.  
  14.          OleDbDataAdapter cmd;
  15.          cmd = new OleDbDataAdapter("Update * [" + pWorksheetName + "$]", con);
  16.          con.Open();
  17.          cmd.Update(pData);
  18.          con.Close();
  19.  
  20.  
Has anyone an idea how to solve this?
EDIT: I posted this already but they advised to post it under ASP.NET

Greetz Jonathan
Jan 29 '10 #1
4 10278
Frinavale
9,735 Expert Mod 8TB
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
  1.     String sConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+Server.MapPath("~/Temp/Book1.xls;")+Extended Properties='Excel 8.0;HDR=NO'";
  2.         OleDbConnection objConn = new OleDbConnection(sConnectionString);
  3.         objConn.Open();
  4.         OleDbCommand objCmdSelect = new OleDbCommand("UPDATE [Sheet1$A2:A2] SET F1=123456", objConn);
  5.         objCmdSelect.ExecuteNonQuery();
  6.         objCmdSelect = new OleDbCommand("UPDATE [Sheet1$A4:A4] SET F1='hello'", objConn);
  7.         objCmdSelect.ExecuteNonQuery();
  8.         objCmdSelect = new OleDbCommand("UPDATE [Sheet1$A5:A5] SET F1='goodby'", objConn); 
  9.         objCmdSelect.ExecuteNonQuery();
  10.  
-Frinny
Feb 1 '10 #2
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
Feb 2 '10 #3
Curtis Rutland
3,256 Expert 2GB
I've moved this threat to the General .NET forum. I think it's a better place for it.

--insertAlias
MODERATOR
Feb 2 '10 #4
Curtis Rutland
3,256 Expert 2GB
Try using a OleDbCommandBuilder to generate your update statement.
Feb 2 '10 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Peter Cushing via .NET 247 | last post: by
1 post views Thread by Moomin Ma | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.