$100 USD to the winner... Stored Procs, Temporary Tables and Cursors... oh my!

I'm so desperate, I'll pay $100 to the proper solution to this problem. I'm sure it's an easy fix, but I'm wasting more money every day trying to figure it out...

I have a table with hierarchial data in it (see the bottom tabledef) and I need to query an "indented outline" of the records in it for a tree control on a website. To do that I have to perform some sort of recursive or nested query or I can do all that manipulation in a temporary table/cursor... However, even though the resultset will display when I check the query, when I try to open it using ADO, I get a recordcount of -1.... it's very frustrating and extremely important.

I'd rather pay an expert here than try to navigate a tech help line.

ConnIS is defined in an earlier include file...

Set oCmd = Server.CreateObject("ADODB.Command")
Set oCmd.ActiveConnection = ConnIS
oCmd.CommandText = "dbo.Expandset" 'Name of SP
oCmd.CommandType = adCmdStoredProc 'ADO constant for 4
Set oTmp = oCmd.CreateParameter("@current", adInteger, adParamInput,, 892)
oCmd.Parameters.Append oTmp
Set oRs = Server.CreateObject("ADODB.Recordset")
oRs.Open oCmd
Response.Write oRs.RecordCount & &#34;<hr>&#34;
oRs.Close
Set oRs=Nothing


This code generates the following result when run from an active server page:

-1<hr>

When I execute the raw SQL code (&#34;exec Expandset 892&#34;) against the stored proc in the query analyzer, I get:

item tier
----------- -----------
892 1
948 2
895 2
946 2
945 2
893 2
894 3
944 2
943 2
904 2
896 3
897 4
901 2
903 3
902 3
900 2
947 2
899 2

The source for the stored proc is:
------------------------------------------------------
CREATE PROCEDURE Expandset (@current int) as
SET NOCOUNT ON
DECLARE @level int, @line char(20)
CREATE TABLE #stack (item int, tier int)
CREATE TABLE #output (item int, tier int)
INSERT INTO #stack VALUES (@current, 1)
SELECT @level = 1
WHILE @level > 0
BEGIN
IF EXISTS (SELECT * FROM #stack WHERE tier = @level)
BEGIN
SELECT @current = item
FROM #stack
WHERE tier = @level
SELECT @line = space(@level - 1) + convert(varchar,@current)
INSERT INTO #output (item, tier) values (@current, @level)
DELETE FROM #stack
WHERE tier = @level
AND item = @current
INSERT #stack
SELECT ID, @level + 1
FROM SITE_Container
WHERE parent = @current
IF @@ROWCOUNT > 0
SELECT @level = @level + 1
END
ELSE
SELECT @level = @level - 1
END
SELECT O.item, O.tier FROM #output O
-------------------------------------------------------------------

The relevant portions of the Table definitions for SITE_Container are:

CREATE TABLE [dbo].[SITE_Container] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Parent] [int] NULL)

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

My contact information is:

Jared Nielsen
PO Box 600271
Jacksonville, FL 32260
904-230-1688
888-316-2357 vm / fax

Jared,

Try changing the VB Script line :

oRs.Open oCmd

to

Set oRs = oCmd.Execute

Everything else seems to look OK to me.

John

------------
Jared Nielsen at 10/14/01 3:18:37 PM


I&#39;m so desperate, I&#39;ll pay $100 to the proper solution to this problem. I&#39;m sure it&#39;s an easy fix, but I&#39;m wasting more money every day trying to figure it out...

I have a table with hierarchial data in it (see the bottom tabledef) and I need to query an &#34;indented outline&#34; of the records in it for a tree control on a website. To do that I have to perform some sort of recursive or nested query or I can do all that manipulation in a temporary table/cursor... However, even though the resultset will display when I check the query, when I try to open it using ADO, I get a recordcount of -1.... it&#39;s very frustrating and extremely important.

I&#39;d rather pay an expert here than try to navigate a tech help line.

ConnIS is defined in an earlier include file...

Set oCmd = Server.CreateObject(&#34;ADODB.Command&#34;)
Set oCmd.ActiveConnection = ConnIS
oCmd.CommandText = &#34;dbo.Expandset&#34; &#39;Name of SP
oCmd.CommandType = adCmdStoredProc &#39;ADO constant for 4
Set oTmp = oCmd.CreateParameter(&#34;@current&#34;, adInteger, adParamInput,, 892)
oCmd.Parameters.Append oTmp
Set oRs = Server.CreateObject(&#34;ADODB.Recordset&#34;)
oRs.Open oCmd
Response.Write oRs.RecordCount & &#34;<hr>&#34;
oRs.Close
Set oRs=Nothing


This code generates the following result when run from an active server page:

-1<hr>

When I execute the raw SQL code (&#34;exec Expandset 892&#34;) against the stored proc in the query analyzer, I get:

item tier
----------- -----------
892 1
948 2
895 2
946 2
945 2
893 2
894 3
944 2
943 2
904 2
896 3
897 4
901 2
903 3
902 3
900 2
947 2
899 2

The source for the stored proc is:
------------------------------------------------------
CREATE PROCEDURE Expandset (@current int) as
SET NOCOUNT ON
DECLARE @level int, @line char(20)
CREATE TABLE #stack (item int, tier int)
CREATE TABLE #output (item int, tier int)
INSERT INTO #stack VALUES (@current, 1)
SELECT @level = 1
WHILE @level > 0
BEGIN
IF EXISTS (SELECT * FROM #stack WHERE tier = @level)
BEGIN
SELECT @current = item
FROM #stack
WHERE tier = @level
SELECT @line = space(@level - 1) + convert(varchar,@current)
INSERT INTO #output (item, tier) values (@current, @level)
DELETE FROM #stack
WHERE tier = @level
AND item = @current
INSERT #stack
SELECT ID, @level + 1
FROM SITE_Container
WHERE parent = @current
IF @@ROWCOUNT > 0
SELECT @level = @level + 1
END
ELSE
SELECT @level = @level - 1
END
SELECT O.item, O.tier FROM #output O
-------------------------------------------------------------------

The relevant portions of the Table definitions for SITE_Container are:

CREATE TABLE [dbo].[SITE_Container] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Parent] [int] NULL)

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

My contact information is:

Jared Nielsen
PO Box 600271
Jacksonville, FL 32260
904-230-1688
888-316-2357 vm / fax

Jared,

I&#39;ve just remembered something about the ADODB RecordSet. I believe that the RecordCount property is only available once you have moved to the end of the RecordSet.

You either do this in your VB Script (very inefficient) or return the number of records from the stored proc with an output parameter.

Your Stored Proc would look like this :
CREATE PROCEDURE Expandset (@current int, @rows int output)
AS ......
....
SELECT @rows = COUNT(*) FROM #output
SELECT O.item, O.tier FROM #output O

Your ASP would look like this:
Set oCmd = Server.CreateObject(&#34;ADODB.Command&#34;)
Set oCmd.ActiveConnection = ConnIS
oCmd.CommandText = &#34;dbo.Expandset&#34; &#39;Name of SP
oCmd.CommandType = adCmdStoredProc &#39;ADO constant for 4
Set oTmp = oCmd.CreateParameter(&#34;@current&#34;, adInteger, adParamInput,, 892)
oCmd.Parameters.Append oTmp
Set oTmp = oCmd.CreateParameter(&#34;@rows&#34;, adInteger, adParamOutput)
oCmd.Parameters.Append oTmp
Set oRs = Server.CreateObject(&#34;ADODB.Recordset&#34;)
Set oRs = oCmd.Execute
Response.Write oCmd.Parameters(&#34;@rows&#34;) & &#34;<hr>&#34;
oRs.Close
Set oRs=Nothing

....

Let me know how you get on...


------------
Jared Nielsen at 10/14/01 3:18:37 PM


I&#39;m so desperate, I&#39;ll pay $100 to the proper solution to this problem. I&#39;m sure it&#39;s an easy fix, but I&#39;m wasting more money every day trying to figure it out...

I have a table with hierarchial data in it (see the bottom tabledef) and I need to query an &#34;indented outline&#34; of the records in it for a tree control on a website. To do that I have to perform some sort of recursive or nested query or I can do all that manipulation in a temporary table/cursor... However, even though the resultset will display when I check the query, when I try to open it using ADO, I get a recordcount of -1.... it&#39;s very frustrating and extremely important.

I&#39;d rather pay an expert here than try to navigate a tech help line.

ConnIS is defined in an earlier include file...

Set oCmd = Server.CreateObject(&#34;ADODB.Command&#34;)
Set oCmd.ActiveConnection = ConnIS
oCmd.CommandText = &#34;dbo.Expandset&#34; &#39;Name of SP
oCmd.CommandType = adCmdStoredProc &#39;ADO constant for 4
Set oTmp = oCmd.CreateParameter(&#34;@current&#34;, adInteger, adParamInput,, 892)
oCmd.Parameters.Append oTmp
Set oRs = Server.CreateObject(&#34;ADODB.Recordset&#34;)
oRs.Open oCmd
Response.Write oRs.RecordCount & &#34;<hr>&#34;
oRs.Close
Set oRs=Nothing


This code generates the following result when run from an active server page:

-1<hr>

When I execute the raw SQL code (&#34;exec Expandset 892&#34;) against the stored proc in the query analyzer, I get:

item tier
----------- -----------
892 1
948 2
895 2
946 2
945 2
893 2
894 3
944 2
943 2
904 2
896 3
897 4
901 2
903 3
902 3
900 2
947 2
899 2

The source for the stored proc is:
------------------------------------------------------
CREATE PROCEDURE Expandset (@current int) as
SET NOCOUNT ON
DECLARE @level int, @line char(20)
CREATE TABLE #stack (item int, tier int)
CREATE TABLE #output (item int, tier int)
INSERT INTO #stack VALUES (@current, 1)
SELECT @level = 1
WHILE @level > 0
BEGIN
IF EXISTS (SELECT * FROM #stack WHERE tier = @level)
BEGIN
SELECT @current = item
FROM #stack
WHERE tier = @level
SELECT @line = space(@level - 1) + convert(varchar,@current)
INSERT INTO #output (item, tier) values (@current, @level)
DELETE FROM #stack
WHERE tier = @level
AND item = @current
INSERT #stack
SELECT ID, @level + 1
FROM SITE_Container
WHERE parent = @current
IF @@ROWCOUNT > 0
SELECT @level = @level + 1
END
ELSE
SELECT @level = @level - 1
END
SELECT O.item, O.tier FROM #output O
-------------------------------------------------------------------

The relevant portions of the Table definitions for SITE_Container are:

CREATE TABLE [dbo].[SITE_Container] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Parent] [int] NULL)

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

My contact information is:

Jared Nielsen
PO Box 600271
Jacksonville, FL 32260
904-230-1688
888-316-2357 vm / fax

That was precisely the answer. I&#39;ll email you privately to send the check.... Best $100 I ever spent !:)

Jared


------------
John Woos at 10/17/01 5:21:22 AM

Jared,

I&#39;ve just remembered something about the ADODB RecordSet. I believe that the RecordCount property is only available once you have moved to the end of the RecordSet.

You either do this in your VB Script (very inefficient) or return the number of records from the stored proc with an output parameter.

Your Stored Proc would look like this :
CREATE PROCEDURE Expandset (@current int, @rows int output)
AS ......
....
SELECT @rows = COUNT(*) FROM #output
SELECT O.item, O.tier FROM #output O

Your ASP would look like this:
Set oCmd = Server.CreateObject(&#34;ADODB.Command&#34;)
Set oCmd.ActiveConnection = ConnIS
oCmd.CommandText = &#34;dbo.Expandset&#34; &#39;Name of SP
oCmd.CommandType = adCmdStoredProc &#39;ADO constant for 4
Set oTmp = oCmd.CreateParameter(&#34;@current&#34;, adInteger, adParamInput,, 892)
oCmd.Parameters.Append oTmp
Set oTmp = oCmd.CreateParameter(&#34;@rows&#34;, adInteger, adParamOutput)
oCmd.Parameters.Append oTmp
Set oRs = Server.CreateObject(&#34;ADODB.Recordset&#34;)
Set oRs = oCmd.Execute
Response.Write oCmd.Parameters(&#34;@rows&#34;) & &#34;<hr>&#34;
oRs.Close
Set oRs=Nothing

....

Let me know how you get on...


------------
Jared Nielsen at 10/14/01 3:18:37 PM


I&#39;m so desperate, I&#39;ll pay $100 to the proper solution to this problem. I&#39;m sure it&#39;s an easy fix, but I&#39;m wasting more money every day trying to figure it out...

I have a table with hierarchial data in it (see the bottom tabledef) and I need to query an &#34;indented outline&#34; of the records in it for a tree control on a website. To do that I have to perform some sort of recursive or nested query or I can do all that manipulation in a temporary table/cursor... However, even though the resultset will display when I check the query, when I try to open it using ADO, I get a recordcount of -1.... it&#39;s very frustrating and extremely important.

I&#39;d rather pay an expert here than try to navigate a tech help line.

ConnIS is defined in an earlier include file...

Set oCmd = Server.CreateObject(&#34;ADODB.Command&#34;)
Set oCmd.ActiveConnection = ConnIS
oCmd.CommandText = &#34;dbo.Expandset&#34; &#39;Name of SP
oCmd.CommandType = adCmdStoredProc &#39;ADO constant for 4
Set oTmp = oCmd.CreateParameter(&#34;@current&#34;, adInteger, adParamInput,, 892)
oCmd.Parameters.Append oTmp
Set oRs = Server.CreateObject(&#34;ADODB.Recordset&#34;)
oRs.Open oCmd
Response.Write oRs.RecordCount & &#34;<hr>&#34;
oRs.Close
Set oRs=Nothing


This code generates the following result when run from an active server page:

-1<hr>

When I execute the raw SQL code (&#34;exec Expandset 892&#34;) against the stored proc in the query analyzer, I get:

item tier
----------- -----------
892 1
948 2
895 2
946 2
945 2
893 2
894 3
944 2
943 2
904 2
896 3
897 4
901 2
903 3
902 3
900 2
947 2
899 2

The source for the stored proc is:
------------------------------------------------------
CREATE PROCEDURE Expandset (@current int) as
SET NOCOUNT ON
DECLARE @level int, @line char(20)
CREATE TABLE #stack (item int, tier int)
CREATE TABLE #output (item int, tier int)
INSERT INTO #stack VALUES (@current, 1)
SELECT @level = 1
WHILE @level > 0
BEGIN
IF EXISTS (SELECT * FROM #stack WHERE tier = @level)
BEGIN
SELECT @current = item
FROM #stack
WHERE tier = @level
SELECT @line = space(@level - 1) + convert(varchar,@current)
INSERT INTO #output (item, tier) values (@current, @level)
DELETE FROM #stack
WHERE tier = @level
AND item = @current
INSERT #stack
SELECT ID, @level + 1
FROM SITE_Container
WHERE parent = @current
IF @@ROWCOUNT > 0
SELECT @level = @level + 1
END
ELSE
SELECT @level = @level - 1
END
SELECT O.item, O.tier FROM #output O
-------------------------------------------------------------------

The relevant portions of the Table definitions for SITE_Container are:

CREATE TABLE [dbo].[SITE_Container] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Parent] [int] NULL)

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

My contact information is:

Jared Nielsen
PO Box 600271
Jacksonville, FL 32260
904-230-1688
888-316-2357 vm / fax