Accessing recordset from 'COMPUTE BY' clause

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Accessing recordset from 'COMPUTE BY' clause

Author

Topic

Nate
Starting Member

17 Posts

Posted - 2002-05-09 : 13:48:45

Any help on this would be appreciated. It may be a simple answer but it evades me.

I have created the following stored procedure:

CREATE PROCEDURE rp_ASPPayrollDetails @ResID Int, @PayPeriodCode VarChar (8) AS

Select th.th_TicketDate, pj.pj_ProjectDesc, it.it_ItemDesc, th.th_TicketNumber,
pt.pt_ActualQuantity, pt.pt_ActualUnitCode, pt.pt_ActualRate
FROM t_PayrollTransaction pt
INNER JOIN t_TicketProject tp ON
pt.pt_TicketProjectID = tp.tp_TicketProjectID
INNER JOIN t_Projects pj ON
tp.tp_ProjectID = pj.pj_ProjectID
INNER JOIN t_Items it ON
pt.pt_ItemCode = it.it_ItemCode AND
pt.pt_ItemCategoryCode = it.it_ItemCategoryCode
INNER JOIN t_TicketDetail td ON
td.td_TicketProjectID = pt.pt_TicketProjectID AND
td.td_LineNumber = pt.pt_TicketLineNumber
INNER JOIN t_TicketHeader th ON
td.td_TicketID = th.th_TicketID
INNER JOIN t_PayrollBatch pb ON
pt.pt_BatchID = pb.pb_BatchID
INNER JOIN t_PayrollPayPeriod pp ON
pp.pp_PeriodCode = pb.pb_PayrollPeriodCode
WHERE pt.pt_ResourceID = @ResID
and pp.pp_PeriodCode = @PayPeriodCode
Order By pt.pt_ActualUnitCode
Compute SUM(pt.pt_ActualQuantity) BY pt.pt_ActualUnitCode
GO

When I run this stored procedure in Query Analyzer, it gives me the information I want, which is the result sets broken up according to Unit Code and summed on each of these. However when I try to access the information in my ASP page I get a singular result set. Does anyone know how to access this data?

Thanks!

Nate
Starting Member

17 Posts

Posted - 2002-05-09 : 14:49:11

Found the answer.

objRSPayroll.NextRecordset

Ok, it was a simple answer.

Edited by - Nate on 05/09/2002 14:49:37

Scott
Posting Yak Master

145 Posts

Posted - 2002-05-21 : 07:51:47

OK, so I am being dumb!
How did you reference your values?
Compute SUM(pt.pt_ActualQuantity) does not have an (as x) so how did you reference them.
<%=objRSPayroll("???")%>

Thinking as I work, or is it working as I think!

This will reveal all:

set objRSPayroll = objRSPayroll.NextRecordset()
Response.Write "<Table border=1><THEAD><TR>"
For each ofield in objRSPayroll.Fields
Response.Write "<TH>" & ofield.name & "</TH>"
next
Response.Write "</TR><TR><TD>"
Response.Write objRSPayroll.GetString( , , "</TD><TD>", "</TD></TR><TR><TD>", " ")
Response.Write "</TD></TR></Table>"

OR

Thanks to some code on www.aspmessageboard.com

Response.write objRSPayroll(0)
Response.write objRSPayroll(1)
...

Edited by - scott on 05/21/2002 07:57:54

Edited by - scott on 05/21/2002 08:02:03

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources