• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 235
  • Last Modified:

Delete row from database

How do I delete a row from a dataset.  Thanks

Here is what I have so far

 Private Sub Delete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Delete.Click
        DataSet21.Tables(0).Rows.RemoveAt(DataGrid1.CurrentRowIndex)
        SqlDataAdapter2.Update(DataSet21)
    End Sub
0
running32
Asked:
running32
1 Solution
 
nouloukCommented:
Hi, your code is good.
I think you 've made the DELETE stored proc or sql query to delete what you want and add to the SqlDataAdapter,
finally you AcceptChanges in DataSet:

Private Sub Delete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Delete.Click
        DataSet21.Tables(0).Rows.RemoveAt(DataGrid1.CurrentRowIndex) // mark row to be deleted
        SqlDataAdapter2.Update(DataSet21)   // delete the row in db with DELETE query
        DataSet21.AcceptChanges()   // delete the row in the DataSet
    End Sub

Hope this helps.
0
 
Bob LearnedCommented:
What's wrong with your code?  Is there a DeleteCommand set for the SqlDataAdapter?

DataSet.AcceptChanges is useless here.  It would change all the row status to Unmodified.

Bob
0
 
amyhxuCommented:
Private Sub Delete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Delete.Click
        DataSet21.Tables(0).Rows(DataGrid1.CurrentRowIndex).Delete()
        SqlDataAdapter2.Update(DataSet21)
End Sub

If I didn't remember things wrong: RemoveAt() changes row state to unchanged, same effect as first call dataRow.Delete() then DataRow.AcceptChanges(). Therefore, when you call DataAdapter.Update(), the dataadapter thinks there's no change since last update, and will not update the database.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/vbconintroductiontodatasetupdates.asp
"to remove a record from a data table, you could call the RemoveAt Method of the table's Rows collection, which physically deletes the record from the dataset. If you are using the dataset only as a structured store for data and are not concerned about transmitting change information to another application, manipulating collections this way is an acceptable way of updating a dataset."

"However, if you intend to send changes to a data source or another application, you need to maintain change information (that is, metadata) about each update. Later, when you send changes to the data source or application, the process will have the information it needs to locate and update the proper records. For example, if you delete a record in the dataset, information about the deleted record has to be maintained in the dataset so when the DeleteCommand of the data adapter is invoked there will be enough historic information to locate the original record in the data source so it can be deleted. "
0
 
amyhxuCommented:
Another thing I notice here is that user need to click on a delete button to delete a row. But the button is outside the datagrid, therefore the DataGrid1.CurrentRowIndex will return -1. One way to get the rownumber to be deleted is (suppose your datagrid doesn't allow sorting):

Dim rowNo As Integer

Private Sub DataGrid1_MouseDown(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles DataGrid1.MouseDown
    Dim hti As DataGrid.HitTestInfo = DataGrid1.HitTest(DataGrid1.PointToClient(Cursor.Position))
    rowNo = hti.Row
End Sub

Private Sub Delete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Delete.Click
        DataSet21.Tables(0).Rows(rowNo).Delete()
        SqlDataAdapter2.Update(DataSet21)
End Sub


If your datagrid allows sorting, you may need to use currencymanager.
http://www.experts-exchange.com/Programming/Programming_Languages/Dot_Net/VB_DOT_NET/Q_21390868.html
0
 
running32Author Commented:
Thanks for the help.   Delete is working and you gave a great explanation.

Thanks again
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now