Recordset Grouping

[mstery]

I am stumped as to how to do what appears to be a very simple thing.
Want to display the results of a query containing fields ID and Item,
grouped by the ID. I only want 1 instance of the ID to appear on the
page as a header for each group. There are 10 IDs in the query
results. Thought I could use <% Response.Write (rsName("Item")) WHERE
ID=1 %>, but doesn't work. How else could I accomplish this? Thanks
for any help--I only have very basic ASP knowledge.

 

[Slim]

I am stumped as to how to do what appears to be a very simple thing.
Want to display the results of a query containing fields ID and Item,
grouped by the ID. I only want 1 instance of the ID to appear on the
page as a header for each group. There are 10 IDs in the query
results. Thought I could use <% Response.Write (rsName("Item")) WHERE
ID=1 %>, but doesn't work. How else could I accomplish this? Thanks
for any help--I only have very basic ASP knowledge.

sql = "SELECT item FROM yourTable WHERE id = 1"

rsName.open sql, cnName

do until rsName.EOF

Response.Write (rsName("Item"))
rsName.MoveNext

loop

 

[Bob Barrows [MVP]]

I am stumped as to how to do what appears to be a very simple thing.
Want to display the results of a query containing fields ID and Item,
grouped by the ID. I only want 1 instance of the ID to appear on the
page as a header for each group. There are 10 IDs in the query
results. Thought I could use <% Response.Write (rsName("Item"))
WHERE ID=1 %>, but doesn't work. How else could I accomplish this?
Thanks for any help--I only have very basic ASP knowledge.

dim cn,rs,ar,id, sql, i
set cn=createobject("adodb.connection")
cn.open ...
set rs=cn.execute("select distinct id from yourtable",, 1)
if not rs.eof then ar=rs.getrows
rs.close
if isarray(ar) then
set rs=createobject("adodb.recordset")
rs.cursorlocation=3 'adUseClient
sql="select ID, Item from yourtable"
rs.open sql,cn,,,1
set rs.activeconection=nothing
cn.close:set cn=nothing
for i = 0 to ubound(ar,2)
id = ar(0,i)
response.write id
rs.filter = "ID=" & id
do until rs.eof
response.write "<div style=""margin-left:10"">"
response.write rs(1) & "</div>"
loop
next
rs.close: set rs=nothing
else
response.write "No data was returned"
end if

Bob Barrows

 

[mstery]

sql = "SELECT item FROM yourTable WHERE id = 1"

rsName.open sql, cnName

do until rsName.EOF

Response.Write (rsName("Item"))
rsName.MoveNext

loop

Thanks for the reply, but if I set the where clause in the query, I'm
going to have to have 10 queries on my page (one for each ID--which is
what I'm doing now) in order to display all the items under each ID. I
had hoped for a better way to do this, since it doesn't seem like I
should have to run all those queries just to group records by the ID #.
But maybe I'm wrong and running all those queries is the only way I
can do this?!?