• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 208
  • Last Modified:

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
0
Mylor
Asked:
Mylor
  • 5
  • 4
1 Solution
 
Brian CroweDatabase AdministratorCommented:
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
0
 
MylorAuthor Commented:
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.
0
 
Brian CroweDatabase AdministratorCommented:
you haven't defined a deletecommand for your adapter.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Brian CroweDatabase AdministratorCommented:
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
0
 
MylorAuthor Commented:
This is an Access table, is it the same?
0
 
Brian CroweDatabase AdministratorCommented:
yes.  for the sake of simplicity if you're just dealing with a single table look up oledbcommandbuilder in help.
0
 
MylorAuthor Commented:
I got it working, caan you tell me how to refresh the datagrid?
0
 
MylorAuthor Commented:
I got it, thanks.
0
 
Brian CroweDatabase AdministratorCommented:
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)
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now