Update Requires a Valid Update Command when Passed Datarow Collection

by Troy Scott

2009/10/29

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:

image 

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

try
{
 
this.Validate();
  this.rulesBindingSource.EndEdit();
  this.rulesTableAdapter.Update(this.ruleEngineDataSet.Rules);

  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:

 

image 

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

image

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:

image

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

http://social.msdn.microsoft.com/Forums/en-US/Vsexpressvcs/thread/6d4de721-7571-49ca-a1fc-8689ec19a94e/