Question Problem with DataGrid

[jimmy1981]

I have added a DataGrid to my form and it seems to be working well apart from when I add a row, save the changes and then attempt to delete a row, i get a Concurrency Violation with the DeleteCommand.

Any help would be much appreciated.


My Code:

[COLOR=blue]
[/COLOR]Imports System.Data
 
Public Class Window2
    Dim customerdataset As New TB_DatabaseDataSet
    Dim customeradapter As New TB_DatabaseDataSetTableAdapters.IngredientsTableAdapter
    Dim customermanager As New TB_DatabaseDataSetTableAdapters.TableAdapterManager
    Dim myView As CollectionView
 
    Private Sub Window2_Loaded(sender As Object, e As System.Windows.RoutedEventArgs) Handles Me.Loaded
        Me.customeradapter.Fill(Me.customerdataset.Ingredients)
        Me.DataContext = Me.customerdataset.Ingredients
        Me.myView = CollectionViewSource.GetDefaultView(Me.customerdataset.Ingredients)
        DataGrid1.ItemsSource = Me.myView
    End Sub
 
    Private Sub btnAdd_Click(sender As System.Object, e As System.Windows.RoutedEventArgs) Handles btnAdd.Click
        Dim row = Me.customerdataset.Ingredients.NewIngredientsRow
        Me.customerdataset.Ingredients.AddIngredientsRow(row)
        Me.myView.MoveCurrentToLast()
        DataGrid1.UpdateLayout()
 
    End Sub
 
    Private Sub btnSave_Click(sender As System.Object, e As System.Windows.RoutedEventArgs) Handles btnSave.Click
        Try
            If Me.customerdataset.HasChanges Then
                Me.customeradapter.Update(Me.customerdataset)
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub
 
    Private Sub btnDelete_Click(sender As System.Object, e As System.Windows.RoutedEventArgs) Handles btnDelete.Click
 
        Dim result As MessageBoxResult = Microsoft.Windows.Controls.MessageBox.Show("If you select Yes you will not be able to Undo" & vbCrLf & vbCrLf & "Are You Sure?", "Delete Record?", MessageBoxButton.YesNo, MessageBoxImage.Question)
        Dim msgboxdel As MessageBoxResult
 
        If result = MessageBoxResult.Yes Then
 
            MsgBox(myView.Count)
 
            If Me.myView.CurrentPosition > -1 Then
                Dim row = CType(Me.myView.CurrentItem, DataRowView)
                row.Delete()
 
                Try
                    If Me.customerdataset.HasChanges Then
                        Me.customeradapter.Update(Me.customerdataset)
                        msgboxdel = MessageBox.Show("The record has been removed from the database!", "Record Deleted", MessageBoxButton.OK, MessageBoxImage.Information)
                    End If
                Catch ex As Exception
                    MsgBox(ex.Message)
                End Try
            End If
        Else
        End If
 
        MsgBox(myView.Count)
        DataGrid1.Items.Refresh()
 
 
    End Sub
 
    Private Sub DataGrid_LoadingRow(sender As System.Object, e As System.Windows.Controls.DataGridRowEventArgs)
        e.Row.Header = (e.Row.GetIndex()) + 1.ToString()
    End Sub
 
 
End Class

My XAML:

<DataGrid AutoGenerateColumns="False" Height="450" HorizontalAlignment="Left" Name="DataGrid1" VerticalAlignment="Top" Width="500" Margin="15" LoadingRow="DataGrid_LoadingRow" CanUserAddRows="False">
        <DataGrid.Columns>
            <DataGridTextColumn x:Name="NameColumn" Binding="{Binding Path=Name}" Header="Name" Width="150" CanUserSort="True" />
            <DataGridTextColumn x:Name="Pack_SizeColumn" Binding="{Binding Path=Pack_Size}" Header="Pack Size" Width="100" />
            <DataGridTextColumn x:Name="PriceColumn" Binding="{Binding Path=Price}" Header="Price" Width="100" />
            <DataGridTextColumn x:Name="Unit_PriceColumn" Binding="{Binding Path=Unit_Price}" Header="Unit Price" Width="100" />
        </DataGrid.Columns>
        </DataGrid>

 

[Philip Wallman]

Have you attempted to close the tableadapter after saving/deleting?

Me.customeradapter.Update(Me.customerdataset)

Me.customeradapter.close 'make sure the connection is closed after saving
'the tableadapter will automatically reopen the connection when needed

I've run into a similar problem and although the tableadapter is supposed to open and close itself, it seems like it doesn't always close the connection when needed.

 

[jimmy1981]

I don't have

Me.customeradapter.close

I tried the below in the Save button.

[COLOR=blue][FONT=Consolas]Me[/FONT][/COLOR][COLOR=black][FONT=Consolas].customeradapter.Connection.Close()[/FONT][/COLOR][COLOR=blue][FONT=Consolas]
[/FONT][/COLOR]

But I still get the error, it then deletes the record but when I close and re-open the form the record is still there.

 

[Philip Wallman]

I wish someone with a little more experience with the collection list would chime in. What I think is happening is you are deleting out of the collection view but you aren't flagging the record for deletion in the dataset. would be easy to find out, through a messagebox in your if statement:

    Private Sub btnDelete_Click(sender As System.Object, e As System.Windows.RoutedEventArgs) Handles btnDelete.Click

        Dim result As MessageBoxResult = Microsoft.Windows.Controls.MessageBox.Show("If you select Yes you will not be able to Undo" & vbCrLf & vbCrLf & "Are You Sure?", "Delete Record?", MessageBoxButton.YesNo, MessageBoxImage.Question)
        Dim msgboxdel As MessageBoxResult

        If result = MessageBoxResult.Yes Then

            MsgBox(myView.Count)

            If Me.myView.CurrentPosition > -1 Then
                Dim row = CType(Me.myView.CurrentItem, DataRowView)
                row.Delete()

                Try
                    If Me.customerdataset.HasChanges Then
''put a message box to see if it does have changes to the dataset
MsgBox("whatever")

                        Me.customeradapter.Update(Me.customerdataset)
                        msgboxdel = MessageBox.Show("The record has been removed from the database!", "Record Deleted", MessageBoxButton.OK, MessageBoxImage.Information)
                    End If
                Catch ex As Exception
                    MsgBox(ex.Message)
                End Try
            End If
        Else
        End If

        MsgBox(myView.Count)
        DataGrid1.Items.Refresh()

What I am thinking is the delete command isn't flagging an actual row, and should look something like:

Me.customerdataset.Ingredients.Rows(row).Delete()

Hope this helps, like I said a little out of my element:fat:

 

[jimmy1981]

It is deleting from the dataset but

If Me.myView.CurrentPosition > -1 Then 
Dim row = CType(Me.myView.CurrentItem, DataRowView) 
row.Delete()

looks like an issue as i'm always getting CurrentPosition equals 0 no matter what line in the datagrid i press on so it always deletes row 0.

I think there are more issues with this code than i originally thought, just don't fully understand wpf datagrids.