SP + asp

[vlitim]

Iam using a stored procedure to return results to an asp page (code below) but I ma getting an error on the asp page:

Operation is not allowed when the object is closed.

for the line: If Not rs.BOF Then

Now this sounds like nothing is getting returned from the SP or something, but when you run the query in sql query analyzer about 40 records are returned!

Has anyone got any ideas, as I have run out of ideas!!

ASP:

SQL="sp_REPORTS_Sales_breakdown "&distiID&",'"&session("region&quot&"_"&session("inputMask&quot&"_LICENSING_REQ','"&session("region&quot&"_"&session("inputMask&quot&"_BP'"
Set rs=posconn.Execute(SQL)
If Not rs.BOF Then
Do While Not rs.EOF
response.write(&quot;<tr><td>&quot;&rs(&quot;partnername&quot&&quot;</td>&quot
response.write(&quot;<td align=right>&quot;&formatnumber(rs(&quot;expr1&quot,2,0,0)&&quot;</td></tr>&quot
total=total+CCur(rs(&quot;expr1&quot)
rs.movenext
Loop
End If
rs.close

SP:

CREATE PROCEDURE sp_REPORTS_Sales_breakdown
(
@distiID varchar(100),
@licensing varchar(100),
@BP varchar(100)
)
AS

DECLARE @SQL varchar(2000)
DECLARE @SQLlicensing varchar(500)
DECLARE @SQLBP varchar(500)

CREATE TABLE #tempItems
(
partnerID int,
partnerName varchar(255),
)

CREATE TABLE #tempItemsTotal
(
partnerID int,
totalPrice decimal(18,2)
)

CREATE TABLE #tempItemsPartner
(
partnerID int,
partnerName varchar(255),
)


SET @SqlLicensing = 'INSERT into #tempItems (partnerId,partnerName) SELECT TOP 100 PERCENT CA_GLOBAL_PARTNERS.dbo.Partners.ID, CA_GLOBAL_PARTNERS.dbo.Partners.Name FROM dbo.'+@licensing+' INNER JOIN CA_GLOBAL_PARTNERS.dbo.Partners ON dbo.'+@licensing+'.distiID = CA_GLOBAL_PARTNERS.dbo.Partners.ID WHERE (dbo.'+@licensing+'.partnerID = '+@distiID+') GROUP BY CA_GLOBAL_PARTNERS.dbo.Partners.ID, CA_GLOBAL_PARTNERS.dbo.Partners.Name'
exec(@SqlLicensing)

SET @SqlBP = 'INSERT into #tempItems (partnerId,partnerName) SELECT TOP 100 PERCENT CA_GLOBAL_PARTNERS.dbo.Partners.ID, CA_GLOBAL_PARTNERS.dbo.Partners.Name FROM dbo.'+@BP+' INNER JOIN CA_GLOBAL_PARTNERS.dbo.Partners ON dbo.'+@BP+'.distiID = CA_GLOBAL_PARTNERS.dbo.Partners.ID WHERE (dbo.'+@BP+'.partnerID = '+@distiID+') GROUP BY CA_GLOBAL_PARTNERS.dbo.Partners.ID, CA_GLOBAL_PARTNERS.dbo.Partners.Name'
exec(@SqlBP)

SET @SqlLicensing = 'INSERT into #tempItemsTotal (partnerId,totalprice) SELECT TOP 100 PERCENT CA_GLOBAL_PARTNERS.dbo.Partners.ID, SUM(dbo.'+@licensing+'.TotalPrice) AS Expr1 FROM dbo.'+@licensing+' INNER JOIN CA_GLOBAL_PARTNERS.dbo.Partners ON dbo.'+@licensing+'.distiID = CA_GLOBAL_PARTNERS.dbo.Partners.ID WHERE (dbo.'+@licensing+'.partnerID = '+@distiID+' ) GROUP BY CA_GLOBAL_PARTNERS.dbo.Partners.ID, CA_GLOBAL_PARTNERS.dbo.Partners.Name'
exec(@SqlLicensing)

SET @SqlBP = 'INSERT into #tempItemsTotal (partnerId,totalprice) SELECT TOP 100 PERCENT CA_GLOBAL_PARTNERS.dbo.Partners.ID, SUM(dbo.'+@BP+'.TotalPrice) AS Expr1 FROM dbo.'+@BP+' INNER JOIN CA_GLOBAL_PARTNERS.dbo.Partners ON dbo.'+@BP+'.distiID = CA_GLOBAL_PARTNERS.dbo.Partners.ID WHERE (dbo.'+@BP+'.partnerID = '+@distiID+') GROUP BY CA_GLOBAL_PARTNERS.dbo.Partners.ID, CA_GLOBAL_PARTNERS.dbo.Partners.Name'
exec(@SqlBP)

INSERT into #tempItemsPartner (partnerId,partnerName) SELECT DISTINCT partnerID,partnerName FROM #tempItems

SELECT #tempItemsPartner.partnerID, #tempItemsPartner.partnerName, SUM(#tempItemstotal.totalPrice) as expr1 FROM #tempItemsPartner INNER JOIN #tempitemsTotal on #tempItemsPartner.partnerID=#tempItemsTotal.partnerID GROUP by #tempItemsPartner.partnerID, #tempItemsPartner.partnerName
GO



Cheers

Tim

 

[mwa]

sounds to me like it is in your asp...

try changing the line that says:
If Not rs.BOF Then

to:
If Not rs.EOF Then

or try commenting out that line (and the end if) and see what happens...

mwa

 

[vlitim]

Tried that, if I get rid of :

If Not rs.BOF Then
Do While Not rs.EOF

then I get the error:

Item cannot be found in the collection corresponding to the requested name or ordinal.

for
response.write(&quot;<tr><td>&quot;&rs(&quot;partnername&quot&&quot;</td>&quot

???

Tim

 

[vlitim]

another thing I have just noticed is that in sql query analyzer it prints out this in the messages:

(32 row(s) affected)


(21 row(s) affected)


(32 row(s) affected)

Warning: Null value is eliminated by an aggregate or other SET operation.

(21 row(s) affected)

Warning: Null value is eliminated by an aggregate or other SET operation.

(44 row(s) affected)


(44 row(s) affected)

Warning: Null value is eliminated by an aggregate or other SET operation.


could this warning have anything to do with it??

 

[vlitim]

ok have sorted it now, you have to set NOCOUNT ON on the stored procedure