none
update after deleting a record in datagridview RRS feed

  • Question

  • Hello
    The following code is used to delete a specific row in a DATAGRIDVIEW
    The code works
    The code does not update the data after deletion

    FULL CODE

     Public con As New OleDb.OleDbConnection("provider=microsoft.ace.oledb.12.0;data source=" & Application.StartupPath & "\ONLINESERVERTOME.accdb;Jet OLEDB:Database Password=12345")
        Public DataSet1 As New DataSet
        Public DataAdapter1 As New OleDbDataAdapter
        Sub conn()
            Dim cmd As OleDbCommand = New OleDbCommand("SELECT *  FROM TB_WEBSERVERPAYED", con)
            DataAdapter1 = New OleDbDataAdapter(cmd)
            Dim builder As OleDbCommandBuilder = New OleDbCommandBuilder(DataAdapter1)
            DataSet1.Clear()
            DataAdapter1.Fill(DataSet1, "TB_WEBSERVERPAYED")
            DATAGRID_SERVER.DataSource = DataSet1
            DATAGRID_SERVER.DataMember = "TB_WEBSERVERPAYED"
            DATAGRID_SERVER.Refresh()
    
        End Sub
        Private Sub SERVERME_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
            Try
                conn()
    
                DATAGRID_SERVER.Columns(0).Width = 40
                DATAGRID_SERVER.Columns(1).Width = 90
                DATAGRID_SERVER.Columns(2).Width = 110
                DATAGRID_SERVER.Columns(3).Width = 110
                DATAGRID_SERVER.Columns(4).Width = 110
                DATAGRID_SERVER.Columns(5).Width = 110
                DATAGRID_SERVER.Columns(6).Width = 150
    
    
                DATAGRID_SERVER.Columns(0).HeaderText = "ID"
                DATAGRID_SERVER.Columns(1).HeaderText = "WEB CODE"
                DATAGRID_SERVER.Columns(2).HeaderText = "WEB COSTDOLLAR"
                DATAGRID_SERVER.Columns(3).HeaderText = "WEB COSTCOUNTRY"
                DATAGRID_SERVER.Columns(4).HeaderText = "FULL PERIOD"
                DATAGRID_SERVER.Columns(5).HeaderText = "START DATE"
                DATAGRID_SERVER.Columns(6).HeaderText = "END DATE"
    
                DATAGRID_SERVER.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
                DATAGRID_SERVER.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
                count()
            Catch ex As Exception
    
            End Try
        End Sub
    
        Public Sub count()
            Dim records, current As Integer
            records = Me.BindingContext(DataSet1, "TB_WEBSERVERPAYED").Count
            current = Me.BindingContext(DataSet1, "TB_WEBSERVERPAYED").Position + 1
            Label2.Text = " COUNT RECORD  " & current.ToString & " FROM " & records.ToString
            For i As Integer = 0 To DATAGRID_SERVER.Rows.Count - 1
                DATAGRID_SERVER.Rows(i).HeaderCell.Value = (i + 1).ToString
            Next
        End Sub
    
    
        Private Sub BT_DELETE_Click(sender As System.Object, e As System.EventArgs) Handles BT_DELETE.Click
            Try
                Dim constr = "provider=microsoft.ace.oledb.12.0;data source=" & Application.StartupPath & "\ONLINESERVERTOME.accdb;Jet OLEDB:Database Password=12345"
                Dim con As OleDbConnection = New OleDbConnection(constr)
                Dim sql = "Delete From TB_WEBSERVERPAYED WHERE WEBSERVERPAYED_ID= @WEBSERVERPAYED_ID"
                Dim sda As OleDbDataAdapter = New OleDbDataAdapter(sql, constr)
                Dim com As OleDbCommand = New OleDbCommand(sql, con)
                con.Open()
                Dim indx As Integer = DATAGRID_SERVER.CurrentRow.Index
                com.Parameters.AddWithValue("@WEBSERVERPAYED_ID", DATAGRID_SERVER(0, indx).Value.ToString())
                com.ExecuteNonQuery()
                DATAGRID_SERVER.Refresh()
                conn()
                MsgBox("DELETE DON")
            Catch ex As Exception
                MsgBox("ERROR")
            Finally
                con.Close()
            End Try
        End Sub
    


    • Edited by ahmeddc Saturday, September 29, 2018 1:38 PM
    Saturday, September 29, 2018 1:38 PM

Answers

  • Hello,

    If you are open to a different approach that does not need a reload of data then check out the following MSDN code sample.

    Partial code from the code sample.

        Private Sub cmdRemoveCurrentRow_Click(ByVal sender As System.Object, ByVal e As EventArgs) _ 
            Handles ToolStripButton1.Click 
     
            If My.Dialogs.Question("Remove current customer?") Then 
                If ops.RemoveCurrentCustomer(bsCustomers.CurrentRow.Field(Of Int32)("Identifier")) Then 
                    bsCustomers.RemoveCurrent() 
                    bsCustomers.DataTable.AcceptChanges() 
                Else 
                    If Not ops.IsSuccessFul Then 
                        MessageBox.Show($"Removal failed: {ops.LastExceptionMessage}") 
                    End If 
                End If 
            End If 
        End Sub 
     

    Backing code

        ''' <summary> 
        ''' Remove a customer by primary key 
        ''' </summary> 
        ''' <param name="pIdentfier"></param> 
        ''' <returns></returns> 
        Public Function RemoveCurrentCustomer(ByVal pIdentfier As Integer) As Boolean 
            Try 
                Using cn As New OleDbConnection(Builder.ConnectionString) 
                    Using cmd As New OleDbCommand With {.Connection = cn} 
     
                        cmd.CommandText = "DELETE FROM Customer WHERE Identifier = @Identifier" 
     
                        Dim IdentifierParameter As New OleDbParameter With 
                            { 
                                .DbType = DbType.Int32, 
                                .ParameterName = "@Identifier", 
                                .Value = pIdentfier 
                            } 
                        cmd.Parameters.Add(IdentifierParameter) 
     
                        Try 
                            cn.Open() 
     
                            Dim Affected = cmd.ExecuteNonQuery 
                            Return Affected = 1 
     
                        Catch ex As Exception 
                            mHasException = True 
                            mLastException = ex 
                            Return False 
                        End Try 
                    End Using 
                End Using 
     
            Catch ex As Exception 
                mHasException = True 
                mLastException = ex 
                Return IsSuccessFul 
            End Try 
        End Function 
    


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by ahmeddc Sunday, September 30, 2018 4:50 PM
    Sunday, September 30, 2018 4:10 PM
    Moderator
  • Hi,

     DATAGRID_SERVER.Refresh() can't update DataGridview, please repopulate.

    Public Sub updateDGV()
            Using conn = New SqlConnection(constr)
                conn.Open()
                sda = New SqlDataAdapter("Select * From TableName", conn)
                dt = New DataTable()
                sda.Fill(dt)
                BS.DataSource = dt
                DataGridView1.DataSource = BS
            End Using
        End Sub

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by ahmeddc Sunday, September 30, 2018 4:50 PM
    Sunday, September 30, 2018 1:59 PM

All replies

  • Hi,

     DATAGRID_SERVER.Refresh() can't update DataGridview, please repopulate.

    Public Sub updateDGV()
            Using conn = New SqlConnection(constr)
                conn.Open()
                sda = New SqlDataAdapter("Select * From TableName", conn)
                dt = New DataTable()
                sda.Fill(dt)
                BS.DataSource = dt
                DataGridView1.DataSource = BS
            End Using
        End Sub

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by ahmeddc Sunday, September 30, 2018 4:50 PM
    Sunday, September 30, 2018 1:59 PM
  • >The code does not update the data after deletion

    To make it work you need update a DataSet which used as DataSource before you do Refresh().

    DataSet is "in memory" data and it not automatically synchronized with database.

    So, reload or remove row from DataSet too.


    Sincerely, Highly skilled coding monkey.

    Sunday, September 30, 2018 3:00 PM
  • Hello,

    If you are open to a different approach that does not need a reload of data then check out the following MSDN code sample.

    Partial code from the code sample.

        Private Sub cmdRemoveCurrentRow_Click(ByVal sender As System.Object, ByVal e As EventArgs) _ 
            Handles ToolStripButton1.Click 
     
            If My.Dialogs.Question("Remove current customer?") Then 
                If ops.RemoveCurrentCustomer(bsCustomers.CurrentRow.Field(Of Int32)("Identifier")) Then 
                    bsCustomers.RemoveCurrent() 
                    bsCustomers.DataTable.AcceptChanges() 
                Else 
                    If Not ops.IsSuccessFul Then 
                        MessageBox.Show($"Removal failed: {ops.LastExceptionMessage}") 
                    End If 
                End If 
            End If 
        End Sub 
     

    Backing code

        ''' <summary> 
        ''' Remove a customer by primary key 
        ''' </summary> 
        ''' <param name="pIdentfier"></param> 
        ''' <returns></returns> 
        Public Function RemoveCurrentCustomer(ByVal pIdentfier As Integer) As Boolean 
            Try 
                Using cn As New OleDbConnection(Builder.ConnectionString) 
                    Using cmd As New OleDbCommand With {.Connection = cn} 
     
                        cmd.CommandText = "DELETE FROM Customer WHERE Identifier = @Identifier" 
     
                        Dim IdentifierParameter As New OleDbParameter With 
                            { 
                                .DbType = DbType.Int32, 
                                .ParameterName = "@Identifier", 
                                .Value = pIdentfier 
                            } 
                        cmd.Parameters.Add(IdentifierParameter) 
     
                        Try 
                            cn.Open() 
     
                            Dim Affected = cmd.ExecuteNonQuery 
                            Return Affected = 1 
     
                        Catch ex As Exception 
                            mHasException = True 
                            mLastException = ex 
                            Return False 
                        End Try 
                    End Using 
                End Using 
     
            Catch ex As Exception 
                mHasException = True 
                mLastException = ex 
                Return IsSuccessFul 
            End Try 
        End Function 
    


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by ahmeddc Sunday, September 30, 2018 4:50 PM
    Sunday, September 30, 2018 4:10 PM
    Moderator