?
Solved

Deleting a row from datagrid

Posted on 2008-11-03
10
Medium Priority
?
785 Views
Last Modified: 2008-11-09
I'm trying to delete a row from a datagrid. That's not too hard. I use:
        If grdDataGrid.Row >= 0 Then
            Dim r As Integer = grdDataGrid.RowSel - 1                 '.Rows(grdDataGrid.Row).DataIndex
            If r >= 0 Then
                dt.Rows.RemoveAt(r)
            End If
        End If
The row disappears. But, if I close the form and reopen it, the row is still there. It is not deleted from the database. So I try to update the data adapter with:
        da.Update(dt)
        dt.AcceptChanges()
But I get the same result.
How do I update the database?

Thanks.

0
Comment
Question by:LeeLiam
  • 4
  • 4
  • 2
10 Comments
 
LVL 14

Expert Comment

by:Binuth
ID: 22873382
call      dt.AcceptChanges()  before da.Update(dt)
0
 

Author Comment

by:LeeLiam
ID: 22873424
Thanks, but it still does not update the database.
0
 
LVL 14

Expert Comment

by:Binuth
ID: 22873462
check da(SqlDataAdapter) has proper DeleteCommand and UpdateCommand....

or provide full source code
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:LeeLiam
ID: 22876423
I'm an old VB6 guy trying to learn .NET, so forgive my ignorance.

I don't know what a proper DeleteCommand is.

As for code, I'm binding a simple grid to a datatable with:
        Dim con As New OleDbConnection()
        Dim cmd As New OleDbCommand()
        cmd.Connection = OleConn
        cmd.CommandText = sttSQL
        da.SelectCommand = cmd
        da.Fill(dt)
        grdDataGrid.DataSource = dt

After I add a row, I save it back to the database with an Update button:
        Dim dt_changes As DataTable
        Dim cb As OleDbCommandBuilder
        Dim rows As Integer
        dt_changes = dt.GetChanges()
        If Not IsNothing(dt_changes) Then
            Try
                cb = New OleDbCommandBuilder(da)
                rows = da.Update(dt_changes)
                MsgBox(rows & " updated")
            Catch ex As Exception
                MsgBox("Can't save the changes!" & vbCrLf & ex.Message, _
                 MsgBoxStyle.Exclamation)
            End Try
        Else
            MsgBox("There's nothing to update")
        End If

After I delete a row with:
             dt.Rows.RemoveAt(r)
as stated above.

Then when I click the Update button, it says there is nothing to update and row is there the next time I open the form.

Let me know if you need more.

Thanks.
0
 
LVL 14

Expert Comment

by:Binuth
ID: 22882963
add following line of code

        Dim CB As OleDbCommandBuilder
        CB = New OleDbCommandBuilder(da)
        da.InsertCommand = CB.GetInsertCommand()
        da.UpdateCommand = CB.GetUpdateCommand()
        da.DeleteCommand = CB.GetDeleteCommand()

        Dim con As New OleDbConnection()
        Dim cmd As New OleDbCommand()
        cmd.Connection = OleConn
        cmd.CommandText = sttSQL
        da.SelectCommand = cmd
 
        Dim CB As OleDbCommandBuilder
        CB = New OleDbCommandBuilder(da)
        da.InsertCommand = CB.GetInsertCommand()
        da.UpdateCommand = CB.GetUpdateCommand()
        da.DeleteCommand = CB.GetDeleteCommand()
 
        da.Fill(dt)
        grdDataGrid.DataSource = dt

Open in new window

0
 

Author Comment

by:LeeLiam
ID: 22887093
I tried it, but it still does not delete from the database. When I close and reopen the form the deleted row reappears.

In looking at your links, it seems like perhaps I need to create a datarow, something like
Dim dr As DataRow =  ???
and then do a dr.Delete.

I just don't know what to put in the ???.

Or, am I way off base?

Thanks.

0
 
LVL 3

Expert Comment

by:Mezillinu
ID: 22916029
to delete a row from the datagrid, you have to

1) create a delete query and pass it through the database
2)it gets deleted from the db
3)return the new set of data
4)bind it to the grid

that way you remove it from the database, and then re-bind it with the set which has the row deleted. What you are doing is removing it from the datagrid, but not removing it from the db, that way, when getting the datafrom the database and re binding it - you are returning the row that you removed before.
0
 
LVL 3

Accepted Solution

by:
Mezillinu earned 2000 total points
ID: 22916047
The following code shows you how to delete a record from a database. It is using OLE DB, Microsoft Access, if you are using MS SQL - you specifiy SQL Client's data adapter and connection string.

Instead of tools.getXmlValue("DBConnStringErrorHandlingException"), specify you own connection string.

delete from tbl_emailTemplates where templatename = @templatename"

@templateName is used to compare the rows in the database, which in this case, if the templateName field in the database's table, tbl_emailTemplates, matches @templatename, it will be deleted.

I am passing a value in a textbox as the parameter here

  With SQL_Command
                    .Parameters.AddWithValue("@TemplateName", Me.txtTemplateName.Text)

note that is is best to compare ID's, not names, so I would recommend having the ID passed as a parameter, and delete the record which has that ID.

post if you need any more help

cheers
Dim SQL_Connection As New OleDbConnection
                Dim SQL_Command As New OleDbCommand
                Dim sSQL_Command As String = ""
 
                Dim tools As New Tools
                Dim databaseConnectionString As String = tools.getXmlValue("DBConnStringErrorHandlingException")
                SQL_Connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & databaseConnectionString
                SQL_Command.Connection = SQL_Connection
 
                'create the delete query to act on the DB
                sSQL_Command = "delete from tbl_emailTemplates where templatename = @templatename"
 
                With SQL_Command
                    .Parameters.AddWithValue("@TemplateName", Me.txtTemplateName.Text)
                    .CommandText = sSQL_Command
                End With
 
                SQL_Connection.Open()
                SQL_Command.ExecuteNonQuery()
                SQL_Connection.Close()

Open in new window

0
 

Author Comment

by:LeeLiam
ID: 22917435
Thanks, Mezillinu.

That's exactly what I needed.
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

Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

578 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