Remove duplicates from VB combobox

Posted on 2007-08-01
Last Modified: 2013-12-25
Hi Experts

I have two questions about my project. I'm using VB 2005 to connect to an Access database and populate a DataGridView and some combo boxes. The GridView and comboboxes both populate as they should. However, My question is:
           1)How do I remove duplicates from the combo boxes
                  What it displays now is:                         I would like it to display:
                          Mage                                                     Mage
                          Mage                                                     Hunter
                          Hunter                                                   Druid

I believe I could fix the problem with a DO While loop but I have several combo boxes and don't really want to have to write a DO While for each box. Also I can't clear the data out of the combo box. I get an error saying I cant remove the data while it's data source is bound.

My Code:

    Dim command As New OleDb.OleDbCommand
    Dim commandupdate As New OleDb.OleDbCommand
    Dim adapter As New OleDb.OleDbDataAdapter(command)
    Dim dataset As New DataSet
    Dim filler As New DataTable

    Private Sub datafill()
        Me.command = New OleDb.OleDbCommand("SELECT * FROM pvp", New OleDb.OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0; Data Source = C:\arenateam.mdb"))
        Me.adapter = New OleDb.OleDbDataAdapter(Me.command)
        Me.dataset = New DataSet

        difficultysearchComboBox.DataSource = filler
        difficultysearchComboBox.DisplayMember = "Difficulty"

        opponentsearchComboBox.DataSource = filler
        opponentsearchComboBox.DisplayMember = "P1"

        opponentsearchComboBox1.DataSource = filler
        opponentsearchComboBox1.DisplayMember = "P2"

        Me.pvpDataGridView.DataSource = Me.dataset.Tables(0).DefaultView
    End Sub

    Private Sub clearbox()
        classComboBox1.Text = ""
        classComboBox2.Text = ""
        difficultyComboBox.Text = ""
        winComboBox.Text = ""

    End Sub

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Call datafill()
    End Sub

    Private Sub submitButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles submitButton.Click
        'insert the record
        Dim iswin As Int32

        'validate and input new row
        If classComboBox1.Items.Contains(classComboBox1.Text) = False Or classComboBox2.Items.Contains(classComboBox2.Text) = False Or difficultyComboBox.Items.Contains(difficultyComboBox.Text) = False Then
            MessageBox.Show("Please fill in all boxes with items from the drop down boxes", "Ya Dumb Cracker")

            'set value for win combobox
            If winComboBox.Text = "Yes" Then
                iswin = 1
            ElseIf winComboBox.Text = "No" Then
                iswin = 0
            ElseIf winComboBox.Text <> "Yes" And winComboBox.Text <> "No" Then
                MessageBox.Show("Please Choose Yes or NO", "ZMG")
            End If

            Me.commandupdate = New OleDb.OleDbCommand("INSERT INTO pvp (P1,P2,Difficulty,Win) VALUES (?,?,?,?)", New OleDb.OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0; Data Source = C:\arenateam.mdb"))
            Me.commandupdate.Parameters.Add(New OleDb.OleDbParameter("@P1", classComboBox1.Text))
            Me.commandupdate.Parameters.Add(New OleDb.OleDbParameter("@P2", classComboBox2.Text))
            Me.commandupdate.Parameters.Add(New OleDb.OleDbParameter("@Difficulty", difficultyComboBox.Text))
            Me.commandupdate.Parameters.Add(New OleDb.OleDbParameter("@win", iswin))

        End If

        'Call clearbox()
        Call datafill()

    End Sub

Thanks for the Help
Question by:JSmead
    LVL 53

    Accepted Solution

    You only have to change your sql-statement: use a group-by function on the field or a distinct on that field
    SELECT DISTINCT <field> FROM pvp
    SELECT <field> FROM pvp GROUP BY <field>

    Me.command = New OleDb.OleDbCommand("SELECT <field> FROM pvp GROUP BY <field>", New OleDb.OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0; Data Source = C:\arenateam.mdb"))

    Author Comment

    Thanks for the reply. It is indeed working correctly. If you know how to clear a combo box that is bound to a data source. Please let me know.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
    When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

    794 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now