Update using OleDbDataAdapter and DataSet

[Guest]

I am having difficulty getting an Update (MS Access) to work correctly using
a OleDbDataAdapter, DataSet, and parameters.

I add the parameters, and must have a default value for the values I want to
SET in the update (I use the current time, so I can see any changes). I then
attempt to change a value in a DataRow of the DataSet (the DataSet is updated
correctly). However, when I attempt to update using the DataAdapter, I
always get the default value I set earlier, never the new value in the
DataSet.

Here is my code:
// BEGIN CODE SNIPPET
OleDbCommand MyValueCommand =
new OleDbCommand("SELECT * FROM MyValues", Conn);

OleDbDataAdapter ValueAdapter = new OleDbDataAdapter(MyValueCommand);

ValueAdapter.UpdateCommand = new OleDbCommand("UPDATE MyValues " +
"SET SomeString=? WHERE IDString=?", Conn);
ValueAdapter.UpdateCommand.Parameters.Add("@SomeString",
OleDbType.VarChar);
// If I don't add this, I get an error "Paremeter ?_1 has no default value'
ValueAdapter.UpdateCommand.Parameters["@SomeString"].Value =
DateTime.Now.ToString("T");

OleDbParameter KeyParam =
ValueAdapter.UpdateCommand.Parameters.Add("@IDString", OleDbType.VarChar);
KeyParam.SourceColumn = "IDString";
KeyParam.SourceVersion = DataRowVersion.Original;

DataSet ds = new DataSet();
ValueAdapter.Fill(ds, "MyValues");

foreach (DataRow row in ds.Tables["MyValues"].Rows)
{
OleDbCommand MyComm =
new OleDbCommand("SELECT * FROM AnotherTable " +
"WHERE IDString=\"" + row["IDString"] + "\"", Conn);
OleDbDataReader MyReader = MyComm.ExecuteReader();

if (MyReader.HasRows)
{
MyReader.Read();
object [] Values = new object[ MyReader.FieldCount ];
MyReader.GetValues(Values);
// Update the data based on data from AnotherTable
row["SomeString"] = UpdateSomeString(Values);
}
MyReader.Close();
ValueAdapter.Update(ds, "MyValues");
}
// END CODE SNIPPET

Thanks,
PAGates

 

[Cor Ligthert [MVP]]

PA,

Do you know the oledbcommandbuilder class, that can save you a lot of time
in those simple selects as you show now.

http://msdn.microsoft.com/library/d...temdataoledboledbcommandbuilderclasstopic.asp

I hope this helps,

Cor