NextRecordSet / Sproc Problem - SQL Server Forums

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

Transact-SQL (2000)

NextRecordSet / Sproc Problem

Author

Topic

kirkeby
Yak Posting Veteran

57 Posts

Posted - 2005-03-22 : 16:41:45

Hi there. Hope someone can help me. I have a stored procedure that returns 4 recordsets but I am only able to access 2 of them in my ASP page. It keeps telling me it can't find it in the ordinal or that it's not allowed when the object is closed. Here is my stored procedure:

CREATE PROCEDURE dbo.usp_QtrlyRptByType (
@Year datetime
)
AS
SET NOCOUNT ON

IF CONVERT(CHAR(10),@Year,101) = '01/01/1999'
BEGIN
-- Type of work performed by year, monthly breakdown
SELECT DISTINCT
ReqTypeDef.Type,
COUNT(CASE WHEN ReqDomains.Completed_Date >= '01/01/1999' AND ReqDomains.Completed_Date <= '01/31/1999' THEN 1 ELSE NULL END) AS Jan,
COUNT(CASE WHEN ReqDomains.Completed_Date >= '02/01/1999' AND ReqDomains.Completed_Date <= '02/28/1999' THEN 1 ELSE NULL END) AS Feb,
COUNT(CASE WHEN ReqDomains.Completed_Date >= '03/01/1999' AND ReqDomains.Completed_Date <= '03/31/1999' THEN 1 ELSE NULL END) AS Mar,
COUNT(CASE WHEN ReqDomains.Completed_Date >= '04/01/1999' AND ReqDomains.Completed_Date <= '04/30/1999' THEN 1 ELSE NULL END) AS Apr,
COUNT(CASE WHEN ReqDomains.Completed_Date >= '05/01/1999' AND ReqDomains.Completed_Date <= '05/31/1999' THEN 1 ELSE NULL END) AS May,
COUNT(CASE WHEN ReqDomains.Completed_Date >= '06/01/1999' AND ReqDomains.Completed_Date <= '06/30/1999' THEN 1 ELSE NULL END) AS Jun,
COUNT(CASE WHEN ReqDomains.Completed_Date >= '07/01/1999' AND ReqDomains.Completed_Date <= '07/31/1999' THEN 1 ELSE NULL END) AS Jul,
COUNT(CASE WHEN ReqDomains.Completed_Date >= '08/01/1999' AND ReqDomains.Completed_Date <= '08/31/1999' THEN 1 ELSE NULL END) AS Aug,
COUNT(CASE WHEN ReqDomains.Completed_Date >= '09/01/1999' AND ReqDomains.Completed_Date <= '09/30/1999' THEN 1 ELSE NULL END) AS Sep,
COUNT(CASE WHEN ReqDomains.Completed_Date >= '10/01/1999' AND ReqDomains.Completed_Date <= '10/31/1999' THEN 1 ELSE NULL END) AS Oct,
COUNT(CASE WHEN ReqDomains.Completed_Date >= '11/01/1999' AND ReqDomains.Completed_Date <= '11/30/1999' THEN 1 ELSE NULL END) AS Nov,
COUNT(CASE WHEN ReqDomains.Completed_Date >= '12/01/1999' AND ReqDomains.Completed_Date <= '12/31/1999' THEN 1 ELSE NULL END) AS 'Dec',
COUNT(CASE WHEN ReqDomains.Completed_Date >= '01/01/1999' AND ReqDomains.Completed_Date <= '12/31/1999' THEN ReqTypeDef.Type_ID ELSE NULL END) AS Total
FROM ReqDomains RIGHT JOIN
ReqTypeDef ON ReqDomains.Type_ID = ReqTypeDef.Type_ID
WHERE (ReqTypeDef.Active = 1) --AND (CHARINDEX(@Year,CONVERT(CHAR(10),ReqDomains.Completed_Date,101)) > 0)
GROUP BY ReqTypeDef.Type
-- Monthly Totals for type of work done, by year
SELECT
COUNT(CASE WHEN ReqDomains.Completed_Date >= '01/01/1999' AND ReqDomains.Completed_Date <= '01/31/1999' THEN 1 ELSE NULL END) AS JanTotal,
COUNT(CASE WHEN ReqDomains.Completed_Date >= '02/01/1999' AND ReqDomains.Completed_Date <= '02/28/1999' THEN 1 ELSE NULL END) AS FebTotal,
COUNT(CASE WHEN ReqDomains.Completed_Date >= '03/01/1999' AND ReqDomains.Completed_Date <= '03/31/1999' THEN 1 ELSE NULL END) AS MarTotal,
COUNT(CASE WHEN ReqDomains.Completed_Date >= '04/01/1999' AND ReqDomains.Completed_Date <= '04/30/1999' THEN 1 ELSE NULL END) AS AprTotal,
COUNT(CASE WHEN ReqDomains.Completed_Date >= '05/01/1999' AND ReqDomains.Completed_Date <= '05/31/1999' THEN 1 ELSE NULL END) AS MayTotal,
COUNT(CASE WHEN ReqDomains.Completed_Date >= '06/01/1999' AND ReqDomains.Completed_Date <= '06/30/1999' THEN 1 ELSE NULL END) AS JunTotal,
COUNT(CASE WHEN ReqDomains.Completed_Date >= '07/01/1999' AND ReqDomains.Completed_Date <= '07/31/1999' THEN 1 ELSE NULL END) AS JulTotal,
COUNT(CASE WHEN ReqDomains.Completed_Date >= '08/01/1999' AND ReqDomains.Completed_Date <= '08/31/1999' THEN 1 ELSE NULL END) AS AugTotal,
COUNT(CASE WHEN ReqDomains.Completed_Date >= '09/01/1999' AND ReqDomains.Completed_Date <= '09/30/1999' THEN 1 ELSE NULL END) AS SepTotal,
COUNT(CASE WHEN ReqDomains.Completed_Date >= '10/01/1999' AND ReqDomains.Completed_Date <= '10/31/1999' THEN 1 ELSE NULL END) AS OctTotal,
COUNT(CASE WHEN ReqDomains.Completed_Date >= '11/01/1999' AND ReqDomains.Completed_Date <= '11/30/1999' THEN 1 ELSE NULL END) AS NovTotal,
COUNT(CASE WHEN ReqDomains.Completed_Date >= '12/01/1999' AND ReqDomains.Completed_Date <= '12/31/1999' THEN 1 ELSE NULL END) AS DecTotal,
COUNT(CASE WHEN ReqDomains.Completed_Date >= '01/01/1999' AND ReqDomains.Completed_Date <= '12/31/1999' THEN 1 ELSE NULL END) AS YearlyTotal
FROM ReqDomains
-- Quarterly totals by type
SELECT DISTINCT
COUNT(CASE WHEN ReqDomains.Completed_Date >= '01/01/1999' AND ReqDomains.Completed_Date <= '03/31/1999' AND ReqTypeDef.Type_ID = 1 THEN 1 ELSE NULL END) AS Q1DC,
COUNT(CASE WHEN ReqDomains.Completed_Date >= '01/01/1999' AND ReqDomains.Completed_Date <= '03/31/1999' AND ReqTypeDef.Type_ID = 2 THEN 1 ELSE NULL END) AS Q1DD,
COUNT(CASE WHEN ReqDomains.Completed_Date >= '01/01/1999' AND ReqDomains.Completed_Date <= '03/31/1999' AND ReqTypeDef.Type_ID = 3 THEN 1 ELSE NULL END) AS Q1DR,
COUNT(CASE WHEN ReqDomains.Completed_Date >= '01/01/1999' AND ReqDomains.Completed_Date <= '03/31/1999' AND ReqTypeDef.Type_ID = 4 THEN 1 ELSE NULL END) AS Q1DRe,
COUNT(CASE WHEN ReqDomains.Completed_Date >= '01/01/1999' AND ReqDomains.Completed_Date <= '03/31/1999' AND ReqTypeDef.Type_ID = 5 THEN 1 ELSE NULL END) AS Q1DNS,
COUNT(CASE WHEN ReqDomains.Completed_Date >= '01/01/1999' AND ReqDomains.Completed_Date <= '03/31/1999' AND ReqTypeDef.Type_ID = 6 THEN 1 ELSE NULL END) AS Q1DT,
COUNT(CASE WHEN ReqDomains.Completed_Date >= '01/01/1999' AND ReqDomains.Completed_Date <= '03/31/1999' AND ReqTypeDef.Type_ID = 7 THEN 1 ELSE NULL END) AS Q1ME,
COUNT(CASE WHEN ReqDomains.Completed_Date >= '01/01/1999' AND ReqDomains.Completed_Date <= '03/31/1999' AND ReqTypeDef.Type_ID = 8 THEN 1 ELSE NULL END) AS Q1IP,
COUNT(CASE WHEN ReqDomains.Completed_Date >= '01/01/1999' AND ReqDomains.Completed_Date <= '03/31/1999' AND ReqTypeDef.Type_ID = 9 THEN 1 ELSE NULL END) AS Q1RO,
COUNT(CASE WHEN ReqDomains.Completed_Date >= '04/01/1999' AND ReqDomains.Completed_Date <= '06/30/1999' AND ReqTypeDef.Type_ID = 1 THEN 1 ELSE NULL END) AS Q2DC,
COUNT(CASE WHEN ReqDomains.Completed_Date >= '04/01/1999' AND ReqDomains.Completed_Date <= '06/30/1999' AND ReqTypeDef.Type_ID = 2 THEN 1 ELSE NULL END) AS Q2DD,
COUNT(CASE WHEN ReqDomains.Completed_Date >= '04/01/1999' AND ReqDomains.Completed_Date <= '06/30/1999' AND ReqTypeDef.Type_ID = 3 THEN 1 ELSE NULL END) AS Q2DR,
COUNT(CASE WHEN ReqDomains.Completed_Date >= '04/01/1999' AND ReqDomains.Completed_Date <= '06/30/1999' AND ReqTypeDef.Type_ID = 4 THEN 1 ELSE NULL END) AS Q2DRe,
COUNT(CASE WHEN ReqDomains.Completed_Date >= '04/01/1999' AND ReqDomains.Completed_Date <= '06/30/1999' AND ReqTypeDef.Type_ID = 5 THEN 1 ELSE NULL END) AS Q2DNS,
COUNT(CASE WHEN ReqDomains.Completed_Date >= '04/01/1999' AND ReqDomains.Completed_Date <= '06/30/1999' AND ReqTypeDef.Type_ID = 6 THEN 1 ELSE NULL END) AS Q2DT,
COUNT(CASE WHEN ReqDomains.Completed_Date >= '04/01/1999' AND ReqDomains.Completed_Date <= '06/30/1999' AND ReqTypeDef.Type_ID = 7 THEN 1 ELSE NULL END) AS Q2ME,
COUNT(CASE WHEN ReqDomains.Completed_Date >= '04/01/1999' AND ReqDomains.Completed_Date <= '06/30/1999' AND ReqTypeDef.Type_ID = 8 THEN 1 ELSE NULL END) AS Q2IP,
COUNT(CASE WHEN ReqDomains.Completed_Date >= '04/01/1999' AND ReqDomains.Completed_Date <= '06/30/1999' AND ReqTypeDef.Type_ID = 9 THEN 1 ELSE NULL END) AS Q2RO,
COUNT(CASE WHEN ReqDomains.Completed_Date >= '07/01/1999' AND ReqDomains.Completed_Date <= '09/30/1999' AND ReqTypeDef.Type_ID = 1 THEN 1 ELSE NULL END) AS Q3DC,
COUNT(CASE WHEN ReqDomains.Completed_Date >= '07/01/1999' AND ReqDomains.Completed_Date <= '09/30/1999' AND ReqTypeDef.Type_ID = 2 THEN 1 ELSE NULL END) AS Q3DD,
COUNT(CASE WHEN ReqDomains.Completed_Date >= '07/01/1999' AND ReqDomains.Completed_Date <= '09/30/1999' AND ReqTypeDef.Type_ID = 3 THEN 1 ELSE NULL END) AS Q3DR,
COUNT(CASE WHEN ReqDomains.Completed_Date >= '07/01/1999' AND ReqDomains.Completed_Date <= '09/30/1999' AND ReqTypeDef.Type_ID = 4 THEN 1 ELSE NULL END) AS Q3DRe,
COUNT(CASE WHEN ReqDomains.Completed_Date >= '07/01/1999' AND ReqDomains.Completed_Date <= '09/30/1999' AND ReqTypeDef.Type_ID = 5 THEN 1 ELSE NULL END) AS Q3DNS,
COUNT(CASE WHEN ReqDomains.Completed_Date >= '07/01/1999' AND ReqDomains.Completed_Date <= '09/30/1999' AND ReqTypeDef.Type_ID = 6 THEN 1 ELSE NULL END) AS Q3DT,
COUNT(CASE WHEN ReqDomains.Completed_Date >= '07/01/1999' AND ReqDomains.Completed_Date <= '09/30/1999' AND ReqTypeDef.Type_ID = 7 THEN 1 ELSE NULL END) AS Q3ME,
COUNT(CASE WHEN ReqDomains.Completed_Date >= '07/01/1999' AND ReqDomains.Completed_Date <= '09/30/1999' AND ReqTypeDef.Type_ID = 8 THEN 1 ELSE NULL END) AS Q3IP,
COUNT(CASE WHEN ReqDomains.Completed_Date >= '07/01/1999' AND ReqDomains.Completed_Date <= '09/30/1999' AND ReqTypeDef.Type_ID = 9 THEN 1 ELSE NULL END) AS Q3RO,
COUNT(CASE WHEN ReqDomains.Completed_Date >= '10/01/1999' AND ReqDomains.Completed_Date <= '12/31/1999' AND ReqTypeDef.Type_ID = 1 THEN 1 ELSE NULL END) AS Q4DC,
COUNT(CASE WHEN ReqDomains.Completed_Date >= '10/01/1999' AND ReqDomains.Completed_Date <= '12/31/1999' AND ReqTypeDef.Type_ID = 2 THEN 1 ELSE NULL END) AS Q4DD,
COUNT(CASE WHEN ReqDomains.Completed_Date >= '10/01/1999' AND ReqDomains.Completed_Date <= '12/31/1999' AND ReqTypeDef.Type_ID = 3 THEN 1 ELSE NULL END) AS Q4DR,
COUNT(CASE WHEN ReqDomains.Completed_Date >= '10/01/1999' AND ReqDomains.Completed_Date <= '12/31/1999' AND ReqTypeDef.Type_ID = 4 THEN 1 ELSE NULL END) AS Q4DRe,
COUNT(CASE WHEN ReqDomains.Completed_Date >= '10/01/1999' AND ReqDomains.Completed_Date <= '12/31/1999' AND ReqTypeDef.Type_ID = 5 THEN 1 ELSE NULL END) AS Q4DNS,
COUNT(CASE WHEN ReqDomains.Completed_Date >= '10/01/1999' AND ReqDomains.Completed_Date <= '12/31/1999' AND ReqTypeDef.Type_ID = 6 THEN 1 ELSE NULL END) AS Q4DT,
COUNT(CASE WHEN ReqDomains.Completed_Date >= '10/01/1999' AND ReqDomains.Completed_Date <= '12/31/1999' AND ReqTypeDef.Type_ID = 7 THEN 1 ELSE NULL END) AS Q4ME,
COUNT(CASE WHEN ReqDomains.Completed_Date >= '10/01/1999' AND ReqDomains.Completed_Date <= '12/31/1999' AND ReqTypeDef.Type_ID = 8 THEN 1 ELSE NULL END) AS Q4IP,
COUNT(CASE WHEN ReqDomains.Completed_Date >= '10/01/1999' AND ReqDomains.Completed_Date <= '12/31/1999' AND ReqTypeDef.Type_ID = 9 THEN 1 ELSE NULL END) AS Q4RO
FROM ReqDomains RIGHT JOIN
ReqTypeDef ON ReqDomains.Type_ID = ReqTypeDef.Type_ID
WHERE (ReqTypeDef.Active = 1) --AND (CHARINDEX(@Year,CONVERT(CHAR(10),ReqDomains.Completed_Date,101)) > 0)
GROUP BY ReqTypeDef.Type
-- Quarterly totals
SELECT DISTINCT
COUNT(CASE WHEN ReqDomains.Completed_Date >= '01/01/1999' AND ReqDomains.Completed_Date <= '03/31/1999' THEN 1 ELSE NULL END) AS Q1Total,
COUNT(CASE WHEN ReqDomains.Completed_Date >= '04/01/1999' AND ReqDomains.Completed_Date <= '06/30/1999' THEN 1 ELSE NULL END) AS Q2Total,
COUNT(CASE WHEN ReqDomains.Completed_Date >= '07/01/1999' AND ReqDomains.Completed_Date <= '09/30/1999' THEN 1 ELSE NULL END) AS Q3Total,
COUNT(CASE WHEN ReqDomains.Completed_Date >= '10/01/1999' AND ReqDomains.Completed_Date <= '12/31/1999' THEN 1 ELSE NULL END) AS Q4Total
FROM ReqDomains
END

return
GO

From Query Analyzer, the results come back just fine. From my ASP page, using NextRecordset, the results aren't so good. After the first 2 recordsets, it barfs on me. I tested it by removing the last part that grabs what I believe to be recordsets 3 and 4 and it displays recordsets 1 and 2 beautifully in the browser. I also tried not killing my objects (at the bottom of the asp page) but it didn't do anything. Here is my code:

<%@ Language=VBScript %>
<%
Response.ContentType = "application/vnd.ms-excel"
%>


<%
'-----------------------------------------------------------
'--- Declare all variables to be used
'-----------------------------------------------------------
dim objRS '--- The ADO Recordset that stores the data.
dim intRow '--- A loop counter variable.
dim intCol '--- A loop counter variable.
dim intPos '--- Position of a square bracket within a string.
dim strColor '--- Used to store the color of the report rows.
Dim cmdParms '--- ADO Command Object
Dim nRptOption '--- Report Option
Dim bQueryFailed '--- Flag to indicate insufficient parameters for stored procedure
Dim dYear '--- Report Year

nRptOption = CInt(Request("rptoption"))
session("dYear") = Request("xYear")

Set objRS = Server.CreateObject("ADODB.Recordset")

objRS.CursorLocation = adUseClient
'--- Use a static cursor so we can navigate freely.
objRS.CursorType = adOpenForwardOnly

Set cmdParms = Server.CreateObject("ADODB.COMMAND")
Set cmdParms.ActiveConnection = objConn

cmdParms.CommandType = adCmdStoredProc

bQueryFailed = false

cmdParms.Parameters.Append cmdParms.CreateParameter("@Year", adDate, adParamInput, , session("dYear"))

Select Case nRptOption
Case 1 ' Spend per month, per type
response.Redirect("DomainReportsSpend.asp")
'cmdParms.CommandText = ""
Case 2 ' Quarterly Report by Type
cmdParms.CommandText = "usp_QtrlyRptByType"
Case Else
bQueryFailed = true
End Select

If Not bQueryFailed Then
Set objRS = cmdParms.Execute
End If

dim rstDetails
dim fieldDetails
Set rstDetails = Server.CreateObject("ADODB.Recordset")
rstDetails.Open "exec dbo.usp_QtrlyRptByType @Year='" & session("dYear") & "'", objConn
'set rstDetails = cmdParms.Execute

'intCount = 1
'Do Until rstDetails Is Nothing
'response.write "Contents of recordset #" & intCount & " "
'Do While Not rstDetails.EOF
'response.write rstDetails.Fields(0)& ", " & rstDetails.Fields(1) & " "
'rstDetails.MoveNext
'Loop
'Set rstDetails = rstDetails.NextRecordset
'intCount = intCount + 1
'Loop
%>
<html>
<head>
<link rel="stylesheet" href="../../sitestyle.css" type="text/css">
</head>
<body>
<table border="0" width="100%">
<tr>
<td colspan="14">
<%
Response.Write "<h4>Quarterly Report by Type - Report Year: " & right(session("dYear"), 4) & "</h4>" & vbCrLf
%>
</td>
</tr>
</table>
<table border="1" width="100%">
<tr>
<td width="35%" align="left">Domain Work</td>
<td width="5%" align="left">Jan</td>
<td width="5%" align="left">Feb</td>
<td width="5%" align="left">Mar</td>
<td width="5%" align="left">Apr</td>
<td width="5%" align="left">May</td>
<td width="5%" align="left">Jun</td>
<td width="5%" align="left">Jul</td>
<td width="5%" align="left">Aug</td>
<td width="5%" align="left">Sep</td>
<td width="5%" align="left">Oct</td>
<td width="5%" align="left">Nov</td>
<td width="5%" align="left">Dec</td>
<td width="5%" align="left">Total</td>
</tr>
<%
do while not rstDetails.EOF
response.write "<tr>"
for each fieldDetails in rstDetails.Fields
Response.Write "<td align='left'>" & fieldDetails.Value & "</td>" & vbCrLf
next
rstDetails.MoveNext
loop
%>
</tr>
<%
do while not rstDetails.EOF
Response.Write "<tr>"
for each fieldDetails in rstDetails.Fields
Response.Write "<td align='left'>" & fieldDetails.Value & "</td>" & vbCrLf
next
rstDetails.MoveNext
loop
%>
</tr>
<%
do while not rstDetails.EOF
Response.Write "<tr>"
for each fieldDetails in rstDetails.Fields
Response.Write "<td align='left'>" & fieldDetails.Value & "</td>" & vbCrLf
next
rstDetails.MoveNext
loop
%>
</tr>
<%
do while not rstDetails.EOF
Response.Write "<tr>"
for each fieldDetails in rstDetails.Fields
Response.Write "<td align='left'>" & fieldDetails.Value & "</td>" & vbCrLf
next
rstDetails.MoveNext
loop
%>
</tr>
<%
do while not rstDetails.EOF
Response.Write "<tr>"
for each fieldDetails in rstDetails.Fields
Response.Write "<td align='left'>" & fieldDetails.Value & "</td>" & vbCrLf
next
rstDetails.MoveNext
loop
%>
</tr>
<%
do while not rstDetails.EOF
Response.Write "<tr>"
for each fieldDetails in rstDetails.Fields
Response.Write "<td align='left'>" & fieldDetails.Value & "</td>" & vbCrLf
next
rstDetails.MoveNext
loop
%>
</tr>
<%
do while not rstDetails.EOF
Response.Write "<tr>"
for each fieldDetails in rstDetails.Fields
Response.Write "<td align='left'>" & fieldDetails.Value & "</td>" & vbCrLf
next
rstDetails.MoveNext
loop
%>
</tr>
<%
do while not rstDetails.EOF
Response.Write "<tr>"
for each fieldDetails in rstDetails.Fields
Response.Write "<td align='left'>" & fieldDetails.Value & "</td>" & vbCrLf
next
rstDetails.MoveNext
loop
%>
</tr>
<%
do while not rstDetails.EOF
Response.Write "<tr>"
for each fieldDetails in rstDetails.Fields
Response.Write "<td align='left'>" & fieldDetails.Value & "</td>" & vbCrLf
next
rstDetails.MoveNext
loop
%>
</tr>
<% Set rstDetails = rstDetails.NextRecordset() %>
<tr>
<td width="35%" align="left">Monthly Total</td>
<td width="5%" align="left"><%=rstDetails("JanTotal")%></td>
<td width="5%" align="left"><%=rstDetails("FebTotal")%></td>
<td width="5%" align="left"><%=rstDetails("MarTotal")%></td>
<td width="5%" align="left"><%=rstDetails("AprTotal")%></td>
<td width="5%" align="left"><%=rstDetails("MayTotal")%></td>
<td width="5%" align="left"><%=rstDetails("JunTotal")%></td>
<td width="5%" align="left"><%=rstDetails("JulTotal")%></td>
<td width="5%" align="left"><%=rstDetails("AugTotal")%></td>
<td width="5%" align="left"><%=rstDetails("SepTotal")%></td>
<td width="5%" align="left"><%=rstDetails("OctTotal")%></td>
<td width="5%" align="left"><%=rstDetails("NovTotal")%></td>
<td width="5%" align="left"><%=rstDetails("DecTotal")%></td>
<td width="5%" align="left"><%=rstDetails("YearlyTotal")%></td>
</tr>
<tr><td width="35%"> </td>
</tr>
<% Set rstDetails = rstDetails.NextRecordset() %>
<tr>
<td colspan="3"> </td>
<td width="5%" align="left">Q1</td>
<td colspan="2"> </td>
<td width="5%" align="left">Q2</td>
<td colspan="2"> </td>
<td width="5%" align="left">Q3</td>
<td colspan="2"> </td>
<td width="5%" align="left">Q4</td>
<td width="5%"> </td>
</tr>
<tr>
<td width="35%" align="left">Digital Certificates</td>
<td colspan="2"> </td>
<td width="5%" align='left'><%=rstDetails("Q1DC")%></td>
<td colspan="2"> </td>
<td width="5%" align='left'><%=rstDetails("Q2DC")%></td>
<td colspan="2"> </td>
<td width="5%" align='left'><%=rstDetails("Q3DC")%></td>
<td colspan="2"> </td>
<td width="5%" align='left'><%=rstDetails("Q4DC")%></td>
<td width="5%"> </td>
</tr>
<tr>
<td width="35%" align="left">Domain Name Deletions</td>
<td colspan="2"> </td>
<td width="5%" align='left'><%=rstDetails("Q1DD")%></td>
<td colspan="2"> </td>
<td width="5%" align='left'><%=rstDetails("Q2DD")%></td>
<td colspan="2"> </td>
<td width="5%" align='left'><%=rstDetails("Q3DD")%></td>
<td colspan="2"> </td>
<td width="5%" align='left'><%=rstDetails("Q4DD")%></td>
<td width="5%"> </td>
</tr>
<tr>
<td width="35%" align="left">Domain Name Registration</td>
<td colspan="2"> </td>
<td width="5%" align='left'><%=rstDetails("Q1DR")%></td>
<td colspan="2"> </td>
<td width="5%" align='left'><%=rstDetails("Q2DR")%></td>
<td colspan="2"> </td>
<td width="5%" align='left'><%=rstDetails("Q3DR")%></td>
<td colspan="2"> </td>
<td width="5%" align='left'><%=rstDetails("Q4DR")%></td>
<td width="5%"> </td>
</tr>
<tr>
<td width="35%" align="left">Domain Name Renewals</td>
<td colspan="2"> </td>
<td width="5%" align='left'><%=rstDetails("Q1DRe")%></td>
<td colspan="2"> </td>
<td width="5%" align='left'><%=rstDetails("Q2DRe")%></td>
<td colspan="2"> </td>
<td width="5%" align='left'><%=rstDetails("Q3DRe")%></td>
<td colspan="2"> </td>
<td width="5%" align='left'><%=rstDetails("Q4DRe")%></td>
<td width="5%"> </td>
</tr>
<tr>
<td width="35%" align="left">Domain Name Server Transfers</td>
<td colspan="2"> </td>
<td width="5%" align='left'><%=rstDetails("Q1DNS")%></td>
<td colspan="2"> </td>
<td width="5%" align='left'><%=rstDetails("Q2DNS")%></td>
<td colspan="2"> </td>
<td width="5%" align='left'><%=rstDetails("Q3DNS")%></td>
<td colspan="2"> </td>
<td width="5%" align='left'><%=rstDetails("Q4DNS")%></td>
<td width="5%"> </td>
</tr>
<tr>
<td width="35%" align="left">Domain Name Transfers</td>
<td colspan="2"> </td>
<td width="5%" align='left'><%=rstDetails("Q1DT")%></td>
<td colspan="2"> </td>
<td width="5%" align='left'><%=rstDetails("Q2DT")%></td>
<td colspan="2"> </td>
<td width="5%" align='left'><%=rstDetails("Q3DT")%></td>
<td colspan="2"> </td>
<td width="5%" align='left'><%=rstDetails("Q4DT")%></td>
<td width="5%"> </td>
</tr>
<tr>
<td width="35%" align="left">Mail Exchange Setup</td>
<td colspan="2"> </td>
<td width="5%" align='left'><%=rstDetails("Q1ME")%></td>
<td colspan="2"> </td>
<td width="5%" align='left'><%=rstDetails("Q2ME")%></td>
<td colspan="2"> </td>
<td width="5%" align='left'><%=rstDetails("Q3ME")%></td>
<td colspan="2"> </td>
<td width="5%" align='left'><%=rstDetails("Q4ME")%></td>
<td width="5%"> </td>
</tr>
<tr>
<td width="35%" align="left">Redirect IP Address</td>
<td colspan="2"> </td>
<td width="5%" align='left'><%=rstDetails("Q1IP")%></td>
<td colspan="2"> </td>
<td width="5%" align='left'><%=rstDetails("Q2IP")%></td>
<td colspan="2"> </td>
<td width="5%" align='left'><%=rstDetails("Q3IP")%></td>
<td colspan="2"> </td>
<td width="5%" align='left'><%=rstDetails("Q4IP")%></td>
<td width="5%"> </td>
</tr>
<tr>
<td width="35%" align="left">Research Ownership or Validity of Domain Name</td>
<td colspan="2"> </td>
<td width="5%" align='left'><%=rstDetails("Q1RO")%></td>
<td colspan="2"> </td>
<td width="5%" align='left'><%=rstDetails("Q2RO")%></td>
<td colspan="2"> </td>
<td width="5%" align='left'><%=rstDetails("Q3RO")%></td>
<td colspan="2"> </td>
<td width="5%" align='left'><%=rstDetails("Q4RO")%></td>
<td width="5%"> </td>
</tr>
<% Set rstDetails = rstDetails.NextRecordset() %>
<tr>
<td width="35%" align="left">Quarterly Total</td>
<td colspan="2"> </td>
<td width="5%" align='left'><%=rstDetails("Q1Total")%></td>
<td colspan="2"> </td>
<td width="5%" align='left'><%=rstDetails("Q2Total")%></td>
<td colspan="2"> </td>
<td width="5%" align='left'><%=rstDetails("Q3Total")%></td>
<td colspan="2"> </td>
<td width="5%" align='left'><%=rstDetails("Q4Total")%></td>
<td width="5%"> </td>
</tr>
</table>
 
<%
objRS.Close
set objRS = nothing
rstDetails.Close
set rstDetails = nothing
set cmdParms = nothing
%>

</body>
</html>

Oh, and by the way, my SQL version is 2000, running sp3a, I believe. IIS 6. I hope someone can help. Thank you in advance for having a look!

Lisa Kirkeby
BP
kirkeblm@bp.com
SQL 7.0 SP3, NT 4.0 (1381), CP 1252

Kristen
Test

22859 Posts

Posted - 2005-03-22 : 16:47:49

After each

rstDetails.MoveNext

don't you need a

rstDetails.NextRecordset()

??

Kristen

kirkeby
Yak Posting Veteran

57 Posts

Posted - 2005-03-23 : 09:33:47

No, because in that section I'm looping through that specific set of records. I don't need to move to the next recordset until after that. It gives me the first 2 recordsets just fine. Where it fails is at this line of the ASP page:

<td width="5%" align='left'><%=rstDetails("Q1DC")%></td>

Right before it gets to the above line, I have moved to the next recordset, but it keeps telling me that it can't find "Q1DC". It either thinks there are no more recordsets, or that "Q1DC" isn't in the recordset it's looking at.

If you activate this portion of code, you can see what's being returned. It doesn't look right to me as there should be data for some of the fields being returned, and 0's for the fields with no data.

'intCount = 1
'Do Until rstDetails Is Nothing
'response.write "Contents of recordset #" & intCount & " "
'Do While Not rstDetails.EOF
'response.write rstDetails.Fields(0)& ", " & rstDetails.Fields(1) & " "
'rstDetails.MoveNext
'Loop
'Set rstDetails = rstDetails.NextRecordset
'intCount = intCount + 1
'Loop

Thanks for any info you can provide.

Lisa Kirkeby
BP
kirkeblm@bp.com
SQL 7.0 SP3, NT 4.0 (1381), CP 1252

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-03-23 : 10:16:52

quote:

do while not rstDetails.EOF
response.write "<tr>"
for each fieldDetails in rstDetails.Fields
Response.Write "<td align='left'>" & fieldDetails.Value & "</td>" & vbCrLf
next
rstDetails.MoveNext
loop
%>
</tr>
<%
do while not rstDetails.EOF
Response.Write "<tr>"
for each fieldDetails in rstDetails.Fields
Response.Write "<td align='left'>" & fieldDetails.Value & "</td>" & vbCrLf
next
rstDetails.MoveNext
loop
%>
</tr>
<%
do while not rstDetails.EOF
Response.Write "<tr>"
for each fieldDetails in rstDetails.Fields
Response.Write "<td align='left'>" & fieldDetails.Value & "</td>" & vbCrLf
next
rstDetails.MoveNext
loop
%>
</tr>
<%
do while not rstDetails.EOF
Response.Write "<tr>"
for each fieldDetails in rstDetails.Fields
Response.Write "<td align='left'>" & fieldDetails.Value & "</td>" & vbCrLf
next
rstDetails.MoveNext
loop
%>
</tr>
<%
do while not rstDetails.EOF
Response.Write "<tr>"
for each fieldDetails in rstDetails.Fields
Response.Write "<td align='left'>" & fieldDetails.Value & "</td>" & vbCrLf
next
rstDetails.MoveNext
loop
%>
</tr>
<%
do while not rstDetails.EOF
Response.Write "<tr>"
for each fieldDetails in rstDetails.Fields
Response.Write "<td align='left'>" & fieldDetails.Value & "</td>" & vbCrLf
next
rstDetails.MoveNext
loop
%>
</tr>
<%
do while not rstDetails.EOF
Response.Write "<tr>"
for each fieldDetails in rstDetails.Fields
Response.Write "<td align='left'>" & fieldDetails.Value & "</td>" & vbCrLf
next
rstDetails.MoveNext
loop
%>
</tr>
<%
do while not rstDetails.EOF
Response.Write "<tr>"
for each fieldDetails in rstDetails.Fields
Response.Write "<td align='left'>" & fieldDetails.Value & "</td>" & vbCrLf
next
rstDetails.MoveNext
loop
%>
</tr>
<%
do while not rstDetails.EOF
Response.Write "<tr>"
for each fieldDetails in rstDetails.Fields
Response.Write "<td align='left'>" & fieldDetails.Value & "</td>" & vbCrLf
next
rstDetails.MoveNext
loop
%>

?? What the heck is going on here? you loop through the recordset about 8 different times. But at no point do you go back to the beginning of it, so only the first loop executes. The rest are probably all skipped because you are at EOF so they never get started. I can't figure out what you are trying to accomplish with this code.

as a side note, lose the DISTINCT in your SQL statements. Never use DISTINCT when you are trying to do calculations and counts, it is a very bad habbit to just to add the word DISTINCT into each SELECT query that you write.

- Jeff

Kristen
Test

22859 Posts

Posted - 2005-03-23 : 10:40:48

"you loop through the recordset about 8 different times"

That's why I thought rstDetails.NextRecordset() was neede - 'coz there look to be at least 4 separate SELECT statements.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources