?
Solved

How to delete a record from datagrid?

Posted on 2005-03-14
9
Medium Priority
?
201 Views
Last Modified: 2010-04-23
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
Comment
Question by:Mylor
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 13539059
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
 

Author Comment

by:Mylor
ID: 13539105
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
 
LVL 34

Accepted Solution

by:
Brian Crowe earned 2000 total points
ID: 13539125
you haven't defined a deletecommand for your adapter.
0
Industry Leaders: 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!

 
LVL 34

Expert Comment

by:Brian Crowe
ID: 13539139
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
 

Author Comment

by:Mylor
ID: 13539205
This is an Access table, is it the same?
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 13539262
yes.  for the sake of simplicity if you're just dealing with a single table look up oledbcommandbuilder in help.
0
 

Author Comment

by:Mylor
ID: 13539265
I got it working, caan you tell me how to refresh the datagrid?
0
 

Author Comment

by:Mylor
ID: 13539357
I got it, thanks.
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 13539395
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question