Trouble Deleteing records in DataBase - SQL Server 7 - ASP.NET?

[Guest]

Below is the code I have in the Page_Load event of my web application/page.
However, the records I select are not being deleted for some reason. It does
go through the code(look at: 'find the specified row and delete it' section)
and the msg label does say it was deleted. When I look at the rows in the
DataBase the record is still there.

Why aren't my records being deleted?


lblResults.Text = ""
If IsPostBack Then
'create a connection
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 sql command to select data
Dim cmdSelect As SqlCommand = cnn.CreateCommand()
cmdSelect.CommandType = CommandType.Text
cmdSelect.CommandText = "Select CustomerID, ContactName From
Customers"

'create a sql command 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 a sqldataadapter 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)
lblResults.Text = "Row Deleted!"
Exit For
End If
Next

'save the change
da.Update(ds, "Customers")

 

[W.G. Ryan eMVP]

When you call Remove vs. Delete, the row is actually taken out of the
table.. so when you call update, there's not any rows there with a rowstate
of Deleted for it to delete.

If you have 100 row table, and call Delete on all rows, you still have 100
rows until you call update (which calles AcceptChanges) or you call
AcceptChanges on the rows.
If you calll remove, then you'll have 0 rows so there's nothing to "update"

Just change it to delete and you should be good to go.

 

[WJ]

Or you can just do this:

'create a connection
Dim cnn As SqlConnection = New SqlConnection("Data Source=(local);Initial
Catalog=Northwind;Integrated Security=SSPI")

'create a sql command 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").Value=txtCustomerID.Text
cmdDelete.ExecuteNonQuery(); //This will delete everything that is
"txtCustomerID.Text " in one trip

***
John Webb

 

[W.G. Ryan eMVP]

John.. I'd honestly recommend using .Delete instead and calling update. If
the requirements changed at all to look for multiple values, it'd be very
easy to load them into an array or collection and check for their existence,
and if they exist, delete the row. Then call update and have it clear out
all of your rows. The problem with just calling it directly is that it
won't bend very easy if requirements change and you aren't accomodating
concurrency changes. If you deleted everything in the database with a given
user ID, you may or may not actually want to do that. For instance, if you
only wanted to delete records in a given range with that ID (the Select
Command may have a Where in it to restrict the rows in the datatable) but
you called Delete Where blag = CustomerID, you'll delete everything in the
database with that ID, which may or may not be what you intend. Based on
the looping logic he used, I'm guessing that he only wanted to delete a
specific subset of records however that's just a guess.

 

[WJ]

...concurrency changes. If you deleted everything in the database with a
given
user ID

Yes. it is just another way of deleting data records directly. The records
must be exclusively owned by a specific users to avoid
collision/concurrency.

John