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=Mic rosoft.Jet.OLED B.4.0;Data
Source=../../Book1.xls;Exten ded Properties=Exce l 8.0;");

connection.Open ();

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

connection.Clos e();

OleDbDataAdapte r dataAdapter = new OleDbDataAdapte r();
dataAdapter.Sel ectCommand = command;
DataSet dataSet = new DataSet();

dataAdapter.Fil l(dataSet);
Then OK!!!

2. But I can't update it from dataset to Excel by:
connection.Open ();
dataAdapter.Upd ateCommand = new OleDbCommand("U PDATE myRange1 SET FirstName
= @FirstName, LastName = @LastName", connection);
dataAdapter.Upd ate(this.dataSe t);
connection.Clos e();
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.Upd ateCommand = new OleDbCommand("U PDATE 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.Upd ate(this.dataSe t);
connection.Clos e();

That should work.

Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard. caspershouse.co m

"JacksonYin " <Ja************ @hotmail.com> wrote in message
news:%2******** ********@TK2MSF TNGP15.phx.gbl. ..

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

OleDbConnection connection = new
OleDbConnection (@"Provider=Mic rosoft.Jet.OLED B.4.0;Data
Source=../../Book1.xls;Exten ded Properties=Exce l 8.0;");

connection.Open ();

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

connection.Clos e();

OleDbDataAdapte r dataAdapter = new OleDbDataAdapte r();
dataAdapter.Sel ectCommand = command;
DataSet dataSet = new DataSet();

dataAdapter.Fil l(dataSet);
Then OK!!!

2. But I can't update it from dataset to Excel by:
connection.Open ();
dataAdapter.Upd ateCommand = new OleDbCommand("U PDATE myRange1 SET
FirstName = @FirstName, LastName = @LastName", connection);
dataAdapter.Upd ate(this.dataSe t);
connection.Clos e();
How can I do it???? :-(

[Brian Brown]

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.ExecuteNonQ uery();
cmd.CommandText = "UPDATE [Sheet1$] SET FirstName = 'Homer', LastName =
'Simpson' " +
" WHERE FirstName = 'Joe'";
cmd.ExecuteNonQ uery();
conn.Close();

[JacksonYin]

Thank you very much! ;-)

"JacksonYin " <Ja************ @hotmail.com> wrote in message
news:%2******** ********@TK2MSF TNGP15.phx.gbl. ..

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

OleDbConnection connection = new
OleDbConnection (@"Provider=Mic rosoft.Jet.OLED B.4.0;Data
Source=../../Book1.xls;Exten ded Properties=Exce l 8.0;");

connection.Open ();

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

connection.Clos e();

OleDbDataAdapte r dataAdapter = new OleDbDataAdapte r();
dataAdapter.Sel ectCommand = command;
DataSet dataSet = new DataSet();

dataAdapter.Fil l(dataSet);
Then OK!!!

2. But I can't update it from dataset to Excel by:
connection.Open ();
dataAdapter.Upd ateCommand = new OleDbCommand("U PDATE myRange1 SET
FirstName = @FirstName, LastName = @LastName", connection);
dataAdapter.Upd ate(this.dataSe t);
connection.Clos e();
How can I do it???? :-(