Deleting a row from datagrid

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.

LeeLiamAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BinuthCommented:
call      dt.AcceptChanges()  before da.Update(dt)
0
LeeLiamAuthor Commented:
Thanks, but it still does not update the database.
0
BinuthCommented:
check da(SqlDataAdapter) has proper DeleteCommand and UpdateCommand....

or provide full source code
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

LeeLiamAuthor Commented:
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
BinuthCommented:
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
LeeLiamAuthor Commented:
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
MezillinuCommented:
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
MezillinuCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LeeLiamAuthor Commented:
Thanks, Mezillinu.

That's exactly what I needed.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.