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]

<ms****@soon.comwrote in message
news:11**********************@p79g2000cwp.googlegr oups.com...

>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]]

ms****@soon.com wrote:

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
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Bob Barrows [MVP]]

Darn! I left out the rs.movenext!!

Bob Barrows [MVP] wrote:

do until rs.eof
response.write "<div style=""margin-left:10"">"
response.write rs(1) & "</div>"

rs.movenext

loop

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[mstery]

Bob Barrows [MVP] wrote:

Darn! I left out the rs.movenext!!

Thanks for the reply, but I must be missing something - I keep getting
the error "Arguments are of the wrong type, are out of acceptable
range, or are in conflict with one another". Here's the code I was
using:

<%
dim cn,rs,ar,id, sql, i
set cn=createobject("adodb.connection")
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=database.mdb"
cn.open
set rs=cn.execute("select distinct CatID FROM Test",, 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 CatID, Equip from Test"
rs.open sql,cn,,,1
set rs.activeconnection=nothing
cn.close:set cn=nothing
for i = 0 to ubound(ar,2)
id = ar(0,i)
response.write id
rs.filter = "CatID=" & catid
do until rs.eof
response.write "<div style=""margin-left:10"">"
response.write rs(1) & "</div>"
rs.movenext
loop
next
else
response.write "No data was returned"
end if%>

Just so I'm clear--is this supposed to loop thru each of my 10 CatIDs
and display the Equip under each CatID on the same page? Or will this
only give me a single CatID's Equip records? I'm not sure if I was
clear on what I wanted--I need everything on the same page. Thanks
again, and apologies if I'm missing something.

[mstery]

Slim wrote:

>
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?!?

[Bob Barrows [MVP]]

ms****@soon.com wrote:

Bob Barrows [MVP] wrote:

>Darn! I left out the rs.movenext!!

Thanks for the reply, but I must be missing something - I keep getting
the error "Arguments are of the wrong type, are out of acceptable
range, or are in conflict with one another".

Never tell us an error message without telling us which line generated
the error

Here's the code I was
using:

<%
dim cn,rs,ar,id, sql, i
set cn=createobject("adodb.connection")
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=database.mdb"

I see that this connection string is on two lines. It should be a single
line. In fact, replace it with this:

cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=database.mdb"

If that's not the problem, then you need to tell me which line causes
the error. I obviously cannot debug this myself.

>
Just so I'm clear--is this supposed to loop thru each of my 10 CatIDs
and display the Equip under each CatID on the same page?

Yes

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.