CODEWITHSUNDEEP
vb.netsql-server-2008visual-studio-2008
user1295053
user1295053

Reputation: 303

Deleting a record from dataset and sql server

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

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

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

I then call my adapter.update as below:

            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"))

Upvotes: 0

Views: 7089

Answers (1)

Tim Schmelter
Tim Schmelter

Reputation: 460228

  1. You don't want to remove the DataRow from the DataTable, you want to Delete it

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

  2. Don't call ds1.AcceptChanges() afterwards since the Update will not recognize that this row has changed anymore then because it will change it's RowState to Unchanged. DataAdapter.Update calls AcceptChanges as the last step implicitely, not you.

  3. I assume that Tab2_DGVDuty is a DataGridView and not the DataTable, i've taken that into account above.

Upvotes: 2

Related Questions