Originally Posted by
markyjj 
An unhandled exception of type 'System.InvalidOperationException' occurred in system.data.dll
Additional information: Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information.
Ah, yes. The command builder also requires that your
SELECT statement return a primary key or unique index.
If your table has either of those, you may be able to get away with simply adding the appropriate column(s) to the
SELECT statement.
If not, you'll have to write the update command yourself. Here's what it might look like:
using(SqlConnection connection = new SqlConnection("<connection string>"))
{
SqlDataAdapter adapter = new SqlDataAdapter(
"SELECT SomeField, SomeOtherField FROM Table"
);
adapter.UpdateCommand = new SqlCommand(
"UPDATE Table SET SomeField = @SomeField WHERE TheKey = @TheKey"
);
adapter.UpdateCommand.Parameters.Add(
"@TheKey", SqlDbType.Int).SourceColumn = "TheKey";
adapter.UpdateCommand.Parameters.Add(
"@SomeField", SqlDbType.Binary).SourceColumn = "SomeField";
DataTable table = new DataTable();
connection.Open();
adapter.Fill(table);
foreach(DataRow row in table.Rows)
{
row["SomeField"] = (int)row["SomeOtherField"] == 42;
}
adapter.Update(table);
}