vb.net 从数据集和 sql server 中删除记录

Deleting a record from dataset and sql server

提问by user1295053

I am trying to delete a record from a DataTable and then update the database that it is attached to.

我正在尝试从 DataTable 中删除一条记录，然后更新它所附加到的数据库。

I delete a row of my DataGridView and then update my Dataset using:

我删除了一行 DataGridView，然后使用以下方法更新我的数据集：

                Me.Tab2_DGVDuty.Rows.RemoveAt(Me.Tab2_DGVDuty.CurrentRow.Index)
                ds1.AcceptChanges()
                Tab2_DGVDuty.Refresh()

I then call my adapter.update as below:

然后我调用我的adapter.update 如下：

            Dim adapter As New SqlDataAdapter
            Dim cmdBuilder As New SqlCommandBuilder(adapter)
            Dim DutyDetails As String = "SELECT * from MyTable"

            adapter.SelectCommand = New SqlCommand(DutyDetails, SQLConn)
            adapter.UpdateCommand = cmdBuilder.GetUpdateCommand
            adapter.DeleteCommand = cmdBuilder.GetDeleteCommand

            Dim cb As SqlCommandBuilder = New SqlCommandBuilder(adapter)

            adapter.Update(ds1.Tables("DT_Table"))

But when i reload the data my record is still there. If I change a value and update this works fine but for some reason the delete doesnt.

但是当我重新加载数据时，我的记录仍然存在。如果我更改一个值并更新它可以正常工作但由于某种原因删除不会。

Any help much appreciated.

非常感谢任何帮助。

EDIT: OK I changed my delete to the following as suggested below:

编辑：好的，我按照以下建议将删除更改为以下内容：

ds1.Tables("DT_Table").Rows(Tab2_DGVDuty.CurrentRow.Index).Delete()

This is attached to a button, it deletes fine the first time but on the second press to delete another record nothing happens. If I use

这是附加到一个按钮，它第一次删除很好，但在第二次按下删除另一条记录时没有任何反应。如果我使用

ds1.AcceptChanges()

then it works fine. However, if i use the above then my code below does not delete anything from the database:

然后它工作正常。但是，如果我使用上面的代码，那么下面的代码不会从数据库中删除任何内容：

            Dim adapter As New SqlDataAdapter
            Dim cmdBuilder As New SqlCommandBuilder(adapter)
            Dim DutyDetails As String = "SELECT * from MyTable"

            adapter.SelectCommand = New SqlCommand(DutyDetails, SQLConn)
            adapter.UpdateCommand = cmdBuilder.GetUpdateCommand
            adapter.DeleteCommand = cmdBuilder.GetDeleteCommand

            Dim cb As SqlCommandBuilder = New SqlCommandBuilder(adapter)

            adapter.Update(ds1.Tables("DT_Table"))

回答by Tim Schmelter

1. You don't want to remove the DataRowfrom the DataTable, you want to Deleteit

   ds1.Tables("DT_Table").Rows(Tab2_DGVDuty.CurrentRow.Index).Delete()
   

2. Don't call ds1.AcceptChanges()afterwards since the Updatewill not recognize that this row has changed anymore then because it will change it's RowStateto Unchanged. DataAdapter.Updatecalls AcceptChangesas the last step implicitely, not you.

3. I assume that Tab2_DGVDutyis a DataGridViewand not the DataTable, i've taken that into account above.

1. 你不想删除DataRow从DataTable，你想Delete它

   ds1.Tables("DT_Table").Rows(Tab2_DGVDuty.CurrentRow.Index).Delete()
   

2. 之后不要调用ds1.AcceptChanges()，因为它Update不会再识别该行已更改，因为它将更改RowState为Unchanged. 隐式DataAdapter.Update调用AcceptChanges作为最后一步，而不是你。

3. 我认为那Tab2_DGVDuty是 aDataGridView而不是DataTable，我在上面已经考虑到了这一点。