asp page gets an empty recordset from a sql server store procedure

[ipez75]

Hello everyone,
I have a web application written in asp 6.0, my problem is that I execute a sql server store procedure and I get an empty recordset, while executing the same sp on query anlyzer I can see 5 records.
Basically the sp create a temp table, populate it, and lastly selects the results from the temp table.
I read on a preceding post to insert the clause: "SET NOCOUNT ON" to avoid getting closed recordset of the insert statements, so I did it.
The query analyzer results changed, (I didn't see the number of rows created any more) But it didn't change the result on the asp page: I still get an empty recordset.
the snippet asp code is the following:

   dim cmd 
   dim rs
 
   set cmd = server.createobject("adodb.command")
   set rs = server.CreateObject("adodb.recordset")
 
   cmd.commandtext = "mywonderfulstoreprocedure"
   cmd.commandtype = adcmdstoredproc
   cmd.activeconnection = cnapp 'assume that's a succesful connection
 
   cmd.Parameters("@month") = 3
   cmd.Parameters("@year") = 2007
 
   rs.open cmd
   if rs.EOF then
 
        Response.Write "recordset empty"
   else
        Response.Write "recordset full of stuff"
   end if
 

Eventually it's important to point out that the same code arranged for visual basic 6.0 works perfectly, giving back the recordset full of data

Thank you for your help in advance,
Gianni

[ipez75]

I forgot to say that we use windows 2000 Service Pack 4.
Any help would be very appreciated!

[jhardman]

Hello everyone,
I have a web application written in asp 6.0,

There is no ASP 6.0. It only got up to 3.0 before M$ abandoned it.

Do you have some sort of error handler running?

Jared

[ipez75]

Hi,
thank you for your help.
You are absolutely right, it's asp 3.0
As for error handler I test with
if err.number <> 0 then ...
but this test is not passed.

The test passed is rs.EOF, that comes true (I don't know why)

Thank you again.
Gianni

[jhardman]

Gianni,

I personally don't like to use stored procedures, I prefer to do the programming in the script and just send very basic queries to the db, but I'll try to help.

if you take out the 'on error' line what error do you get?

Jared