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"
rs1.MoveNext
Loop
Set rs1 = Nothing
End If
...what can I change to make it add distinct records only?
Thanks,
Kristi
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"
rs1.MoveNext
Loop
Set rs1 = Nothing
End If
...what can I change to make it add distinct records only?
Thanks,
Kristi