mgmhicks
asked on
Can not delete datagrid row from dataset
I have a datagrid that is bound to a dataset. I use the datagrids, edit and delete auto buttons. When I delete the row, code example 1 fires, then makes visible to update button which is to be used to updated the underlying database. But when I run the update code (the 2nd example) I receive erroor that "Deleted row information can not be accessed through the row.
Please help, the update items seem to work fine, although I thought if I attached each of the sqlcommands to the correct dataapdapter function, all I had to do was to dataadapter.update(ds.tabl es(0)) rather than having to iriderate through the database. Maybe someone could elaborate on that for me.
thanks
Please help, the update items seem to work fine, although I thought if I attached each of the sqlcommands to the correct dataapdapter function, all I had to do was to dataadapter.update(ds.tabl
thanks
Private Sub UpdateTable()
'Dim myUpdateCMD As New SqlCommand
Dim cmdEdit As New SqlCommand
Dim cmdADD As New SqlCommand
Dim cmdDelete As New SqlCommand
'' Dim strUpdate As String
Dim i As Integer = 0
If dsCategories.HasChanges Then
For i = 0 To dsCategories.Tables(0).Rows.Count - 1
Dim myRow As DataRow
If dsCategories.Tables(0).Rows(i).RowState = DataRowState.Added Then
myRow = dsCategories.Tables(0).Rows(i)
With cmdADD
.Connection = myConn
.CommandText = "TAG_InsertCategory"
.CommandType = CommandType.StoredProcedure
.Parameters.Add(New SqlParameter("@ID", myRow.Item(0)))
.Parameters.Add(New SqlParameter("@Long_Name", myRow.Item(1)))
.Parameters.Add(New SqlParameter("@Short_Name", myRow.Item(2)))
.Parameters.Add(New SqlParameter("@Color", myRow.Item(3)))
.Parameters.Add(New SqlParameter("@Active", myRow.Item(4)))
.Parameters.Add(New SqlParameter("@LocationID", myRow.Item(5)))
End With
ElseIf dsCategories.Tables(0).Rows(i).RowState = DataRowState.Deleted Then
myRow = dsCategories.Tables(0).Rows(i)
With cmdDelete
.Connection = myConn
.CommandText = "TAG_DeleteCategory"
.CommandType = CommandType.Text
.Parameters.Add(New SqlParameter("@RowID", myRow(0)))
End With
ElseIf dsCategories.Tables(0).Rows(i).RowState = DataRowState.Modified Then
myRow = dsCategories.Tables(0).Rows(i)
With cmdEdit
.Connection = myConn
.CommandText = "TAG_UpdateCategories"
.CommandType = CommandType.StoredProcedure
.Parameters.Add(New SqlParameter("@ID", myRow.Item(0)))
.Parameters.Add(New SqlParameter("@Long_Name", myRow.Item(1)))
.Parameters.Add(New SqlParameter("@Short_Name", myRow.Item(2)))
.Parameters.Add(New SqlParameter("@Color", myRow.Item(3)))
.Parameters.Add(New SqlParameter("@Active", myRow.Item(4)))
.Parameters.Add(New SqlParameter("@LocationID", myRow.Item(5)))
End With
End If
Next
Else
' nothing to change
End If
Dim daAdapter As New SqlDataAdapter
Try
myConn.Open()
daAdapter.UpdateCommand = cmdEdit
daAdapter.InsertCommand = cmdADD
daAdapter.DeleteCommand = cmdDelete
daAdapter.Update(dsCategories.Tables(0))
myConn.Close()
Catch ex As Exception
Dim myError As String
myError = ex.Message
End Try
End Sub
Private Sub gvCategories_RowDeleting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewDeleteEventArgs) Handles gvCategories.RowDeleting
dsCategories.Tables(0).Rows(e.RowIndex).BeginEdit()
dsCategories.Tables(0).Rows(e.RowIndex).Delete()
dsCategories.Tables(0).Rows(e.RowIndex).EndEdit()
'dsCategories.Tables(0).Rows(e.RowIndex).AcceptChanges()
gvCategories.DataSource = dsCategories.Tables(0)
gvCategories.DataBind()
If dsCategories.HasChanges Then
btnUpdate.Visible = True
Else
btnUpdate.Visible = False
End If
End Sub
Call a refresh of the datagridview AFTER deleting the row in the datatable.
ASKER
Where under row_deleting or the update sub. Looking at the row_deleting sub, looks like I removed the row from the dataset, however the problem is updating the sql server to delete that row in the update sub.
I mis-understood your question.
Now that you know that your routine to delete the data from the table works:
1. you should call the routine to delete the same data from the database before acually deleting it from the table, thereby making use of the data in the datatable BEFORE it is deleted, or:
2. keep some referencing data in a variable so that you can access this data to call your routine to update the database, e.g row number or key data
Now that you know that your routine to delete the data from the table works:
1. you should call the routine to delete the same data from the database before acually deleting it from the table, thereby making use of the data in the datatable BEFORE it is deleted, or:
2. keep some referencing data in a variable so that you can access this data to call your routine to update the database, e.g row number or key data
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
x77 I have a bound grid, and I have a delete button (auto delete link) and I cant get it deleted from the underlying database. Should I update the underlying database after each change or wait and do all the changes to the database at once like I have tried to do in the update sub I have. Its not supposed to be this hard I am missing the boat somewhere. If I cancel the delete, when I do the UpdateTable sub, how can I tell the underlying database which record to delete.
thanks
thanks
ASKER
Here is how I handle getting rid of the row, within the underlying database
Private Sub gvCategories_RowDeleting(B yVal sender As Object, ByVal e As System.Web.UI.WebControls. GridViewDe leteEventA rgs) Handles gvCategories.RowDeleting
Dim mValue As Integer = dsCategories.Tables(0).Row s(e.RowInd ex).Item(0 )
dsCategories.Tables(0).Row s(e.RowInd ex).BeginE dit()
dsCategories.Tables(0).Row s(e.RowInd ex).Delete ()
dsCategories.Tables(0).Row s(e.RowInd ex).EndEdi t()
dsCategories.Tables(0).Row s(e.RowInd ex).Accept Changes()
Dim mystr As String
mystr = "DELETE FROM TAG_InspectionCategories WHERE ID = " & mValue
Dim myCMD As New SqlCommand
Dim myDA As New SqlDataAdapter
'myDA.DeleteCommand.Comman dText = mystr
Try
myConn.Open()
With myCMD
.Connection = myConn
.CommandText = mystr
.CommandType = CommandType.Text
End With
myCMD.ExecuteNonQuery()
myConn.Close()
Catch ex As Exception
Dim mtest As String
mtest = ex.Message
End Try
gvCategories.DataSource = dsCategories.Tables(0)
gvCategories.DataBind()
If dsCategories.HasChanges Then
btnUpdate.Visible = True
Else
btnUpdate.Visible = False
End If
End Sub
Private Sub gvCategories_RowDeleting(B
Dim mValue As Integer = dsCategories.Tables(0).Row
dsCategories.Tables(0).Row
dsCategories.Tables(0).Row
dsCategories.Tables(0).Row
dsCategories.Tables(0).Row
Dim mystr As String
mystr = "DELETE FROM TAG_InspectionCategories WHERE ID = " & mValue
Dim myCMD As New SqlCommand
Dim myDA As New SqlDataAdapter
'myDA.DeleteCommand.Comman
Try
myConn.Open()
With myCMD
.Connection = myConn
.CommandText = mystr
.CommandType = CommandType.Text
End With
myCMD.ExecuteNonQuery()
myConn.Close()
Catch ex As Exception
Dim mtest As String
mtest = ex.Message
End Try
gvCategories.DataSource = dsCategories.Tables(0)
gvCategories.DataBind()
If dsCategories.HasChanges Then
btnUpdate.Visible = True
Else
btnUpdate.Visible = False
End If
End Sub