Listbox population

[DANZIG]

Hellos,

I'm tring to populate a listbox with items from a table space. The problem I'm having is dealing with duplicate items in the table being displayed in the listbox ( IE 75 Dell CPI's in the table become 75 individual option values in the listbox ). The select statement I'm using is below.


set oRs = oRsSWBI.Execute("select * from Workstation_Bios_Information WHERE SystemModel IS NOT NULL ORDER BY SystemName ASC&quot

Do While Not oRs.EOF
Response.Write &quot; <OPTION VALUE='&quot;&oRs(&quot;SystemModel&quot&&quot;'>&quot;&oRs(&quot;SystemModel&quot&&quot;</OPTION>&quot;
oRs.MoveNext
Loop
oRs.Close
Set oRs=Nothing


Any help with the correct statement would be greatly appreciated.

Thanks in advance.

 

[SQLBill]

Try the DISTINCT keyword:

select DISTINCT *

-SQLBill

 

[DANZIG]

Still returns the long listbox. 8(

 

[DANZIG]

Sorry you had it I just didn't look at it close enough.


set oRs = oRsSWBI.Execute(&quot;select DISTINCT SystemModel from SAPL_Workstation_Bios_Information&quot

Thanks 8)

 

[RPredrag]

Did you try to specify colum names you would like to populate in a list box. For example insted using this statement
select * from Workstation_Bios_Information WHERE SystemModel IS NOT NULL ORDER BY SystemName ASC

try this

select distinct colum_name1, colum_name2 from Workstation_Bios_Information WHERE SystemModel IS NOT NULL ORDER BY SystemName ASC

Check your SQL code in Query Analyzer see what are results retrun. The code above should return uniqe values.

 

[DANZIG]

I think I got that part of it worked out now I'm trying to figure out how to tie the action of the choosen items in to the button.

Here is what I have.

set oRsMType = oRsSWBI.Execute(&quot;select DISTINCT SystemModel from Workstation_Bios_Information&quot
Do While Not oRsMType.EOF
Response.Write &quot; <OPTION VALUE='&quot;&oRsMType(&quot;SystemModel&quot&&quot;'>&quot;&oRsMType(&quot;SystemModel&quot&&quot;</OPTION>&quot;
oRsMType.MoveNext
Loop
oRsMType.Close
Set oRsMType=Nothing

%>
</select></td>
<td width=&quot;57%&quot;></td>
</tr>
<tr>
<td width=&quot;25%&quot;><font face=&quot;Arial&quot;>Sort by Collection Date</font></td>
<td width=&quot;18%&quot;><select size=&quot;1&quot; name=&quot;Date&quot;>
<%
set oRsCDate = oRsSWBI.Execute(&quot;select DISTINCT CollectionDate from Workstation_Bios_Information&quot
Do While Not oRsCDate.EOF
Response.Write &quot; <OPTION VALUE='&quot;&oRsCDate(&quot;CollectionDate&quot&&quot;'>&quot;&oRsCDate(&quot;CollectionDate&quot&&quot;</OPTION>&quot;
oRsCDate.MoveNext
Loop
oRsCDate.Close
Set oRsCDate=Nothing

%>

</select></td>
<td width=&quot;57%&quot;><input type=&quot;submit&quot; value=&quot;Sort Now&quot; name=&quot;B1&quot;>   <input type=&quot;reset&quot; value=&quot;Reset&quot; name=&quot;B2&quot;></td>

What is the best way to display the resulst on the same page?

I'm wanting the users to get an initial listing of all of the information when they open the initial asp and them have the option to sort the results. I haven't quite figured out that part of it.

Any ideas?