Using OLEDB to write to excel file.. problems

This is really frustrating..

I have an blank excel workbook (97-2003 version) ie .xls which just has some formatting in it and two worksheets Results and Other

I am trying to write something into the cells of the worksheet Results using two methods :-

1)

string sql = null;

System.Data.OleDb.OleDbConnection MyConnection;

MyConnection = new System.Data.OleDb.OleDbConnection(@"provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:\\test\\ResultsTest.xls'; Extended Properties='Excel 8.0;HDR=No;'");

MyConnection.Open();

System.Data.OleDb.OleDbDataAdapter MyAdapter = new System.Data.OleDb.OleDbDataAdapter("select * from [Results$]", MyConnection);

System.Data.DataSet DS = new DataSet();

MyAdapter.Fill(DS);

MyConnection.Close();

MyAdapter.InsertCommand = new System.Data.OleDb.OleDbCommand("insert into [Results$] (F1,F2) Values ('test','peter'))", MyConnection);

MyAdapter.Update(DS);

and

2)

sql = " insert into [Results$] (F1,F2) Values ('test','peter') ";

System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(sql, MyConnection);

cmd.ExecuteNonQuery();

cmd.Dispose();

MyConnection.Close();

Both these do not error but when I open up the spreadsheet nothing has been saved..

What am I doing wrong !!

thanks

Does the code you posted here represent what your code really is? I will assume it does. If my comments are not relevant because your code shown here is not what your code actually is, then please excuse the misunderstanding.

You are not using try/catch, therefore "these do not error" might not be accurate. Try using try/catch to ensure you are getting all the errors.

You open the connection, then use it to fill the adapter, then close the connection, then try to insert, correct? If that code is accurate, then the problem is obvious. Also note that MyAdapter.InsertCommand is a property but it does not execute the command. MyAdapter.Update executes the command for rows that have been added to the adapter but you don't have code to add a row to the adapter.

I don't understand what the "2)" code is.

Please have a look at the articles in this web site. Go to the home page and search for OleDbDataAdapter. Also look for articles about SqlDataAdapter, since the two classes are very much alike.

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Sam Hobbs15y ago
Does the code you posted here represent what your code really is? I will assume it does. If my comments are not relevant because your code shown here is not what your code actually is, then please excuse the misunderstanding.

You are not using try/catch, therefore "these do not error" might not be accurate. Try using try/catch to ensure you are getting all the errors.

You open the connection, then use it to fill the adapter, then close the connection, then try to insert, correct? If that code is accurate, then the problem is obvious. Also note that MyAdapter.InsertCommand is a property but it does not execute the command. MyAdapter.Update executes the command for rows that have been added to the adapter but you don't have code to add a row to the adapter.

I don't understand what the "2)" code is.

Please have a look at the articles in this web site. Go to the home page and search for OleDbDataAdapter. Also look for articles about SqlDataAdapter, since the two classes are very much alike.