header image troyscott.ca - powered by jigsaw

Update Requires a Valid Update Command when Passed Datarow Collection

by Troy Scott


I have been creating an application for work over the last couple of days which requires ADO.Net. I work in the Business Intelligence field so I do not code verify often unless I need a custom script for an SSIS package.   Most of the Microsoft BI products just require a solid understanding of SQL or MDX and of course the customers business requirements.   While creating the application I ran into the following error when updating the databases with changes in the dataset.  I am using Visual Studio 2008 Express Edition and the database is a Compact Database. 

I only received the error when I tried to update a record in the database.  If I added a new record (INSERT) and saved my changes the application worked fine.  Below is a screenshot of the simple app:


When you hit the save button it executes the following code:


  MessageBox.Show("Update successful!");


catch (System.Exception ex)
  MessageBox.Show("Update failed:\n" + ex.Message + "\n" + ex.StackTrace );


I placed a breakpoint on: this.Validate(); line.  When I ran the program  the program failed on: this.rulesTableAdapter.Update(this.ruleEngineDataSet.Rules);.  Below is a print screen of the message:



The issue is with the TableAdapter.  There was no UpdateCommand (which is mentioned in the error message) or DeleteCommand for the TableAdapter:


Create a new UpdateCommand by click (new) in the dropdown box.  You can use the query designer to create the update statement for the CommandText property:


Once the the CommandText property was set for the UpdateCommand the program worked. Below is a link to the issue found on msdn forums: