Mylor
asked on
How to delete a record from datagrid?
Hi,
I have this sub to remove a record from the datagrid and table, but it just removes the record from datagrid and it does not from the table.
I already defined the dataset and everything else before and these is the delete sub:
Private Sub cmdRemoveLoan_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdRemoveLoan.Click
Dim rno As String = dgAddedLoans.CurrentRowInd ex
Try
DataSet11.Tables(0).Rows(r no).Delete ()
OleDbDataAdapter1.Fill()
OleDbDataAdapter1.Update(D ataSet11)
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
I have this sub to remove a record from the datagrid and table, but it just removes the record from datagrid and it does not from the table.
I already defined the dataset and everything else before and these is the delete sub:
Private Sub cmdRemoveLoan_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdRemoveLoan.Click
Dim rno As String = dgAddedLoans.CurrentRowInd
Try
DataSet11.Tables(0).Rows(r
OleDbDataAdapter1.Fill()
OleDbDataAdapter1.Update(D
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
ASKER
Still the same. I have the following error:
"Update requires a valid Deletecommend when passed DataRow collection with deleted rows."
and it removes it from the datagrid but not from the table.
"Update requires a valid Deletecommend when passed DataRow collection with deleted rows."
and it removes it from the datagrid but not from the table.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
the adapter has three different commands that it uses to update the DB depending on the rowstate (insertcommand, updatecommand, deletecommand). You must define each or use the commandbuilder. I prefer to do it myself, below is an example.
With daBatch
.SelectCommand = New SqlCommand
With .SelectCommand
.Connection = myparent.EZGoesConnection
.CommandText = "SELECT BatchKey, BatchID, UserLogin, " + _
"PostDate, Comment, CreateDate, ImportDate " + _
"FROM gwi_tbl_Batch WHERE BatchID = @BatchID"
.Parameters.Add("@BatchID" , SqlDbType.Int, 4, "BatchID")
End With 'daBatch.SelectCommand
.InsertCommand = New SqlCommand
With .InsertCommand
.Connection = myparent.EZGoesConnection
.CommandText = "INSERT INTO dbo.gwi_tbl_Batch " + _
"(BatchID, UserLogin, PostDate, Comment, CreateDate, " + _
"ImportDate) VALUES (@BatchID, @UserLogin, @PostDate," + _
"@Comment, @CreateDate, @ImportDate)"
With .Parameters
.Add("@BatchID", SqlDbType.Int, 4, "BatchID")
.Add("@UserLogin", SqlDbType.VarChar, 12, "UserLogin")
.Add("@PostDate", SqlDbType.SmallDateTime, 4, "PostDate")
.Add("@Comment", SqlDbType.VarChar, 250, "Comment")
.Add("@CreateDate", SqlDbType.DateTime, 8, "CreateDate")
.Add("@ImportDate", SqlDbType.DateTime, 8, "ImportDate")
End With 'daBatch.InsertCommand.Par ameters
End With 'daBatch.InsertCommand
.UpdateCommand = New SqlCommand
With .UpdateCommand
.Connection = myparent.EZGoesConnection
.CommandText = "UPDATE dbo.gwi_tbl_Batch " + _
"SET BatchID = @BatchID, UserLogin = @UserLogin, " + _
"PostDate = @PostDate, Comment = @Comment, " + _
"CreateDate = @CreateDate, ImportDate = @ImportDate " + _
"WHERE BatchKey = @BatchKey"
With .Parameters
.Add("@BatchKey", SqlDbType.Int, 4, "BatchKey")
.Add("@BatchID", SqlDbType.Int, 4, "BatchID")
.Add("@UserLogin", SqlDbType.VarChar, 12, "UserLogin")
.Add("@PostDate", SqlDbType.SmallDateTime, 4, "PostDate")
.Add("@Comment", SqlDbType.VarChar, 250, "Comment")
.Add("@CreateDate", SqlDbType.DateTime, 8, "CreateDate")
.Add("@ImportDate", SqlDbType.DateTime, 8, "ImportDate")
End With 'daBatch.UpdateCommand.Par ameters
End With 'daBatch.UpdateCommand
.DeleteCommand = New SqlCommand
With .DeleteCommand
.Connection = myparent.EZGoesConnection
.CommandText = "DELETE FROM dbo.gwi_tbl_Batch " + _
"WHERE BatchKey = @BatchKey"
.Parameters.Add("@BatchKey ", SqlDbType.Int, 4, "BatchKey")
End With 'daBatch.DeleteCommand
.AcceptChangesDuringFill = True
.TableMappings.Add("Table" , "Batch")
.MissingSchemaAction = MissingSchemaAction.AddWit hKey
End With 'daBatch
With daBatch
.SelectCommand = New SqlCommand
With .SelectCommand
.Connection = myparent.EZGoesConnection
.CommandText = "SELECT BatchKey, BatchID, UserLogin, " + _
"PostDate, Comment, CreateDate, ImportDate " + _
"FROM gwi_tbl_Batch WHERE BatchID = @BatchID"
.Parameters.Add("@BatchID"
End With 'daBatch.SelectCommand
.InsertCommand = New SqlCommand
With .InsertCommand
.Connection = myparent.EZGoesConnection
.CommandText = "INSERT INTO dbo.gwi_tbl_Batch " + _
"(BatchID, UserLogin, PostDate, Comment, CreateDate, " + _
"ImportDate) VALUES (@BatchID, @UserLogin, @PostDate," + _
"@Comment, @CreateDate, @ImportDate)"
With .Parameters
.Add("@BatchID", SqlDbType.Int, 4, "BatchID")
.Add("@UserLogin", SqlDbType.VarChar, 12, "UserLogin")
.Add("@PostDate", SqlDbType.SmallDateTime, 4, "PostDate")
.Add("@Comment", SqlDbType.VarChar, 250, "Comment")
.Add("@CreateDate", SqlDbType.DateTime, 8, "CreateDate")
.Add("@ImportDate", SqlDbType.DateTime, 8, "ImportDate")
End With 'daBatch.InsertCommand.Par
End With 'daBatch.InsertCommand
.UpdateCommand = New SqlCommand
With .UpdateCommand
.Connection = myparent.EZGoesConnection
.CommandText = "UPDATE dbo.gwi_tbl_Batch " + _
"SET BatchID = @BatchID, UserLogin = @UserLogin, " + _
"PostDate = @PostDate, Comment = @Comment, " + _
"CreateDate = @CreateDate, ImportDate = @ImportDate " + _
"WHERE BatchKey = @BatchKey"
With .Parameters
.Add("@BatchKey", SqlDbType.Int, 4, "BatchKey")
.Add("@BatchID", SqlDbType.Int, 4, "BatchID")
.Add("@UserLogin", SqlDbType.VarChar, 12, "UserLogin")
.Add("@PostDate", SqlDbType.SmallDateTime, 4, "PostDate")
.Add("@Comment", SqlDbType.VarChar, 250, "Comment")
.Add("@CreateDate", SqlDbType.DateTime, 8, "CreateDate")
.Add("@ImportDate", SqlDbType.DateTime, 8, "ImportDate")
End With 'daBatch.UpdateCommand.Par
End With 'daBatch.UpdateCommand
.DeleteCommand = New SqlCommand
With .DeleteCommand
.Connection = myparent.EZGoesConnection
.CommandText = "DELETE FROM dbo.gwi_tbl_Batch " + _
"WHERE BatchKey = @BatchKey"
.Parameters.Add("@BatchKey
End With 'daBatch.DeleteCommand
.AcceptChangesDuringFill = True
.TableMappings.Add("Table"
.MissingSchemaAction = MissingSchemaAction.AddWit
End With 'daBatch
ASKER
This is an Access table, is it the same?
yes. for the sake of simplicity if you're just dealing with a single table look up oledbcommandbuilder in help.
ASKER
I got it working, caan you tell me how to refresh the datagrid?
ASKER
I got it, thanks.
I'm thinking that you should set the datagrid to a dataview of your table. All I can think is that deleting the row from the table doesn't actually remove it, it just sets its rowstate to "Deleted" so that the dataadapter will know what to do with it. If you use a dataview for your datagrid.datasource with its rowstatefilter set to .currentrows (default) it should take care of your refresh problem.
private dvLoans as new dataview
...
(somewhere after the fill command)
dvloans.table = dataset11.tables(0)
private dvLoans as new dataview
...
(somewhere after the fill command)
dvloans.table = dataset11.tables(0)
I would also recommend that you not base the table datarow to delete on the datagrid row as the user could have reordered if you allow that.
CType(dgAddedLoans.Binding