How to delete items from combo box which is connected to database and automatically refresh it?

Question

0.00/5 (No votes)

See more:

I have a form that is connected to my database. What I'm trying to do is when I click the delete button the selected item in the combo box should delete the entire row in my database and it should reload the combo box with values from the database.
Below is my code for the DELETE BUTTON it actually deletes the data but it doesn't update the value of my combo box instead it duplicates the value in my Combobox every time I click delete.

What I have tried:

Using cons As New OleDb.OleDbConnection(conSTR)

        cons.Open()
        Dim cmd As New OleDb.OleDbCommand("DELETE FROM Judges WHERE = @FullName")
        cmd.Parameters.AddWithValue("@FullName", cmbJudges.Text)
        MessageBox.Show("DATA HAS BEEN DELETED")
    End Using

    cmbJudges.Text = ""
    txtFullNameJudge.Text = Nothing
    txtContactJudge.Text = Nothing
    txtUserNameJudge.Text = Nothing
    txtPasswordJudge.Text = Nothing

    Using cons As OleDb.OleDbConnection = New OleDb.OleDbConnection(conSTR)

        Try

            With cmd
                .Connection = cons
                .Connection.Open()
                .CommandText = "SELECT FullName from Judges"
                rdr = cmd.ExecuteReader

                While rdr.Read
                    cmbJudges.Items.Add(rdr.Item(0))
                End While
                rdr.Close()
                .Connection.Close()
            End With

        Catch ex As Exception

            Dim test = MsgBox(ex.Message)

        End Try

    End Using

Posted 11-Mar-20 21:13pm

lelouch_vi 2

Updated 11-Mar-20 21:45pm

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2020-03-11T21:46:00

You need to clear combobox list before you start adding new values: ComboBox.ObjectCollection.Clear Method (System.Windows.Forms) | Microsoft Docs[^]
Or
You need to prevent adding duplicate values: Add and Remove Items from ComboBox, ListBox, or CheckedListBox Control - Windows Forms | Microsoft Docs[^]

Alternativelly, you can re-bind combobox's datasource:

VB.NET

Dim judge = cmbJudges.Text
Dim affected As Integer = 0
Dim dt As DataTable = New DataTable()
Dim comm As String() = {"DELETE FROM Judges WHERE FullName = @FullName", "SELECT FullName FROM Judges;"}
Using cons As OleDb.OleDbConnection = New OleDb.OleDbConnection(conSTR)
    'delete judge
	Using cmd As New OleDb.OleDbCommand(comm(0), cons)
        cons.Open()
		cmd.Parameters.AddWithValue("@FullName", judge)
		affected = cmd.ExecuteNonQuery()
		cons.Close()
	End Using
	'how many records has been affected?	
	If affected >0 Then
		'get list of judges
		Using cmd As New OleDb.OleDbCommand(comm(1), cons)
	        cons.Open()
	        Using rdr As OleDb.OleDbDataReader = cmd.ExecuteReader()
	        	dt.Load(rdr)
			End Using
	        cons.Close()
	    End Using
	End If
End Using

With cmbJudges
	.DataMember = "FullName"
	.ValueMember = "FullName"
	.DataSource = dt
End With