Filtering a database query using a drop down list

[Scarecrow]

I have been told what I am wanting is easy to do, but I just can't figure it out. What I'd like to do is change my current database listing so that the heading column is a drop down filter, like the excel autofilter function.

Below is my current coding. I would like to replace the headings shown in lines 10-12 (position, team, ratings) with dropdown boxes populated with the results from the database.

<%
Dim rsItems
strSQL = &quot;SELECT * FROM Item &quot;
Set rsItems = Server.CreateObject(&quot;ADODB.Recordset&quot
rsItems.Open strSQL, objConn
If Not rsItems.EOF Then
Response.Write _
&quot;<TABLE BORDER=&quot;&quot;1&quot;&quot; CELLSPACING=&quot;&quot;2&quot;&quot; CELLPADDING=&quot;&quot;2&quot;&quot;>&quot; & _
&quot; <TR>&quot; & _
&quot; <TH>Name</TH>&quot; & _
&quot; <TH>Position</TH>&quot; & _
&quot; <TH>Team</TH>&quot; & _
&quot; <TH>Ratings</TH>&quot; & _
&quot; </TR>
Response.Write _
&quot;<TD><FONT SIZE=&quot;&quot;-1&quot;&quot;>&quot; & rsItems(&quot;ItemName&quot & &quot;</TD>&quot; & _
&quot;<TD><FONT SIZE=&quot;&quot;-1&quot;&quot;>&quot; & rsItems(&quot;Position&quot & &quot;</TD>&quot; & _
&quot;<TD><FONT SIZE=&quot;&quot;-1&quot;&quot;>&quot; & rsItems(&quot;Team&quot & &quot;</TD>&quot; & _
&quot;<TD><FONT SIZE=&quot;&quot;-1&quot;&quot;>&quot; & rsItems(&quot;plyrRatings&quot & &quot;</TD>&quot;
rsItems.MoveNext
Loop
Response.Write &quot;</TABLE>&quot;
rsItems.close
%>

If someone could show me a link to a good site that will show me in detail how to do this, or if it's real easy show me here, I would greatly appreciate it.

 

[themikehyde]

Scarecrow,
I am not sure what you mean. Do you want to be able to sort the data based on the field in the column?
Mike

 

[Scarecrow]

Yes.

For example, in the 'Position' field name of my 'Item' database table there will be SP, MR, CL, C, 1B, 2B... . I want it so that there is a drop down box in the top row (currently a header row that says 'Position') so that if I want to see just the Catchers, I can select 'C' from the drop down box, and only the Catchers will be displayed.

 

[contactop]

I think this code would get u the Drop Down with the data.
<%
Dim rsItems
strSQL = &quot;SELECT * FROM Item &quot;
Set rsItems = Server.CreateObject(&quot;ADODB.Recordset&quot
rsItems.Open strSQL, objConn
rsItems1.open strSQL, objConn
If Not rsItems.EOF Then
Response.Write _
&quot;<TABLE BORDER=&quot;&quot;1&quot;&quot; CELLSPACING=&quot;&quot;2&quot;&quot; CELLPADDING=&quot;&quot;2&quot;&quot;>&quot; & _
&quot; <TR>&quot; & _
&quot; <TH>Name</TH>&quot; & _
&quot; <TH>Position <select size=&quot;1&quot; name=&quot;D1&quot;>
<%
do while !rsItems1.eof

&quot;<option value=&quot;& rsItems1(&quot;Position&quot &&quot;>&quot; & rsItems1(&quot;Position&quot & &quot;</option>&quot;
rsItems1.movenext
loop
rsitems1.firstrecord
%></select></TH>&quot; & _
&quot; <TH>Team<select size=&quot;1&quot; name=&quot;D21&quot;>
<%
do while !rsItems1.eof

&quot;<option value=&quot;& rsItems1(&quot;Team&quot &&quot;>&quot; & rsItems1(&quot;Team&quot & &quot;</option>&quot;
rsItems1.movenext
loop
rsitems1.firstrecord
%></select></TH>&quot; & _
&quot; <TH>Ratings<select size=&quot;1&quot; name=&quot;D3&quot;>
<%
do while !rsItems1.eof

&quot;<option value=&quot;& rsItems1(&quot;plyrRatings&quot &&quot;>&quot; & rsItems1(&quot;plyrRatings&quot & &quot;</option>&quot;
rsItems1.movenext
loop
rsitems1.firstrecord
%></select></TH>&quot; & _
&quot; </TR>
Response.Write _
&quot;<TD><FONT SIZE=&quot;&quot;-1&quot;&quot;>&quot; & rsItems(&quot;ItemName&quot & &quot;</TD>&quot; & _
&quot;<TD><FONT SIZE=&quot;&quot;-1&quot;&quot;>&quot; & rsItems(&quot;Position&quot & &quot;</TD>&quot; & _
&quot;<TD><FONT SIZE=&quot;&quot;-1&quot;&quot;>&quot; & rsItems(&quot;Team&quot & &quot;</TD>&quot; & _
&quot;<TD><FONT SIZE=&quot;&quot;-1&quot;&quot;>&quot; & rsItems(&quot;plyrRatings&quot & &quot;</TD>&quot;
rsItems.MoveNext
Loop
Response.Write &quot;</TABLE>&quot;
rsItems.close
%>

now u can write a form eventwhen user selects a particular value of the drop down from any of the three fields,