WPA XAML Datagrid Update requires a valid UpdateCommand

Question

I am trying to commit changes from a WPF datagrid databound to a microsoft SQL server database via a DataSources table adapter, but I am getting an 'Update requires a valid UpdateCommand' error. I know the DataSources wizard is fairly restricted, but it has correctly created an INSERT statement.

INSERT INTO PartRevisions
             (imrPartID, imrPartRevisionID, imrShortDescription, imrLastMaterialCost)
VALUES (@imrPartID,@imrPartRevisionID,@imrShortDescription,@imrLastMaterialCost)

Here is my test code.

namespace WPFtest2
{
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window
    {
        private WPFtest2.M1DataSet m1DataSet = new M1DataSet();
        private WPFtest2.M1DataSetTableAdapters.PartRevisionsTableAdapter m1DataSetPartRevisionsTableAdapter = new WPFtest2.M1DataSetTableAdapters.PartRevisionsTableAdapter();
        private System.Windows.Data.CollectionViewSource partRevisionsViewSource = new CollectionViewSource();

        public MainWindow()
        {
            InitializeComponent();
        }

        private void Window_Loaded(object sender, RoutedEventArgs e)
        {
            m1DataSet = ((WPFtest2.M1DataSet)(this.FindResource("m1DataSet")));
            // Load data into the table PartRevisions. You can modify this code as needed.
            m1DataSetPartRevisionsTableAdapter.Fill(m1DataSet.PartRevisions);
            System.Windows.Data.CollectionViewSource partRevisionsViewSource = ((System.Windows.Data.CollectionViewSource)(this.FindResource("partRevisionsViewSource")));
        }

        private void partRevisionsDataGrid_RowEditEnding(object sender, DataGridRowEditEndingEventArgs e)
        {
            if (e.EditAction == DataGridEditAction.Commit)
            {
                try
                {
                    int rc = 0;
                    rc = m1DataSetPartRevisionsTableAdapter.Update(m1DataSet.PartRevisions);
                    MessageBox.Show("Row updated");
                }
                catch (Exception Ex)
                {
                    MessageBox.Show(Ex.ToString());
                }
            }
        }
    }
}

and the XAML code

<Window
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
        xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
        xmlns:local="clr-namespace:WPFtest2"
        xmlns:M1DataSetTableAdapters="clr-namespace:WPFtest2.M1DataSetTableAdapters" x:Class="WPFtest2.MainWindow"
        mc:Ignorable="d"
        Title="MainWindow" Height="350" Width="525" Loaded="Window_Loaded">
    <Window.Resources>

        <local:M1DataSet x:Key="m1DataSet"/>
        <CollectionViewSource x:Key="partRevisionsViewSource" Source="{Binding PartRevisions, Source={StaticResource m1DataSet}}"/>

    </Window.Resources>
    <Grid >
        <DataGrid 
            x:Name="partRevisionsDataGrid" 
            AutoGenerateColumns="False" 
            EnableRowVirtualization="True" 
            ItemsSource="{Binding}"  
            RowDetailsVisibilityMode="VisibleWhenSelected" 
            DataContext="{StaticResource partRevisionsViewSource}"
            RowEditEnding = "partRevisionsDataGrid_RowEditEnding">
            <DataGrid.Columns>
                <DataGridTextColumn x:Name="imrPartIDColumn" Binding="{Binding imrPartID}" Header="imr Part ID" Width="SizeToHeader"/>
                <DataGridTextColumn x:Name="imrPartRevisionIDColumn" Binding="{Binding imrPartRevisionID}" Header="imr Part Revision ID" Width="SizeToHeader"/>
                <DataGridTextColumn x:Name="imrLastMaterialCostColumn" Binding="{Binding imrLastMaterialCost, Mode=TwoWay}" Header="imr Last Material Cost" Width="SizeToHeader"/>
                <DataGridTextColumn x:Name="imrShortDescriptionColumn" Binding="{Binding imrShortDescription}" Header="imr Short Description" Width="SizeToHeader"/>
            </DataGrid.Columns>
        </DataGrid>

    </Grid>
</Window>

It also seems the RowEditEnding event doesn't have the updated row data, as the error does not occur until I edit a second row.

So my mistake was that I was looking at an insert command (I was tired), not an update command. In fact the wizard didn't generate an update command even though it said it did. This was possibly because this table has two primary keys. I wrote an SQL update command and the error went away. I still have the problem that I have to enter the data twice before the data saves. Thus the RowEditEnding event holds the 'pre saved data'. How to I force a local commit so the SQL update uses the new user edited data. — labnet, Jun 11 '16 at 00:00

score 0 · Answer 1 · edited May 23 '17 at 10:28

The first problem was solved by manually creating an insert command in UpdateCommand property of the TableAdapter.

UPDATE PartRevisions
SET       imrLastMaterialCost = @imrLastMaterialCost, imrAverageMaterialCost = @imrAverageMaterialCost, imrStandardMaterialCost = @imrStandardMaterialCost
WHERE (imrPartID = @imrPartID) AND (imrPartRevisionID = @imrPartRevisionID)

The second problem, of the row not having updated when the DataGridRowEditEndingEvent has fired was solved by executing a commitedit. WPF Datagrid Row Editing "ENDED" event

The problem with a commit edit, is it re-fires the RowGridEndEdit. There are two ways to handle this.

1- Temporarily remove the event handler.

        (sender as DataGrid).RowEditEnding -= partRevisionsDataGrid_RowEditEnding;
        (sender as DataGrid).CommitEdit();
        (sender as DataGrid).Items.Refresh();
        (sender as DataGrid).RowEditEnding += partRevisionsDataGrid_RowEditEnding;

Or 2-put a flag in the code to prevent recursion like this.

bool prdg_already_updating = false;
private void partRevisionsDataGrid_RowEditEnding(object sender, DataGridRowEditEndingEventArgs e)
{
    if (e.EditAction == DataGridEditAction.Commit && !prdg_already_updating)
    {
        prdg_already_updating = true;
        (sender as DataGrid).CommitEdit();
        (sender as DataGrid).Items.Refresh();
        prdg_already_updating = false;
        m1_02DataSetPartRevisionsTableAdapter.Update(m1_02DataSet.PartRevisions);
        MessageBox.Show("Row updated");
    }

This seems broken behaviour, that the ms datagrid has no event mechanism to indicate a committed row update. So it would be nice to know if someone knows of one.

I would also be interested to know if this is the MVVM way? You think could be all handled on the XAML side.

WPA XAML Datagrid Update requires a valid UpdateCommand

1 Answers1