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.CurrentRowIndex
Try
DataSet11.Tables(0).Rows(rno).Delete()
OleDbDataAdapter1.Fill()
OleDbDataAdapter1.Update(DataSet11)

Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub

Brian Crowe

You need to call the Update before you call the Fill.

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.BindingContext(dgAddedLoans.DataSource, dgAddedLoans.DataMember).Current, DataRowView).Row.delete

Mylor

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.

ASKER CERTIFIED SOLUTION

Brian Crowe

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Brian Crowe

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.Parameters
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.Parameters
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.AddWithKey
End With 'daBatch

Mylor

ASKER

This is an Access table, is it the same?

Brian Crowe

yes. for the sake of simplicity if you're just dealing with a single table look up oledbcommandbuilder in help.

Mylor

ASKER

I got it working, caan you tell me how to refresh the datagrid?

Mylor

ASKER

I got it, thanks.

Brian Crowe

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)