Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003
    Posts
    3

    Unanswered: SELECT DISTINCT trouble

    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

  2. #2
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    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.

  3. #3
    Join Date
    Feb 2002
    Posts
    2,232
    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)

  4. #4
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    ahhh,... that makes mroe sense...

    so his sql should be something like...

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

  5. #5
    Join Date
    Feb 2002
    Posts
    2,232
    Depending on what database he is using, the concatenation will vary - but yes.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •