Results 1 to 4 of 4

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

  1. 10-14-2001, 02:18 PM #1
    Jared Nielsen Guest

    $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&#34
    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&#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
    Reply With Quote Reply With Quote
  2. 10-17-2001, 04:02 AM #2
    John Woods Guest

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

    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
    Reply With Quote Reply With Quote
  3. 10-17-2001, 04:21 AM #3
    John Woos Guest

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

    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
    Reply With Quote Reply With Quote
  4. 10-18-2001, 02:35 PM #4
    Jared Nielsen Guest

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

    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
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Forum Rules