Stored procedure won't return to recordset

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Stored procedure won't return to recordset

Author

Topic

Codesensitive
Starting Member

11 Posts

Posted - 2002-03-22 : 03:54:50

G'day,
I recently created a stored procedure to fetch all products in a database for a given category and all it's sub-categories. Everything works fine when I run the procedure in sql query analyzer, however, when I execute it from asp page it won't return any data to the recordset. I'm pretty sure there's nothing wrong with my asp code since the rest of my stored procedures works just fine.

Here's the code for the procedure. First I create a temp table, then I add the category id, execute a procedure to fetch all sub-categories and place them in the temp table, make the select statement that I want returned, and finally droping the temp table.
----------------------------------
CREATE PROCEDURE proc_get_products
@CategoryID int
AS
set nocount on
create table #nested_categories (category_id int)
insert into #nested_categories (category_id) values (@CategoryID)
exec proc_get_all_subcategories @CategoryID
set nocount off

select products_101.n_id_101, products_101.n_id_102_101, products_101.s_code_name_101, products_101.s_name_101, products_101.n_price_101, products_101.s_picture_101
from products_101 inner join #nested_categories
on products_101.n_id_102_101 = #nested_categories.category_id

set nocount on
drop table #nested_categories
set nocount off
GO
----------------------------------

As I said, it works perfect in sql query analyzer, but I get nothing in my recordset. Can someone please help.
Thanks alot.

Peter Dutch
Posting Yak Master

127 Posts

Posted - 2002-03-22 : 04:01:01

You mixed up the SET NOCOUNT ON/OFF options and the recordset ADO recieves is empty/closed.

Just use SET NOCOUNT ON in the beginning of the SProc and forget about the DROP #table command, since it's a temp table it's going to be dropped automatically.

If for some reason this doesn't work try to use ADO's .NextRecordSet method.

Nazim
A custom title

1408 Posts

Posted - 2002-03-22 : 04:57:50

Peter is right when he says you dont have to play with Set Nocount on/off in your Sp . A starting statement settign in on will do the work.

But, its always a good idea dropping the temp table.

Apart from that,I dont find any thing wrong with your Sp . why dont you post your Asp code too.

--------------------------------------------------------------

Codesensitive
Starting Member

11 Posts

Posted - 2002-03-22 : 05:29:34

Alright, I tried to fix the nocount statements and I removed the drop table at the end but it still doesn't work. Here's the new code, I'll paste the second stored procedure that fetches the sub-categories and the asp code ass well.
---------------------------------------
CREATE PROCEDURE proc_get_products
@CategoryID int
AS
set nocount on
create table #nested_categories (category_id int)
insert into #nested_categories (category_id) values (@CategoryID)
exec proc_get_all_subcategories @CategoryID

select products_101.n_id_101, products_101.n_id_102_101, products_101.s_code_name_101, products_101.s_name_101, products_101.n_price_101, products_101.s_picture_101
from products_101 inner join #nested_categories
on products_101.n_id_102_101 = #nested_categories.category_id
GO

---------------------------------------
CREATE PROCEDURE proc_get_all_subcategories
@ParentID int
AS
set nocount on
declare c1 cursor local for
select n_id_102 from product_categories_102 where n_id_102_102 = @ParentID
open c1
fetch next from c1 into @ParentID

while @@FETCH_STATUS = 0
begin
insert into #nested_categories (category_id) values (@ParentID)
exec proc_get_all_subcategories @ParentID
fetch next from c1 into @ParentID
end

close c1
deallocate c1
GO

---------------------------------------
Set oConnection = Server.CreateObject("ADODB.Connection")
oConnection.Open Session("ConndB")
Set oRecordset = Server.CreateObject("ADODB.RecordSet")
oRecordset.Open "proc_get_products 1", oConnection, 3, 1

' Do stuff here.

oRecordset.Close
Set oRecordset = Nothing
oConnection.Close
Set oConnection = Nothing

---------------------------------------

I haven't tried the NextRecordset method yet, since I use a function for data access and I don't know what it will do to the rest of my queries. Can someone explain what it does and how it works? Or is there something wrong with my stored procedures so that I can use the asp code as it is now?
Thanks.

Codesensitive
Starting Member

11 Posts

Posted - 2002-03-22 : 08:48:10

I found the problem. For some reason the Recordset.RecordCount method returns -1 for when I execute this stored procedures. Queer since I use a static cursor on the recordset and other stored procedures doesn't cause this problem.

Anyway, thanks for your help.

jackstow
Posting Yak Master

160 Posts

Posted - 2002-03-22 : 09:07:25

Recordset.RecordCount always returns -1 when you use a stored procedure for data access - you can't set the curser type either - just one of those things...

uberbloke
Yak Posting Veteran

67 Posts

Posted - 2002-03-22 : 12:03:23

WHAT!

I can get a record count from a stored proc...

DB Stuff
create table tbltest(
test varchar(20)
)

insert into tbltest
select 'one'

insert into tbltest
select 'two'

insert into tbltest
select 'three'

create procedure testrows
as
set nocount on
select * from tbltest
set nocount off

ASP stuff
<%
dim objCommand, objConnection, objRS
set objCommand = server.createObject("adodb.command")
with objCommand
.commandType = adCmdStoredProc
.commandText = "testrows"
set objConnection = server.createObject("adodb.connection")
objConnection.open application("db") 'YOUR CONNECTION STRING HERE
.activeConnection = objConnection
end with

set objRS = server.createObject("adodb.recordset")
with objRS
.cursorlocation = adUseClient
.cursorType = adOpenStatic
.lockType = readOnly
.open objCommand
end with

response.write objRS.recordcount
%>

Should give you the result 3.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-22 : 12:05:47

You also only have one operation in your stored procedure (SELECT). CodeSensitive has several operations (SELECT, INSERT, FETCH eeeeeeewwwwwwww) and I'm not at all surprised that ADO can't return a row count.

jackstow
Posting Yak Master

160 Posts

Posted - 2002-03-24 : 03:05:16

uberbloke - well you've stumped me there mate! Your code does work, but you'll find if you change this line;

.open objCommand

to

.open objCommand.Execute

..which is the way I've always done it, you can't set the cursor type or location and hence you always get -1.

uberbloke
Yak Posting Veteran

67 Posts

Posted - 2002-03-25 : 04:00:43

That's a variation on a problem I have advised on in the past.

Using the .execute method of EITHER the command or the connection object causes a DEFAULT recordset to be created! So regardless of all the work you do setting up the connection types etc etc of your recordset .... as soon as you use .execute you get a firehose(!) recordset (readonly, forwardonly (adLockReadOnly, adOpenForwardOnly))

So if you want a default recordset use .execute, if you want to specify the properties of your recordset the ONLY use the .open method of the recordset.

Personally, I NEVER use the default properties of the recordset, I always set it up as per the code given, this way when I look through my ASP I can see exactly what that RS is supposed to do.

I also ONLY use the .execute method of the command object when I am not expecting a recordset back (say on a parameterised insert/update/delete) and then I use

objCommand.execute ,, adExecuteNoRecords

as I can explicitly tell the command object that I am not expecting a recordset back and reduce some overhead.

Hope this helps...

Happy coding

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources