Deleting a record from dataset
I have a table in an Sql database with 2 columns. One column is the record ID and Primary key the other is the information item I am storing.
I have a form with a List box that is populated by the dataset and displays the information column from the above table. When a user selects one of the items in the list box and clicks a Delete button I want to delete the record for that item.
What I have found is that I can delete the item but sometimes the wrong item is deleted from the dataset. I know the problem is that I am grabbing the Index number from the list box and using it to delete the record in the dataset. These two do not always match if you have deleted and added data to the database.
So I know I can get the ID# (Primary Key) of the record I want to delete but how do I use this number to delete the correct record. I can't find a clear example of using the primary key to select a record for deletion.
Can anyone help me out?
Hello Carl,
you can use the Select method on the DataTable to find the right record. Here is an example:
Dim dr() As DataRow = Me.NorthwindDataSet.Orders.Select("OrderID=10250")
If (dr IsNot Nothing) Then
If dr.Length = 1 Then
dr(0).Delete()
Else
' Since we selected using a primary key, we shouldn't find more than one row
Throw New Exception("More than one row matches the ID specified.")
End If
End IfAfter you call Delete on the DataRow object, it'll be marked for deletion. You'll then need to use the DataAdapter to push these changes to the database.
HTHAntoine
Visual Basic team
Antoine,
Just to clarify my understanding:
In the above example you gave, is the Datarow object, dr, an array.
What I do not understand is the purpose of the 0 in dr(0). Does it represent the 0th element?
Thanks for providing the answer to my dilemma! As always, great support here on the forums!
Carl
The Select method returns an array of rows that match the query. In this case, since we select based on the primary key, we should really get 1 row max else the key would not be unique. But if you want, you can select based on other columns that are not unique and then it's possible to get more than one row back.
0 is the index of the first element in the array. If we had 3 elements in there, the indices would be 0, 1 and 2.
HTH
Antoine
Visual Basic team
