Question Unable to delete row from database

[cypress1976]

I have a windows form which connects to multiple database tables. On my form, I have a combobox. I want to be able to hit the delete button and remove the row from the access database which corresponds with the selected combobox value. I've tried many variations of this in my code but am continually running into problems. Here is my relevant code thus far:

I have declared these items at that top of my code.

    Dim dbProvider As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
    Dim TheDatabase As String = "\Certifications.accdb;Persist Security Info=True"
    Dim MyDocumentsFolder As String = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments)
    Dim FullDatabasePath As String = MyDocumentsFolder & TheDatabase
    Dim strConnection As String = dbProvider & FullDatabasePath
    Dim con As New OleDb.OleDbConnection(strConnection)
    'string to load All Employees into combobox
    Dim strAllEmployees As String = "SELECT DISTINCT OFFICERS.LName + ', ' + OFFICERS.FName + ' - ' + OFFICERS.DSN as FullName from OFFICERS"

Then here is the code for my delete button

con.Open()
        Dim dataAdapter As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter()
        Dim command As OleDb.OleDbCommand
        Dim parameter As OleDb.OleDbParameter

        'this takes the value from the combobox and pulls out only the DSN field
        Dim strcboEmployeeSelectedValue As String = cboEmployee.SelectedValue.ToString
        Dim strNewValue As String = strcboEmployeeSelectedValue.Split("- ").Last()


        ' Your DELETE command.
        command = New OleDb.OleDbCommand("DELETE * FROM OFFICERS WHERE DSN = ?", con)
        parameter = command.Parameters.AddWithValue("?", strNewValue)
        parameter.SourceVersion = DataRowVersion.Original
        dataAdapter.DeleteCommand = command
        command.ExecuteNonQuery()

I have tried other ways of deleting the row based on the combobox selection but none seem to work. I have performed code like this in the past and am not sure what I am missing. Thank you.

 

[jmcilhinney]

What you're trying to do is very, very easy. Firstly, create your data adapter with the appropriate SELECT command, which will include retrieving the PK column for the table and whatever other data you want. You should also set the DeleteCommand property of that adapter with an appropriate DELETE statement or use a command builder to generate one for you. Call Fill on the adapter to populate a DataTable, bind that that to a BindingSource and bind that to your ComboBox. To delete the DataRow currently selected in the ComboBox, simply call RemoveCurrent on the BindingSource. To save the changes from the DataTable back to the database, call Update on the adapter. You can do that immediately after deleting the row or wait for multiple edits and save as a batch. E.g.

Imports System.Data.OleDb

Public Class Form1

    Private connection As OleDbConnection
    Private adapter As OleDbDataAdapter
    Private table As DataTable

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        connection = New OleDbConnection("connection string here")
        adapter = New OleDbDataAdapter("SELECT ID, Name FROM MyTable", connection)
        table = New DataTable

        Dim command = New OleDbCommand("DELETE MyTable WHERE ID = @ID", connection)

        command.Parameters.Add("@ID", OleDbType.Integer, 0, "ID")
        adapter.DeleteCommand = command

        adapter.Fill(table)
        BindingSource1.DataSource = table

        With ComboBox1
            .DisplayMember = "Name"
            .ValueMember = "ID"
            .DataSource = BindingSource1
        End With
    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        BindingSource1.RemoveCurrent()
        adapter.Update(table)
    End Sub

End Class