Loading Distinct Records into List Box 1

[kcorrigan]

I have a script running to an Oracle database that uses a distinct select statement. But my code that populates the list box is not populating distinct records; it is populating ALL records, so that some records are duplicates. (I only want to see one instance of each value)
For example, I am seeing:
A
A
B
C
C
C

instead of what I'd like to see:
A
B
C

Here is my code:

Dim strSQL1 As String
Dim rs1 As ADODB.Recordset
Set rs1 = New ADODB.Recordset

strSQL1 = "select distinct line_id from wh_kanban " & _
"where part_number = '" & Trim(txtPartNbr.Text) & "'"

Set rs1 = New ADODB.Recordset
rs1.LockType = adLockOptimistic
rs1.CursorType = adOpenDynamic
rs1.Source = strSQL1
Set rs1.ActiveConnection = cn1

rs1.Open
If (rs1.EOF = True) And (rs1.BOF = True) Then
rs1.Close
LstLine.AddItem "No Lines for Part"
Else
Do Until rs1.EOF
LstLine.AddItem rs1.fields("line_id&quot
rs1.MoveNext
Loop

Set rs1 = Nothing
End If

...what can I change to make it add distinct records only?

Thanks,
Kristi

 

[BKtechie]

try this
strSQL1 = "select line_id from wh_kanban " & _
"group by line_id having part_number = '" & _
Trim(txtPartNbr.Text) & "' group by line_id;"

This format always seems to work out ok for my SQL statements.

 

[kcorrigan]

I receive an error message saying "only one clause allowed per query block"

 

[CajunCenturion]

Although I don't understand why the "select distinct" is returning duplicate values, but that may be a different question.

Given this situation, one way that you may be able to workaround is the following:

Dim LastLineID as String
LastLineID = vbNullString

strSQL1 = "select distinct line_id from wh_kanban " & _
"where part_number = '" & Trim(txtPartNbr.Text) & "'"
strSQL1 = strSQL1 & " ORDER BY line_id"

...

Else
Do Until rs1.EOF
If (rs1.fields(&quot;line_id&quot <> LastLineId) Then
LastLineId = rs1.fields(&quot;line_id&quot
LstLine.AddItem LastLineId
End If
rs1.MoveNext
Loop
Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[kcorrigan]

Thank you! It works perfectly! I appreciate your help so much!