Record is not deleted?

[janilane]

It seems there is something wrong with this as the record is still in the database. It can be found, but delete doesn't work. What's wrong?

Dim newDataSet As New DataSet
Dim dt As DataTable
Dim dr As DataRow
Dim isfound As Boolean = False
Dim ans As String = Nothing


sqlAdapter.SelectCommand = New SqlCommand("select * from tempdb..Employees", sqlCon)
sqlAdapter.Fill(newDataSet, "FindTable") ' add the table now from adapter tie-up, with a name FindTable

dt = newDataSet.Tables("FindTable") 'assign the dataset table to dt object

For Each dr In dt.Rows
If dr.Item("CtContactId") = CInt(TextBox5.Text) Then
isfound = True
MsgBox(dr.Item("FamilyName"))
dr.Delete()
dt.AcceptChanges()
sqlAdapter.Update(dt)

Exit For
End If
Next

If Not isfound Then
MsgBox("Not Found!")
End If

 

[Rocksteady]

Try not calling AcceptChanges before Update.

 

[janilane]

thanks heaps!

... it worked, but isn't that the purpose of acceptchanges - to commit?

cheers

 

[Rocksteady]

Not really... I'm no expert but I think you'll find that dataadapter will call acceptchanges on the table after the update.

There're some leet guys around here that could probably give you a better explanation.

 

[cjard]

ok, here's a brief intro:

datatable has rows, each row knows what the original values it has were and what the new values are. It has two flags, RowState and RowVersion. You can read about these in MSDN http://msdn.microsoft.com/en-us/library/system.data.datarow.rowstate.aspx http://msdn.microsoft.com/en-us/library/system.data.datarowversion.aspx
Retaining old and new values allows undo (RejectChanges)
RowState has 3 significant values, Added, Modified, Deleted.


When you call tableadapter.Update() which syncs the local datatable with the db, if an Added row is encountered, the INSERT sql is called. If a Modified row is encountered, the UPDATE is called. If Deleted is encountered, DELETE sql is called. If the row is Unmodified, no SQL is called


And as ou might have guessed, AcceptChanges, makes the old and new values the same, and sets the state of the row to unchanged. So basically, you were setting your datatable so it had nothing changed and then trying to save it.

For a better idea of how to do your data access, because youre getting some of it a bit wrong, see the dw2 link in my signature