check for duplicate items in combobox

[digita]

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.

[szlamany]

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"...

[ryanlum]

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

[digita]

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

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

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

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

[szlamany]

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?

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...

[digita]

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?

[szlamany]

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

[digita]

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

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

[RobDog888]

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

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.

'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