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.
Re: check for duplicate items in combobox
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"...
Re: check for duplicate items in combobox
can you give an example for the use of distinct and then adding it into the combo box?
Re: check for duplicate items in combobox
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
Re: check for duplicate items in combobox
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...
Re: check for duplicate items in combobox
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?
Re: check for duplicate items in combobox
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
Re: check for duplicate items in combobox
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
Re: check for duplicate items in combobox
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