Does a Stored Procedure return a recordset?

[handle87]

Hi,
I would like to run a stored procedure that returns a recordset object....

rs.Open "Exec _sp_AMS_Monthly_Internal_CSR " & CDbl(request.form("exchangeRate")) & ",'" & CDate(fromdate) & "','" & CDate(todate) & "'",dbConn

If I run the command

EXEC _sp_Update_Reporting_Period '05/05/05', '05/09/05'

on the server, it runturns what I want but if I call it as above in ASP I get an error when I try to loop the recordset:

Operation is not allowed when the object is closed..

anyone know the answer to this? any suggestion what I might want to try if I cannot do it this way.

 

[Chopstik]

Can you show the whole loop? The problem may be that you have closed the recordset object somewhere and then are trying to open it again later. Additional code may help to show where the problem is.

------------------------------------------------------------------------------------------------------------------------
"I am not young enough to know everything."
Oscar Wilde (1854-1900)

 

[handle87]

I still get the same error if I do .MoveFirst() and comment out the whole loop... its like its not returning a recordset... just runs and returns nothing.. any ideas if this is whats happening?

 

[Chopstik]

Can you show the relevant code?

------------------------------------------------------------------------------------------------------------------------
"I am not young enough to know everything."
Oscar Wilde (1854-1900)

 

[handle87]

I have tried it a little different that the above but am getting the same thing... here it is..

		'Create Command object to execute the SP
		Set cmdStoredProc = Server.CreateObject("ADODB.Command")

		'Set our Command to use our existing connection
		cmdStoredProc.ActiveConnection = dbConn

		'Set the SP-s name and tell the Command object
		cmdStoredProc.CommandText = "_sp_AMS_Monthly_Internal_CSR"
		cmdStoredProc.CommandType = 4

		Set paramId1 = cmdStoredProc.CreateParameter("@exchangeRate", 5, 1)
		Set paramId2 = cmdStoredProc.CreateParameter("@startDate", 7, 1)
		Set paramId3 = cmdStoredProc.CreateParameter("@endDate", 7, 1)

		paramId1.value = 1.22
		paramId2.value = CDate(fromdate)
		paramId3.value = CDate(todate)

		cmdStoredProc.Parameters.Append paramId1
		cmdStoredProc.Parameters.Append paramId2
		cmdStoredProc.Parameters.Append paramId3

		Set rs = cmdStoredProc.Execute
DO WHILE NOT rs.EOF
  response.write(rs("StaffName"))
LOOP

It errors on the line with the DO WHILE... however I do know that stored procedure is running ok because I can run it in Query Analyzer and when I run it from ASP the sp changes records in the database... its like its closing the recordset before I can even use it?

 

[handle87]

I found the answer in another post!!!


becasue there was an insert statement in one of the subsequent sp's it was returning '1 rows affected' etc...

I put

SET NOCOUNT ON

at the start of the sp and it supressed the affected row out and it works!!

Thanks for helpin!

 

[Chopstik]

Good catch and glad it worked out for you.

------------------------------------------------------------------------------------------------------------------------
"I am not young enough to know everything."
Oscar Wilde (1854-1900)