Datatable Update does not work for deleted rows

I've update my datatable adding, updating and deleting row using GridView control. At the end, I call the SQL update function and all inserts, updates are execute on physical database whereas all deleted rows are ignored. These are my code:

'***** RowDeleting event *******
   Protected Sub grActivity_RowDeleting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewDeleteEventArgs) Handles grActivity.RowDeleting
        ' Cancel the delete operation if the user attempts to remove the last record from the GridView control.
        If grActivity.Rows.Count <= 1 Then
            e.Cancel = True
            setMessage("You must keep at least one record.", "", "lblMessage")
            Exit Sub
        End If
        dtActivity.Rows.RemoveAt(e.RowIndex)
        Session("dtActivity") = dtActivity
        BindData()
    End Sub

 Private Sub BindData()
        grActivity.DataSource = Session("dtActivity")
        grActivity.DataBind()
    End Sub
'*******  THIS IS THE DATABASE UPDATE *********
Private UpdateDatabase()
               Dim oAdap As OleDbDataAdapter
                Dim conn As New OleDbConnection
                dtActivity = Session("dtActivity")
                If dtActivity.GetChanges() Is Nothing Then
                    setMessage("No changed found. Request rejected", "", "lblMessage")
                    Exit Sub
                End If
                Dim xDataTable As DataTable = dtActivity.GetChanges()
                ' Check the DataTable for errors.
                If xDataTable.HasErrors Then
                    setMessage("Errors found in updating. Request rejected", "", "lblMessage")
                    Exit Sub
                End If
                conn.ConnectionString = "Provider=SQLOLEDB;Data Source=WEBSERVER;Initial Catalog=EPISTEMA;Persist Security Info=True;;User ID=sa;Password=reserved"
                Dim oCom As New OleDbCommand
                Try
                    oCom.CommandText = "SELECT * FROM ACTIVITIES"
                    oCom.Connection = conn
                    oAdap = New OleDbDataAdapter(oCom)
                    Dim cmdbAccessCmdBuilder As New OleDbCommandBuilder(oAdap)
                    oAdap.InsertCommand = cmdbAccessCmdBuilder.GetInsertCommand()
                    oAdap.UpdateCommand = cmdbAccessCmdBuilder.GetUpdateCommand()
                    oAdap.DeleteCommand = cmdbAccessCmdBuilder.GetDeleteCommand()
                    iCount = oAdap.Update(dtActivity)
                Catch ex As Exception
                    setMessage(ex.Message, "", "lblMessage")
                End Try
End Sub
mondoriccoAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
dampseyConnect With a Mentor Commented:
instead of RemoveAt
dtActivity.Rows.RemoveAt(e.RowIndex)

you should use Delete

dtActivity.Rows[e.RowIndex].Delete()

because delete just marks as "will be deleted"
but Remove really removes.
0
All Courses

From novice to tech pro — start learning today.