Help with my Picklist Problemo

[GROOVYHEN]

I posted this as a comment in another thread, but it got no attention, so I'm giving it it's own thread.

Say I had an Access table that looked something like:

Album Title | Artist | Genre
----------------------------
album1 | AA | jazz
album2 | BT | jazz
album3 | CC | pop
album4 | DF | rock
album5 | LL | rock

I want the genre field (is field the correct term?) to be displayed in a picklist on page 1. Then the user would pick it, press submit, and on page 2, a table would appear displaying all the albums and artists of that particular genre. How could I do that?

 

[gc1234]

List contents in a table, the click action is a URL to get values using a query to get the results for page 2

ie:
loop through list
<a href="url.asp?genre="&rs("genre")&">"&rs("genre")&"</a>"

page 2

sql = "select namesofwhatever from yourtable where genre='"&Request.Querystring("genre")&"'"

set rs = yourconnection.execute(sql)

loop
'results
list rs("albumtitle") etc etc


Obviously all this with error trapping

 

[FesterSXS]

page1.asp

<%
strGenreSQL = "SELECT DISTINCT Genre FROM myTable ORDER BY Genre;"
Set objGenreRS = objConn.Execute(strGenreSQL)

If NOT objGenreRS.EOF Then
  Response.Write "<SELECT NAME='Genre'>" & vbcrlf

  While NOT objGenreRS.EOF
    strGenre = objGenreRS("Genre")
    Response.Write "<OPTION VALUE='" & strGenre & "'>" & strGenre & "</OPTION>" & vbcrlf
    objGenreRS.MoveNext
  Wend

  Response.Write "</SELECT>" & vbcrlf
End If

Set objGenreRS = Nothing
%>

page2.asp

<%
strGenre = Request.Form("Genre")
strMusicSQL = "SELECT * FROM myTable WHERE Genre='" & strGenre & "';"
Set objMusicRS = objConn.Execute(strMusicSQL)

If NOT objMusicRS.EOF Then
  While NOT objMusicRS.EOF
    Response.Write objMusicRS("Artist") & " - " & objMusicRS("Album Title") & "<BR>" & vbcrlf
    objMusicRS.MoveNext
  Wend
End If

Set objMusicRS = Nothing
%>

Tony

http://www.SpiraxSarco.com

 

[Tarwn]

For those who are answering, this is a duplicate of: thread333-823267