check for duplicate items in combobox

hi,

I have this combobox with these items

English
English
Math
Phisics
Math

These items are loaded from an access database. I want only one of each.
how do i check for duplicate items in a combobox, and if there are duplicate items, how do i remove them?


Thanks.

I'm curious - where did the duplicate row come from - what is the SQL you are using to get the recordset.

One of our 10 commandments in our shop is to "never use SELECT DISTINCT"...

can you give an example for the use of distinct and then adding it into the combo box?

thanks for the reply,

but that is not what i am looking for, because i do need the other data i the datarow

my access table looks like this

Code:

---

ID      Subject    Year            Mark      Date              Description
1      English      2005-2006    6.0        12-12-2005    Just A description
2      English      2005-2006    7.5        01-03-2006    Another Description
3      Math        2005-2006    8.0        02-03-2006    Description
4      Physics    2005-2006    7.2        15-03-2006    Just a Description
5      Math        2005-2006    6.8        23-04-2006      Description

---

And these entry are loaded in the combobox with this code

first i create a dataset

VB Code:

Private Sub DataSetCreateSubject()
        Dim oAdaptor As OleDbDataAdapter
        Dim strSql As String
        Dim strConn As String
 
        'Get Connection String
        strConn = ConnectionString()
 
        'Build SQL String
        strSql = "SELECT * FROM cijfers WHERE Year='" & cbYear.SelectedItem.ToString() & "' ORDER BY ID ASC"
 
        moDS = New DataSet
 
        Try
            'Create new Data Adapter
            oAdaptor = New OleDbDataAdapter(strSql, strConn)
            'Fill Dataset from Adaptor and give it a name
            oAdaptor.Fill(moDS, "Subject")
            'Create a Primary Key
            With moDS.Tables("Subject")
                .PrimaryKey = New DataColumn() _
                {.Columns("ID")}
                .Columns("ID").AutoIncrement = True
                .Columns("ID").AutoIncrementStep = 1
            End With
 
        Catch ex As Exception
            MessageBox.Show(ex.Message)
 
        End Try
    End Sub

then i load in the combobox

VB Code:

Private Sub ListLoadVakken()
        Dim oItem As listYears
        Dim oRow As DataRow
 
        cbSubject.Items.Clear()
        ' Loop through each row and get a DataRow
        For Each oRow In moDS.Tables("Vak").Rows
            ' Create New Item to hold PK and Description
            oItem = New listYears
            With oItem
                .ID = CInt(oRow.Item("ID"))
                .Value = oRow.Item("Vak").ToString()
            End With
 
            ' Add Item to list box
            cbSubject.Items.Add(oItem)
        Next
 
 
        If cbSubject.Items.Count = 0 Then
            MessageBox.Show("Er zijn nog geen vakken voor dit jaar toegevoegd. Voeg eerst een vak toe.")
            cbSubject.Text = ""
        Else : cbSubject.SelectedIndex = 0
        End If
 
    End Sub

So i am really looking for a way to search to combobox on duplicate items and then delete them

Thanks

Quote:

---

Originally Posted by kulrom

but why ... i've used DISTINCT many times before and i never noted any issue about that ... then i'm wondering why you recommend not using of DISTINCT?
:wave:

---

DISTINCT and GROUP BY have a place in SQL - that's for sure.

But I've seen too many people develop a poor join - resulting in "phantom" duplicate rows and then using DISTINCT to get rid of them.

In the past 3 years we have developed nearly 1000 STORED PROCEDURES and I cannot remember the time we used SELECT DISTINCT.

Prior to MS SQL SERVER 2000, SELECT DISTINCT would build the resultset and then apply the DISTINCT upon it - GROUP BY was better. Now they are the same.

And as you can see from the reply - DISTINCT isn't going to work in this case either.

As for the DUPLICATE issue in the COMBO box - search around the FORUM, I've seen API tricks on COMBOBOXES that get rid of duplicates...

Quote:

---

Originally Posted by szlamany

As for the DUPLICATE issue in the COMBO box - search around the FORUM, I've seen API tricks on COMBOBOXES that get rid of duplicates...

---

I searched the forums, but i can't find it. what keyword should i use?

Quote:

---

Originally Posted by digita

I searched the forums, but i can't find it. what keyword should i use?

---

It was tough to find - but here's a link:

Find a string in a combobox with API

what does it do, because i don't inderstand it, and how do i use it?

VB Code:

Option Explicit
 
Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hwnd As Long, _
                                                                        ByVal wMsg As Long, _
                                                                        ByVal wParam As Long, _
                                                                        lParam As Any) _
                                                                        As Long
 
Private Const CB_ERR = (-1)
Private Const CB_FINDSTRING = &H14C
Private Const CB_FINDSTRINGEXACT = &H158
 
Private Function FindItemByString(cmb As ComboBox, ByVal SearchFor As String, Optional FindExact As Boolean = False) As Integer
    FindItemByString = CInt(SendMessage(cmb.hwnd, IIf(FindExact, CB_FINDSTRINGEXACT, CB_FINDSTRING), _
                            CB_ERR, ByVal SearchFor))
End Function

Hey, that looks very simlar to my code I posted a long time ago. :D

What it does is use the sendmessage api to find in the passed combo box (depending on the exact or
like parameter being passed) a item with the same (exact or partial matching) combo item text. Then if it
finds it it will return the index integer of the found item.

VB Code:

'Usage for exact match:
Private Sub Command1_Click()
    Combo1.ListIndex = FindItemByString(Combo1, Combo1.Text, True)
End Sub

If its not found then the function returns a -1 which will not give the combo an error but rather a no
selection depending on the Style of the combo box.

HTH