How to update data from DataSet to Excel

[JacksonYin]

1. I can fill data from Excel to DataSet like this:

OleDbConnection connection = new
OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=../../Book1.xls;Extended Properties=Excel 8.0;");

connection.Open();

OleDbCommand command =new OleDbCommand("SELECT * FROM myRange1",
connection);

connection.Close();

OleDbDataAdapter dataAdapter = new OleDbDataAdapter();
dataAdapter.SelectCommand = command;
DataSet dataSet = new DataSet();

dataAdapter.Fill(dataSet);


Then OK!!!

2. But I can't update it from dataset to Excel by:
connection.Open();
dataAdapter.UpdateCommand = new OleDbCommand("UPDATE myRange1 SET FirstName
= @FirstName, LastName = @LastName", connection);
dataAdapter.Update(this.dataSet);
connection.Close();


How can I do it???? :-(

 

[Nicholas Paldino [.NET/C# MVP]]

JacksonYin,

OLEDB doesn't support named parameters, only positional ones. I believe
you have to do something like this:

connection.Open();
dataAdapter.UpdateCommand = new OleDbCommand("UPDATE myRange1 SET FirstName
= ?, LastName = ?", connection);

// At this point, add the parameters, making sure that the firstname
parameter is
// added first, and the last name parameter is added second.

dataAdapter.Update(this.dataSet);
connection.Close();

That should work.

Hope this helps.

 

[Guest]

JacksonYin,

As an alternate method you can create an OleDbCommand object and use
ExecuteNonQuery against the spreadsheet to insert/update/delete. I have
posted some sample code below.

I hope this helps.
---------------------

OleDbConnection conn = new OleDbConnection(strConn);
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
conn.Open();
cmd.CommandText = "Insert into [Sheet1$] (FirstName, LastName)" +
" values ('Fred', 'Flintstone')";
cmd.ExecuteNonQuery();
cmd.CommandText = "UPDATE [Sheet1$] SET FirstName = 'Homer', LastName =
'Simpson' " +
" WHERE FirstName = 'Joe'";
cmd.ExecuteNonQuery();
conn.Close();

 

[JacksonYin]

Thank you very much! ;-)