Writing to an Excel File [C#]

[Shaitan00]

Given the following code that connects to an Excel File (as a DB) and reads (works great) and writes (doesn't work - this is the problem).

 

The Excel file itself (C:\myData.XLS Sheet1) looks like this:

row(1): Client1 Assignment1 RUNNING

row(2): Client1 Assignment2 PAUSED

row(3): Client2 Assignment1 FINISHED

(spaces delimited between columns)

 

using System.Data;
using System.Data.OleDb;

DataSet DS;
OleDbDataAdapter MyCommand;
OleDbConnection MyConnection;

MyConnection = new OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; data source=C:\myData.XLS; Extended Properties=Excel 8.0;");

// Select the data from Sheet1 of the workbook.
MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [sheet1$]", MyConnection);

// READ from Excel DB
DS = new DataSet();
MyCommand.Fill(DS);
MyConnection.Close();

// Write to Excel DB
MyCommand = new System.Data.OleDb.OleDbDataAdapter(insert into [sheet1$] ([CLIENTS], [ASSIGNMENTS], [sTATUS]) values( '" + cbClient.Text + "', '" + cbAssignment.Text + "', 'PAUSED')", MyConnection);

 

So as previouslly mentioned this code READS values from my Excel File correctly (DataSet ds is properly populated) HOWEVER it doesn't seem to WRITE (the INSERT INTO). Don't get me wrong - this doesn't generate any errors and seems to execute fine BUT it doesn't actually make/save the changes to the EXCEL (.xls) file. IS there something I am missing? Do I need to confirm/save the changes? Am I missing something with my MyCommand.?? Commit changes?

 

Any help/hints would be GREATLY appreciated.. Thanks,

[mark007]

Try:

 

using System.Data;
using System.Data.OleDb;

DataSet DS;
OleDbDataCommand MyCommand;
OleDbDataAdapter MyAdapter;
OleDbConnection MyConnection;

MyConnection = new OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; data source=C:\myData.XLS; Extended Properties=Excel 8.0;");

// Select the data from Sheet1 of the workbook.
MyAdapter = new System.Data.OleDb.OleDbDataAdapter("select * from [sheet1$]", MyConnection);

// READ from Excel DB
DS = new DataSet();
MyAdapter.Fill(DS);
MyConnection.Close();

// Write to Excel DB
MyCommand = new System.Data.OleDb.OleDbCommand("insert into [sheet1$] ([CLIENTS], [ASSIGNMENTS], [sTATUS]) values( '" + cbClient.Text + "', '" + cbAssignment.Text + "', 'PAUSED')", MyConnection);
MyCommand.ExecuteNonQuery();

 

:)

Edited September 6, 2005 by mark007

[Machaira]

You need to call Update on the DataAdapter I believe.