problem deleting a row using DeleteCommand

From: Luciano (Luciano_at_discussions.microsoft.com)
Date: 02/24/05 

Date: Wed, 23 Feb 2005 17:31:02 -0800

I was wondering whether anybody can help me. I am doing a MCSD & have
encountered a problem in some code I was using to Delete a row. I had no
problem using the INSERT Command & the UPDATE command, as these changes
persisted to the underlying database, but when I run the DELETE part it does
not actually remove the row from the database even if I use the update
command to save the changes. Anyway here is the code I use from a book I have:

Private Sub Page_Load(ByVal sender As System.Object, _
 ByVal e As System.EventArgs) Handles MyBase.Load
    If IsPostBack Then
        ' Create some ADO.NET objects
        Dim cnn As SqlConnection = _
         New SqlConnection("Data Source=(local);" & _
         "Initial Catalog=Northwind;" & _
         "Integrated Security=SSPI")
        Dim ds As DataSet = New DataSet()
        Dim da As SqlDataAdapter = _
         New SqlDataAdapter()
        ' Create a SqlCommand to select data
        Dim cmdSelect As SqlCommand = _
         cnn.CreateCommand()
        cmdSelect.CommandType = CommandType.Text
        cmdSelect.CommandText = _
         "SELECT CustomerID, ContactName " & _
         "FROM Customers"
        ' Create a SqlCommand to delete data
        Dim cmdDelete As SqlCommand = _
         cnn.CreateCommand()
        cmdDelete.CommandType = CommandType.Text
        cmdDelete.CommandText = _
         "DELETE FROM Customers " & _
         "WHERE CustomerID = @CustomerID"
        cmdDelete.Parameters.Add("@CustomerID", _
         SqlDbType.NChar, _
         5, "CustomerID")
        cmdDelete.Parameters("@CustomerID"). _
         SourceVersion = _
         DataRowVersion.Original
        ' Set up the DataAdapter and fill the DataSet
        da.SelectCommand = cmdSelect
        da.DeleteCommand = cmdDelete
        da.Fill(ds, "Customers")
        ' Find the specified row and delete it
        Dim dr As DataRow
        For Each dr In ds.Tables("Customers").Rows
            If dr(0) = txtCustomerID.Text Then
                ds.Tables("Customers").Rows.Remove(dr)
                Exit For
            End If
        Next
        ' Save the changes
        da.Update(ds, "Customers")
        lblResults.Text = "Row deleted!"
    End If
End Sub

What this small application does is ask for a CustomerID & then I press a
button & the Label shows that the action has occurred, when I query the
Database the row still exists. I have tried to delete newly added rows,
existing rows & rows that have been there originally & I get the same
results, ie no deleted row. I tried to the Debug & step through the code &
after the code runs the row seems to have a rowState of Detached rather than
deleted.

I have tried everything & have had no luck, can someone check the code & see
if it works or what may need to be changed to allow it to work.

Thanx

Luciano