By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,846 Members | 2,094 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,846 IT Pros & Developers. It's quick & easy.

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

P: 3
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:
Expand|Select|Wrap|Line Numbers
  1.    dim cmd 
  2.    dim rs
  3.  
  4.    set cmd = server.createobject("adodb.command")
  5.    set rs = server.CreateObject("adodb.recordset")
  6.  
  7.    cmd.commandtext = "mywonderfulstoreprocedure"
  8.    cmd.commandtype = adcmdstoredproc
  9.    cmd.activeconnection = cnapp 'assume that's a succesful connection
  10.  
  11.    cmd.Parameters("@month") = 3
  12.    cmd.Parameters("@year") = 2007
  13.  
  14.    rs.open cmd
  15.    if rs.EOF then
  16.  
  17.         Response.Write "recordset empty"
  18.    else
  19.         Response.Write "recordset full of stuff"
  20.    end if
  21.  
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
Nov 14 '07 #1
Share this Question
Share on Google+
4 Replies


P: 3
I forgot to say that we use windows 2000 Service Pack 4.
Any help would be very appreciated!
Nov 15 '07 #2

jhardman
Expert 2.5K+
P: 3,405
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
Nov 17 '07 #3

P: 3
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
Nov 19 '07 #4

jhardman
Expert 2.5K+
P: 3,405
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
Nov 19 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.