Recordset Grouping

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
Fri Jul 07 12:15:40 CDT 2006


<mstery@soon.com> wrote in message
news:1152289573.522176.189960@p79g2000cwp.googlegroups.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
Fri Jul 07 12:44:10 CDT 2006

mstery@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
Fri Jul 07 13:05:56 CDT 2006

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
Mon Jul 10 10:39:38 CDT 2006

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
Mon Jul 10 10:46:03 CDT 2006

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
Mon Jul 10 10:58:58 CDT 2006

mstery@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.