none
Deleting Data from a Database (Multiple Tables) RRS feed

  • Question

  • Hi All,

     

    I was wondering if someone could help with a deleting from a database.

     

    I have followed on of the Walkthroughs

     "Saving Data to a Database (Multiple Tables) (UpdateMultipleTablesWalkthrough)"

    http://msdn2.microsoft.com/en-us/library/4esb49b4(VS.80).aspx

     

    Save Button Code:-

    Code Snippet

    Me.Validate()
    Me.OrdersBindingSource.EndEdit()
    Me.CustomersBindingSource.EndEdit()

    Dim deletedOrders As NorthwindDataSet.OrdersDataTable = CType( _
        NorthwindDataSet.Orders.GetChanges(Data.DataRowState.Deleted), NorthwindDataSet.OrdersDataTable)

    Dim newOrders As NorthwindDataSet.OrdersDataTable = CType( _
        NorthwindDataSet.Orders.GetChanges(Data.DataRowState.Added), NorthwindDataSet.OrdersDataTable)

    Dim modifiedOrders As NorthwindDataSet.OrdersDataTable = CType( _
        NorthwindDataSet.Orders.GetChanges(Data.DataRowState.Modified), NorthwindDataSet.OrdersDataTable)


    Try
        ' Remove all deleted orders from the Orders table.
        If Not deletedOrders Is Nothing Then
            OrdersTableAdapter.Update(deletedOrders)
        End If

        ' Update the Customers table.
        CustomersTableAdapter.Update(NorthwindDataSet.Customers)

        ' Add new orders to the Orders table.
        If Not newOrders Is Nothing Then
            OrdersTableAdapter.Update(newOrders)
        End If

        ' Update all modified Orders.
        If Not modifiedOrders Is Nothing Then
            OrdersTableAdapter.Update(modifiedOrders)
        End If

        NorthwindDataSet.AcceptChanges()

    Catch ex As Exception
        MsgBox("Update failed")

    Finally
        If Not deletedOrders Is Nothing Then
            deletedOrders.Dispose()
        End If

        If Not newOrders Is Nothing Then
            newOrders.Dispose()
        End If

        If Not modifiedOrders Is Nothing Then
            modifiedOrders.Dispose()
        End If
    End Try

     

    At first the walkthrough would not save to the database when new records were created or edit. As the code stood it only updated the database in memory. I found if I made the following change:-

     

    Under Solution Explorer clicked on Northwind.mdb, and
    changed the properties for Copy to Output Directory to "Copy if Newer"

     

    Then it allowed the update/new record to be written back to the database.

     

    However... {this is the bit I need help with} when deleting a record it shows the message "Update Failed" I think this may happen because the DataRowState for the Orders table as not been marked for deletion.

     

    Any Idea's how I would get this to delete...?

     

    Thank you for your help in advance!

    Sunday, June 3, 2007 3:32 PM

Answers

  •  

    I found an solution, which appears to work ok...

     

    Step 1: Create SQL Statement

    Under Data Sources select Edit DataSet With Designer icon

    Right click on the Orders DataTable and select Add Query

    Click Next

    Select Delete and click Next

    Change the SQL statement to:-

     

    DELETE FROM `Orders`
      WHERE CustomerID = ?

     

    Click Next, then Next and Finish

     

    Step 2: Create code to run the SQL statement

    Open the Design Window against Form1 under Solution Explorer

    Double Click the Delete button

    Add the following code

     

    Dim CustomerID As String = CustomerIDTextBox.Text

    Me.OrdersTableAdapter.DeleteQuery(CustomerID)

     

    Saturday, June 16, 2007 1:58 PM

All replies

  • Hi aisha,
      I am moving this thread from Visual Basic Express Edition forum to the .NET Framework Data Access and Storage forum, since the issue is related to TableAdapter. There are more ADO.net experts in the .NET Framework Data Access and Storage forum.
    Thanks for your understanding!
    Thursday, June 7, 2007 1:51 AM
  • Dear All,

     

    Just to let you know that this is still unresolved.

     

    I'm Visual Basic Express 2005.

     

    Thanks

    Mark

    Thursday, June 14, 2007 1:21 PM
  •  

    I found an solution, which appears to work ok...

     

    Step 1: Create SQL Statement

    Under Data Sources select Edit DataSet With Designer icon

    Right click on the Orders DataTable and select Add Query

    Click Next

    Select Delete and click Next

    Change the SQL statement to:-

     

    DELETE FROM `Orders`
      WHERE CustomerID = ?

     

    Click Next, then Next and Finish

     

    Step 2: Create code to run the SQL statement

    Open the Design Window against Form1 under Solution Explorer

    Double Click the Delete button

    Add the following code

     

    Dim CustomerID As String = CustomerIDTextBox.Text

    Me.OrdersTableAdapter.DeleteQuery(CustomerID)

     

    Saturday, June 16, 2007 1:58 PM