How does the OleDbDataAdapter Update work?

[davepkz]

I know this is really basic but I am baffled trying to get the
DataAdapter Update to work.

Let's say I simply want to change all names "Smith" to "Jones" and I
want to use the DataAdapter and DataTable to do it.

So I try this:

// assume the OleDbConnection conn is already open
OleDbCommand cmd = new OleDbCommand("select lastname from employee",
conn);
OleDbDataAdapter data = new OleDbDataAdapter(cmd);

DataSet ds = new DataSet();
data.Fill(ds, "mydata");

DataTable dt = ds.Tables[0];
DataRow[] rows = dt.Select();

for (int i = 0; i < rows.Length; i++)
{
DataRow row = rows;
string curVal = row["lastname"].ToString();
if (curVal == "Smith")
{
row["lastname"] = "Jones";
rows = row;
}
}
data.Update(rows);

This doesn't work because it "requires a valid UpdateCommand"...
But I don't follow this at all. Sure, I could explicitly write an
update command like so...

OleDbCommand command = new OleDbCommand("UPDATE employee SET lastname
= 'Jones' WHERE lastname = 'Smith'", conn);
data.UpdateCommand = command;

....but then what's the point of allowing me to edit the DataRow and
having an Update() command in the first place?
I know I can do this simply and more efficiently with the
connection's ExecuteNonQuery, but for reasons that I can go into if
necessary I really need an approach like this where I iterate through
each record and programmatically review it before updating it.

Thanks
Dave

 

[Bob Barrows]

I know this is really basic but I am baffled trying to get the
DataAdapter Update to work.

There was no way for you to know it (except maybe by browsing through
some of the previous questions in this newsgroup before posting yours -
always a recommended practice) , but this (microsoft.public.data.ado) is
a classic (COM-based) ADO newsgroup. ADO.Net bears very little
resemblance to classic ADO so, while you may be lucky enough to find a
dotnet-knowledgeable person here who can answer your question, you can
eliminate the luck factor by posting your question to a group where
those dotnet-knowledgeable people hang out. I suggest
microsoft.public.dotnet.framework.adonet.

 

[Pavel Minaev]

I know this is really basic but I am baffled trying to get the
DataAdapter Update to work.

Let's say I simply want to change all names "Smith" to "Jones" and I
want to use the DataAdapter and DataTable to do it.

So I try this:

 // assume the OleDbConnection conn is already open
 OleDbCommand cmd = new OleDbCommand("select lastname from employee",
conn);
 OleDbDataAdapter data = new OleDbDataAdapter(cmd);

 DataSet ds = new DataSet();
 data.Fill(ds, "mydata");

 DataTable dt = ds.Tables[0];
 DataRow[] rows = dt.Select();

 for (int i = 0; i < rows.Length; i++)
 {
  DataRow row = rows;
  string curVal = row["lastname"].ToString();
  if (curVal == "Smith")
  {
   row["lastname"] = "Jones";
   rows = row;
  }
 }
 data.Update(rows);

This doesn't work because it "requires a valid UpdateCommand"...
But I don't follow this at all.  Sure, I could explicitly write an
update command like so...

 OleDbCommand command = new OleDbCommand("UPDATE employee SET lastname
= 'Jones' WHERE lastname = 'Smith'", conn);
 data.UpdateCommand = command;

...but then what's the point of allowing me to edit the DataRow and
having an Update() command in the first place?

Look at this from the perspective of the DataAdapter. It has to
generate an UPDATE (it's not going to update rows in the table by
magic, right?). For the update, it will need some way to filter the
rows that need to be updated (for WHERE clause of the UPDATE) - such
as, well, a primary key. And your SELECT statement was:

SELECT lastname FROM employee

In other words, the DataTable produced from it only includes a bunch
of strings, which aren't in any way linked to specific rows in the
original table. There's nothing a DataAdapter can do about this. But
it's very easy to fix it - just make sure that your SELECT statement
includes the PK field(s) for the table

This behavior is actually documented if you look up documentation for
DbDataAdapter.UpdateCommand on MSDN:

"During Update, if this property is not set and primary key
information is present in the DataSet, the UpdateCommand will be
automatically generated"