Returning Record Set 2

[TMRO]

Hi guys,

I'm calling in my asp page a stored procedure with 2 parameters. Here is the code

set cn = Server.CreateObject("ADODB.Connection")
cn.CursorLocation = adUseClient
cn.Open "Provider=SQLOLEDB;Server=servername;Database=DB;UID=user;PWD=password"
set rsresults = Server.CreateObject("ADODB.RecordSet")
set cmd = Server.CreateObject("ADODB.Command")
set cmd.ActiveConnection = cn
cmd.CommandText = "sp_GetSearchCriteria"
cmd.CommandType = 4
cmd.Parameters.Append cmd.CreateParameter("@UserID",adChar,1,10,Request("UserID"))
cmd.Parameters.Append cmd.CreateParameter("@StartUp",adChar,1,10,Request("StartUp"))
set rsresults = cmd.Execute

The stored procedure (sp_GetSearchCriteria) is calling another stored procedure which returns a recordset.

Now, if I want to work with the recordset the error that I'm getting is

ADODB.Recordset error '800a0e78'
Operation is not allowed when the object is closed.

This is because the main stored proceudre is not returning directly a recordset?

If I run the first SP in Query Analyzer everything is fine.

Anybody some ideas?

Thank you

 

[gmmastros]

Modify your stored procedure with the code in [!]Red[/!]

Alter Procedure sp_GetSearchCriteria
  @Param1 ...,
  @Param2 ...
As
[!]SET NOCOUNT ON[/!]

   The rest of the stored procecure

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[DotNetGnat]

make sure that you have set Nocount ON and ANSI_warnings OFF

-DNG

 

[TMRO]

Thank you guys