none
VB2017 With MS Access Database - Deleting A Selected Row (Selected Using BindingSource) And Nav Buttons RRS feed

  • Question

  • Hi:

    Its been a while since I did database programming. I have a small app using a VB2017 Interface and an MS Access Database.

    I can add, edit and view rows of data but I can't for the life of me remember how to code the delete feature. Here is what I have:

      Private Sub btnDelete_Click(sender As Object, e As EventArgs) Handles btnDelete.Click
            'Delete_SE
            Dim Int As Integer = 0
            dsPSB.Tables("dtSE").Rows(0).Delete()
            DaSE.Update(dsPSB, "dtSE")
        End Sub


        Private Sub Delete_Command_SE()
            'Create An OleDb Command To Delete Service Data.
            SE_DeleteCommand.Connection = cnPSB
            SE_DeleteCommand.CommandType = CommandType.Text

            strSE_DeleteCommand = "DELETE * FROM SE WHERE SE_KEY = @SE_KEY"

            SE_DeleteCommand.CommandText = strSE_DeleteCommand
        End Sub

    I sure would appreciate some help.

    Thanks so much.

    John

    Saturday, February 2, 2019 8:27 PM

Answers

  • Part 2,

    To control how the BindingNavigator functions (this goes back to the project I mentioned in my first reply).

    Single click the BindingNavigator, select properties. Change the action from delete to none. Then double click the delete button in the BindingNavigator to create a click event.

    Here is an example of the click for delete (code comes from my project which uses a custom wrapper for MessageBox).

    Private Sub BindingNavigatorDeleteItem_Click(sender As Object, e As EventArgs) _
        Handles BindingNavigatorDeleteItem.Click
    
        If My.Dialogs.Question($"Remove '{bsCustomers.CurrentRow().Field(Of String)("CompanyName")}'?") Then
            Dim ops As New DatabaseOperations
            ops.RemoveCustomer(bsCustomers.CurrentRow().Field(Of Integer)("Identifier"))
        End If
    End Sub

    IMPORTANT

    You should also handle the event UserDeleteingRow of the DataGridView if using one. This code comes from another project.

    ''' <summary>
    ''' Prompt to remove current record/customer from selecting row in the DataGridView
    ''' and pressing DEL key.
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    Private Sub DataGridView1_UserDeletingRow(sender As Object, e As DataGridViewRowCancelEventArgs) Handles DataGridView1.UserDeletingRow
        e.Cancel = PromptRowRemoval()
    End Sub
    ''' <summary>
    ''' Prompt to remove current customer, default button is "No"
    ''' </summary>
    ''' <returns>True if "Yes" selected and removed successfully, False if replied "No" or failed to remove record</returns>
    Private Function PromptRowRemoval() As Boolean
        Dim result As Boolean = False
        If My.Dialogs.Question($"Remove '{bsCustomers.CurrentRowValue("CompanyName")}'?") Then
            If ops.RemoveCurrentCustomer(bsCustomers.CurrentRow.Field(Of Int32)("Identifier")) Then
                bsCustomers.RemoveCurrent()
                bsCustomers.DataTable.AcceptChanges()
                result = True
            Else
                If Not ops.IsSuccessFul Then
                    MessageBox.Show($"Removal failed: {ops.LastExceptionMessage}")
                End If
            End If
        End If
    
        Return Not result
    
    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

    Saturday, February 2, 2019 8:56 PM
    Moderator

All replies

  • Hello, here is a simple example. In the code below we delete a record in this case by the primary key. Once deleted use BindingSouce.RemoveCurrent.

    Public Sub RemoveCustomer(customerKey As Integer)
    
        Using cn As New OleDbConnection(ConnectionString)
            Using cmd As New OleDbCommand With {.Connection = cn}
                cmd.CommandText = "DELETE FROM Customers WHERE Identifier = ?"
                cmd.Parameters.AddWithValue("?", customerKey)
                Try
                    cn.Open()
                    cmd.ExecuteNonQuery()
                Catch ex As Exception
                    ' handle exception
                End Try
    
            End Using
        End Using
    
    End Sub
    Now how would we know in the above if the record was removed as this is a sub? Either put it in a button click or in a class which has special interfaces. See the following project for a advance complete example.


    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

    Saturday, February 2, 2019 8:47 PM
    Moderator
  • Part 2,

    To control how the BindingNavigator functions (this goes back to the project I mentioned in my first reply).

    Single click the BindingNavigator, select properties. Change the action from delete to none. Then double click the delete button in the BindingNavigator to create a click event.

    Here is an example of the click for delete (code comes from my project which uses a custom wrapper for MessageBox).

    Private Sub BindingNavigatorDeleteItem_Click(sender As Object, e As EventArgs) _
        Handles BindingNavigatorDeleteItem.Click
    
        If My.Dialogs.Question($"Remove '{bsCustomers.CurrentRow().Field(Of String)("CompanyName")}'?") Then
            Dim ops As New DatabaseOperations
            ops.RemoveCustomer(bsCustomers.CurrentRow().Field(Of Integer)("Identifier"))
        End If
    End Sub

    IMPORTANT

    You should also handle the event UserDeleteingRow of the DataGridView if using one. This code comes from another project.

    ''' <summary>
    ''' Prompt to remove current record/customer from selecting row in the DataGridView
    ''' and pressing DEL key.
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    Private Sub DataGridView1_UserDeletingRow(sender As Object, e As DataGridViewRowCancelEventArgs) Handles DataGridView1.UserDeletingRow
        e.Cancel = PromptRowRemoval()
    End Sub
    ''' <summary>
    ''' Prompt to remove current customer, default button is "No"
    ''' </summary>
    ''' <returns>True if "Yes" selected and removed successfully, False if replied "No" or failed to remove record</returns>
    Private Function PromptRowRemoval() As Boolean
        Dim result As Boolean = False
        If My.Dialogs.Question($"Remove '{bsCustomers.CurrentRowValue("CompanyName")}'?") Then
            If ops.RemoveCurrentCustomer(bsCustomers.CurrentRow.Field(Of Int32)("Identifier")) Then
                bsCustomers.RemoveCurrent()
                bsCustomers.DataTable.AcceptChanges()
                result = True
            Else
                If Not ops.IsSuccessFul Then
                    MessageBox.Show($"Removal failed: {ops.LastExceptionMessage}")
                End If
            End If
        End If
    
        Return Not result
    
    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

    Saturday, February 2, 2019 8:56 PM
    Moderator