Delete a record from a datasource

[jimmy1981]

I have a sample database that I am able to navigate, add new and update but I am falling over when it comes to deleting a record. Currently the user would navigate the record and then select delete, I have this code which seems to delete it from the view but when i re-load the form it is there again. When I add a record it saves it no problem, just seems to be a problem with delete.

​

Imports System.Data


Public Class Window2
    Dim customerdataset As New CustomersDataset
    Dim customeradapter As New CustomersDatasetTableAdapters.Purchase_OrdersTableAdapter
    Dim customermanager As New CustomersDatasetTableAdapters.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.Purchase_Orders)
        Me.DataContext = Me.customerdataset.Purchase_Orders
        Me.myView = CollectionViewSource.GetDefaultView(Me.customerdataset.Purchase_Orders)
    End Sub


    Private Sub btnNext_Click(sender As System.Object, e As System.Windows.RoutedEventArgs) Handles btnNext.Click
        If Me.myView.CurrentPosition < Me.myView.Count - 1 Then
            Me.myView.MoveCurrentToNext()
        End If
    End Sub


    Private Sub btnPrevious_Click(sender As System.Object, e As System.Windows.RoutedEventArgs) Handles btnPrevious.Click
        If Me.myView.CurrentPosition > 0 Then
            Me.myView.MoveCurrentToPrevious()
        End If
    End Sub


    Private Sub btnFirst_Click(sender As System.Object, e As System.Windows.RoutedEventArgs) Handles btnFirst.Click
        Me.myView.MoveCurrentToFirst()
    End Sub


    Private Sub btnLast_Click(sender As System.Object, e As System.Windows.RoutedEventArgs) Handles btnLast.Click
        Me.myView.MoveCurrentToLast()
    End Sub


    Private Sub btnAdd_Click(sender As System.Object, e As System.Windows.RoutedEventArgs) Handles btnAdd.Click
        Dim Row = Me.customerdataset.Purchase_Orders.NewPurchase_OrdersRow
        Me.customerdataset.Purchase_Orders.AddPurchase_OrdersRow(Row)
        Me.myView.MoveCurrentToLast()
    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 btnCancel_Click(sender As System.Object, e As System.Windows.RoutedEventArgs) Handles btnCancel.Click
        Me.customerdataset.RejectChanges()
    End Sub


    Private Sub btnDelete_Click(sender As System.Object, e As System.Windows.RoutedEventArgs) Handles btnDelete.Click
        If Me.myView.CurrentPosition > -1 Then
            Dim row = CType(Me.myView.CurrentItem, DataRowView).Row
            Me.customerdataset.Purchase_Orders.RemovePurchase_OrdersRow(row)
            Me.customerdataset.AcceptChanges()
            Me.customeradapter.Update(Me.customerdataset)
        End If
    End Sub








End Class

cheers.​

 

[hipturtle]

It looks to me as though the customerdataset is a user defined control and RemovePurchase_OrdersRow() is a function on that user defined control. If so you would have to have a look at that code to determine what is going wrong.

You could also try debugging.

 

[jmcilhinney]

This is one issue:

    Private Sub btnDelete_Click(sender As System.Object, e As System.Windows.RoutedEventArgs) Handles btnDelete.Click
        If Me.myView.CurrentPosition > -1 Then
            Dim row = CType(Me.myView.CurrentItem, DataRowView).Row
            Me.customerdataset.Purchase_Orders.RemovePurchase_OrdersRow(row)
            [B][U][COLOR="#FF0000"]Me.customerdataset.AcceptChanges()[/COLOR][/U][/B]
            Me.customeradapter.Update(Me.customerdataset)
        End If
    End Sub

You might want to do some reading to learn what that actually does.

Also, I'm not 100% sure but does that RemovePurchase_OrdersRow delete the row or does it remove it? There is a big difference between the two. I would think that you should simply change that code to this:

    Private Sub btnDelete_Click(sender As System.Object, e As System.Windows.RoutedEventArgs) Handles btnDelete.Click
        If Me.myView.CurrentPosition > -1 Then
            Dim row = CType(Me.myView.CurrentItem, DataRowView)

            row.Delete()
        End If
    End Sub

That will flag the row as Deleted but leave it in the DataTable, which is quite different to removing it from the DataTable. That's just like new rows get flagged as Added and updated rows get flagged as Modified. When the user then clicks the Save button, all those changes will be detected and saved back to the database.

 

[jimmy1981]

    Private Sub btnDelete_Click(sender As System.Object, e As System.Windows.RoutedEventArgs) Handles btnDelete.Click
        If Me.myView.CurrentPosition > -1 Then
            Dim row = CType(Me.myView.CurrentItem, DataRowView)

            row.Delete()
        End If
    End Sub

I have tried this and then i get the 'Update requires a valid DeleteCommand when passed DataRow collection with deleted rows.'

I try and use something like

Dim cb As New SqlCeCommandBuilder(Me.customeradapter)

but obviously my dataadapter hasn't been defined as sqlce so it wont work.

 

[jmcilhinney]

You don't use command builders with typed DataSets. Everything is contained in the TableAdapters. Open your DataSet in the designer and take a look at the InsertCommand, UpdateCommand and DeleteCommand properties of that TableAdapter. Which ones are set and what SQL code do they contain?

 

[jimmy1981]

I have a Fill,GetData() and it's SQL is

SELECT [Order_Number], [Order_Reference], [Date_Placed], [Date_Delivered], [Customer_Name], [Customer_Address], [Customer_Tel], [Customer_Email], [Product], [Amount] FROM [Purchase_Orders]

I presume i need a delete command in there, but if that's the case why does the insert a new record work without there being an INSERT command in the dataset?

 

[jimmy1981]

OK I've got it. I hadn't selected a DeleteCommand in the dataset designer and then I just needed to set the CommandText statement to

DELETE FROM Purchase_Orders WHERE (Order_Number = @Order_Number)

Thanks for pointing me in the right direction, have tested and all seems to be working well now.

 

[jmcilhinney]

You didn't actually answer my question. You generally don't need to write any SQL code yourself so you may be doing something wrong and, if so, you will continue to do it wrong in the future.

 

[jimmy1981]

In my dataset designer the INSERT CommandText had been populated with

INSERT INTO Purchase_Orders                      (Order_Reference, Date_Placed, Date_Delivered, Customer_Name, Customer_Address, Customer_Tel, Customer_Email, Product, Amount)
VALUES     (@p1,@p2,@p3,@p4,@p5,@p6,@p7,@p8,@p9)

The SELECT CommandText had been populated with

SELECT     Order_Number, Order_Reference, Date_Placed, Date_Delivered, Customer_Name, Customer_Address, Customer_Tel, Customer_Email, Product, Amount
FROM         Purchase_Orders

There was nothing selected for DELETE or UPDATE, I selected the Command and then manually populated the CommandText SQL.

Does this help show what I am doing wrong?

 

[jmcilhinney]

That almost certainly means that your database table has no primary key. The wizard needs the primary key in order to identify which record to update or delete. If the PK isn't there then a record cannot be uniquely identified so the wizard can't generate those statements. There's rarely a reason for a table not to have a PK so you should add one and then re-run the wizard to generate those statements.

 

[jimmy1981]

That's exactly it, thanks for helping out, much appreciated.