Error with returning, or not, a recordset

Question

Error with returning, or not, a recordset

mikeperrett

Valued Member

Points: 57
More actions
February 24, 2003 at 3:40 pm

#159671

Hi,
I've got a usp that runs a select statement and based on the result of that it decides whether or not to execute another usp. If it does, it works fine, if it doesn't it falls over, how can I get this to not fall over if the usp isn't returning a recordset?
code:
(ASP page)-------------------------
Set rsObj = Server.CreateObject("ADODB.RECORDSET")
strSQL = "Exec dynamicsurveys.dynamics.USP_score_ids @get_val1 =" & dim1 & ", @get_val2 =" & dim2
rsObj.Open strSQL, DBConn, 3, 2, adCmdTable
....
If Not rsObj.Eof Then
....
(the error)------------------------
Error Type:
ADODB.Recordset (0x800A0E78)
Operation is not allowed when the object is closed.
/survey/enter_results.asp, line 49 (=if notrsObj.Eof Then...
(usp)------------------------------
CREATE PROCEDURE dbo.USP_score_ids
(
@get_val1 int,
@get_val2 int
)
AS
IF
(
(
SELECT
dbo.PF_THE_SCORE.COMPLETED
FROM
dbo.PF_THE_SCORE
WHERE
dbo.PF_THE_SCORE.ALLO_ID = @get_val1
AND
dbo.PF_THE_SCORE.ALLO_ID = @get_val2
)
<> 1
)
BEGIN
EXEC dbo.USP_get_score_ids @get_val1
END
ELSE
BEGIN
--if I have nothing here it falls over
--if I execute the same sp but with 0 as the value (I know it'll return nothing) it works.
--I want to avoid making it execute the following usp this time though
EXEC dbo.USP_get_score_ids 0
END
GO
I hope that makes sense. Running on MS SQL Win2000 from ASP page.
Thanks in advance,
Mike

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

gregj Old Hand Points: 346 More actions · Answer 1

How about trying "select null" instead of calling the extra stored procedure? If you need a specific column name, you can do something like "select null as columnname".

tcartwright SSCertifiable Points: 5573 More actions · Answer 2

Hmmmm, when you execut the stored proc in QA does it print out any "(Nrow(s) affected" statements before the actual resultset comes back in text mode? I have seen where these return row statements get interpreted as recordsets, and closed ones at that. Try adding "SET NOCOUNT ON" at the beggining of the stored proc and "SET NOCOUNT OFF" at the end. Let me know how it works out.

Tim C.

//Will write code for food

Tim C //Will code for food

tcartwright SSCertifiable Points: 5573 More actions · Answer 3

As a general rule I almost add those two statementy to my stored procedures. If I actually want to know the records affected I use @@ROWCOUNT to return that information in an ouput variable or the return code.

Tim C.

//Will write code for food

Tim C //Will code for food

mikeperrett Valued Member Points: 57 More actions · Answer 4

Thanks guys, I'll try those suggestions later and let you know how it goes. My 'work' work has been keeping me busy (the nerve of them!) so I've not had a chance to look into this since posting the initial question, but thanks for the advice, I'll post an update next week.

Thanks,

Mike