Hello

I'm using the following to populate a drop down list with distinct values from three fields in a SQL database:

<select name="subArea" class="Text">
<option value="" selected></option>
<%
SELECTStmt = "SELECT DISTINCT subjectName,confSubjectArea2,confSubjectArea3 AS t1 "
FROMStmt = "FROM JPL_conf_DetailsView "
ORDERStmt = "ORDER BY t1 "
SQLStmt = SELECTStmt & FROMStmt & ORDERStmt
Set RS = Connection.Execute(SQLStmt)
Do While Not RS.EOF
cfSubject = RS.Fields("t1")
response.write("<option value='" & cfSubject & "'>" & cfSubject & "</option>" & vbCrLf)
RS.MoveNext
Loop
RS.close
%>
</select>

However, this only returns the following:

<select name="subArea" class="Text">
<option value="" selected></option>
<option value=''></option>
<option value=''></option>
<option value=''></option>
<option value=''></option>
<option value=''></option>
<option value=''></option>
<option value=''></option>
<option value=''></option>
<option value='Children (Private Law)'>Children (Private Law)</option>
</select>

It only seems to retrieve the very last value it finds. I'm not sure where I'm going wrong here - any help would be much appreciated.

Thanks

try executing the query in the query analyzer and seeing what you are getting returned. Ensure that your data is right. Looking at the code it appears okie.

You are only returning the last field when you issue the:
cfSubject = RS.Fields("t1")

If you are trying to concatenate all fields you can either do it in the sql statement or
cfSubject = RS.Fields(0) & RS.Fields(1) & RS.Fields(2)

ahhh,... that makes mroe sense...

so his sql should be something like...

SELECT DISTINCT subjectName + confSubjectArea2 + confSubjectArea3 AS t1 FROM JPL_conf_DetailsView... etc

Depending on what database he is using, the concatenation will vary - but yes.