CODEWITHSUNDEEP
vb6
JetJack
JetJack

Reputation: 988

How to avoid the duplicate values in List box

Table1

ID Division Dept

001 CS IT
002 CD Admin
003 AS Admin

I want to load the dept in the list box with out duplicate values

Tried Code

Dim rdoRs As New ADODB.Recordset
Dim record As Variant
Dim Div As Variant
Dim usr, MySQL As String
usr = "CD,AS,"
record = Split(usr, ",")
For Each Div In record
MySQL = "Select Distinct dept from table1 Where division = '" & div & "'"
   rdoRs.Open MySQL, conn1
   If rdoRs.RecordCount > 0 Then
      Do While Not rdoRs.EOF
         listbox1.AddItem rdoRs!dept
           rdoRs.MoveNext
           Loop
   End If
   rdoRs.Close
Next

Output

Listbox1

Admin 'Loaded for CD Division
Admin 'Loaded for AS Division

The above code is working fine but it is loading 2 times admin dept. in the list box. Because For Loop is loading the dept admin for CD, and again it is loading the dept admin for AS division.

I dont want to show duplicate values in the list box.

Expected Output

Listbox1

Admin  'Loaded for both CD and AS Division

How to do this in VB6.

Need VB6 Code Help.

Upvotes: 1

Views: 1855

Answers (2)

TimFoolery
TimFoolery

Reputation: 1925

Write a function to check if it's already in the list...

Public Function FindInList(theList as ListBox, theString as String)
    Dim i as Integer
    theString = LCase(Trim(theString))

    For i = 0 to theList.ListCount - 1
        If LCase(Trim(theList.List(i))) = theString then
            FindInList = i
            Exit Function
        End If
    Next i

    FindInList = -1
End Function

And then when you want to add things to the list, just do...

If FindInList(List1, StringToAdd) = -1 Then List1.AddItem(StringToAdd)

Upvotes: 1

Adriaan Stander
Adriaan Stander

Reputation: 166396

You can do this in the query

Change 

MySQL = "Select dept from table1 Where division = '" & div & "'"

to something like

MySQL = "Select DISTINCT dept from table1 Where division = '" & div & "'"

Upvotes: 0

Related Questions