Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2387
  • Last Modified:

Remove duplicates from VB combobox

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
                          Druid
                          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.adapter.Fill(filler)
        Me.dataset = New DataSet

        Me.command.Connection.Open()
        Me.adapter.Fill(Me.dataset)
        Me.command.Connection.Close()
        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 = ""
        difficultysearchComboBox.Items.Clear()
        opponentsearchComboBox.Items.Clear()
        opponentsearchComboBox1.Items.Clear()
        winsearchComboBox.Items.Clear()

    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")
        Else

            '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")
                Return
            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))

            Me.commandupdate.Connection.Open()
            Me.commandupdate.ExecuteNonQuery()
            Me.commandupdate.Connection.Close()
        End If

        'Call clearbox()
        Call datafill()

    End Sub

Thanks for the Help
0
JSmead
Asked:
JSmead
1 Solution
 
DhaestCommented:
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
or
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"))
0
 
JSmeadAuthor Commented:
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.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now