Update problem ??

[Gary]

I'm facing a problem while updating a ADO recordset after modifying its

records using the Dataset( ADO.Net).

I'm calling a method of a COM component from C# code. The method returns a

disconnected ADO recordset object which I use to fill the Dataset using the

OleDbDataAdapter.Fill() method. Then I modify the records using

Dataset.Tables[].Rows[].[FiledName].

I want to get these modified records reflected back into the ADO recodset

as I want to call another COM method with the ADO recordset as an input

parameter.

To do this, when I use the OleDbDataAdapter.Update(Dataset, TableName)

method, it throws an exception that says "Update requires a valid

Updatecommand". MSDN documentation about the Fill method is as follows:

"Filling a DataSet with the contents of an ADO object is a one-way

operation. That is, data can be imported from the ADO Recordset or Record

into the DataSet, but any updates to the data must be handled explicitly.

However, once you have populated a DataSet with data from an ADO object,

you can resolve changes back to the data source using a DataAdapter, and

you can also write data as XML."

In this case, what does it mean by one-way operation? If it is a one-way

operation, how do we resolve the changes back to the data source which is a

ADO recordset in this case?

TIA,

Gary

 

[Thomas Weingartner]

Hi Gary

The error message reflects exactly what the error is, but it is hard to
understand how to handle it without using the designer magic of ADO.NET
DataAdapter, Command and Connection.


The DataAdapter must have a Command object assigned to:

SqlCommand command = CreateCommand("SELECT * FROM myTable");
SqlDataAdapter myDataAdapter = new SqlDataAdapter(command);


Then create a CommandBuilder assigned to the DataAdapter and call
DataAdapter.Update:

// Command Builder registers itself as a listener for RowUpdating events
// that are generated by the DataAdapter before the update to the database
// will be done.
SqlCommandBuilder myCB = new SqlCommandBuilder(myDataAdapter);

int affectedRows = myDataAdapter.Update(DataSet);


This way the CommandBuilder builds the commands for update, delete and insert
itself.

I hope that helps.

Thomas