SPROC Recordcount

Discussion in 'ASP General' started by scott, May 23, 2005.

  1. scott

    scott Guest

    In LISTING 2, I have a SPROC that returns a recordset and a recordcount in
    SQL QA. I can access the Recordset with no problem. How can I grab the
    Recordcount with ASP code at the same time I'm getting the Recordset? In QA,
    the RecordCount displays below the Recordset.

    The below USAGE code will display my SPROC results.

    USAGE:
    EXEC SELECT_WITH_PAGING 'CustomerID, ShipName', 'OrderID',
    'Northwind.dbo_Orders', 3, 10, 1, '', 'OrderDate' ************************
    LISTING 1: ASP CODE
    Set objConn = CreateObject("ADODB.Connection")
    objConn.Open myDSN
    Set objRS = Server.CreateObject("ADODB.Recordset")

    strSQL = "SELECT_WITH_PAGING " & SParms

    objRS.Open strSQL, objConn





    ************************
    LISTING 2: SPROC

    CREATE PROCEDURE SELECT_WITH_PAGING (
    @strFields VARCHAR(4000) ,
    @strPK VARCHAR(100),
    @strTables VARCHAR(4000),
    @intPageNo INT = 1,
    @intPageSize INT = NULL,
    @blnGetRecordCount BIT = 0,
    @strFilter VARCHAR(8000) = NULL,
    @strSort VARCHAR(8000) = NULL,
    @strGroup VARCHAR(8000) = NULL
    )

    AS
    DECLARE @blnBringAllRecords BIT
    DECLARE @strPageNo VARCHAR(50)
    DECLARE @strPageSize VARCHAR(50)
    DECLARE @strSkippedRows VARCHAR(50)

    DECLARE @strFilterCriteria VARCHAR(8000)
    DECLARE @strSimpleFilter VARCHAR(8000)
    DECLARE @strSortCriteria VARCHAR(8000)
    DECLARE @strGroupCriteria VARCHAR(8000)

    DECLARE @intRecordcount INT
    DECLARE @intPagecount INT


    --******** NORMALIZE THE PAGING CRITERIA
    --if no meaningful inputs are provided, we can avoid paging and execute a
    more efficient query, so we will set a flag that will help with that
    (blnBringAllRecords)

    IF @intPageNo < 1
    SET @intPageNo = 1

    SET @strPageNo = CONVERT(VARCHAR(50), @intPageNo)

    IF @intPageSize IS NULL OR @intPageSize < 1 -- BRING ALL RECORDS, DON'T DO
    PAGING
    SET @blnBringAllRecords = 1
    ELSE
    BEGIN
    SET @blnBringAllRecords = 0
    SET @strPageSize = CONVERT(VARCHAR(50), @intPageSize)
    SET @strPageNo = CONVERT(VARCHAR(50), @intPageNo)
    SET @strSkippedRows = CONVERT(VARCHAR(50), @intPageSize * (@intPageNo -
    1))
    END


    --******** NORMALIZE THE FILTER AND SORTING CRITERIA
    --if they are empty, we will avoid filtering and sorting, respectively,
    executing more efficient queries

    IF @strFilter IS NOT NULL AND @strFilter != ''
    BEGIN
    SET @strFilterCriteria = ' WHERE ' + @strFilter + ' '
    SET @strSimpleFilter = ' AND ' + @strFilter + ' '
    END
    ELSE
    BEGIN
    SET @strSimpleFilter = ''
    SET @strFilterCriteria = ''
    END

    IF @strSort IS NOT NULL AND @strSort != ''
    SET @strSortCriteria = ' ORDER BY ' + @strSort + ' '
    ELSE
    SET @strSortCriteria = ''

    IF @strGroup IS NOT NULL AND @strGroup != ''
    SET @strGroupCriteria = ' GROUP BY ' + @strGroup + ' '
    ELSE
    SET @strGroupCriteria = ''



    --************************** NOW START DOING THE REAL WORK
    --!NOTE: for potentially improved performance, use sp_executesql instead of
    EXEC

    IF @blnBringAllRecords = 1 --ignore paging and run a simple select
    BEGIN

    EXEC (
    'SELECT ' + @strFields + ' FROM ' + @strTables + @strFilterCriteria +
    @strGroupCriteria + @strSortCriteria
    )

    END-- WE HAD TO BRING ALL RECORDS
    ELSE --BRING ONLY A PARTICULAR PAGE
    BEGIN
    IF @intPageNo = 1 --in this case we can execute a more efficient query,
    with no subqueries
    EXEC (
    'SELECT TOP ' + @strPageSize + ' ' + @strFields + ' FROM ' + @strTables +
    @strFilterCriteria + @strGroupCriteria + @strSortCriteria
    )
    ELSE --execute a structure of subqueries that brings the correct page
    EXEC (
    'SELECT ' + @strFields + ' FROM ' + @strTables + ' WHERE ' + @strPK + '
    IN ' + '
    (SELECT TOP ' + @strPageSize + ' ' + @strPK + ' FROM ' + @strTables +
    ' WHERE ' + @strPK + ' NOT IN ' + '
    (SELECT TOP ' + @strSkippedRows + ' ' + @strPK + ' FROM ' + @strTables
    + @strFilterCriteria + @strGroupCriteria + @strSortCriteria + ') ' +
    @strSimpleFilter +
    @strGroupCriteria +
    @strSortCriteria + ') ' +
    @strGroupCriteria +
    @strSortCriteria
    )

    END


    --IF WE NEED TO RETURN THE RECORDCOUNT
    IF @blnGetRecordCount = 1
    IF @strGroupCriteria != ''
    EXEC (
    'SELECT COUNT(*) AS RECORDCOUNT FROM (SELECT COUNT(*) FROM ' + @strTables
    + @strFilterCriteria + @strGroupCriteria + ') AS tbl (id)'
    )
    ELSE
    EXEC (
    'SELECT COUNT(*) AS RECORDCOUNT FROM ' + @strTables + @strFilterCriteria
    + @strGroupCriteria
    )
    GO
     
    scott, May 23, 2005
    #1
    1. Advertising

  2. scott wrote:
    > In LISTING 2, I have a SPROC that returns a recordset and a
    > recordcount in SQL QA. I can access the Recordset with no problem.
    > How can I grab the Recordcount with ASP code at the same time I'm
    > getting the Recordset? In QA, the RecordCount displays below the
    > Recordset.
    >
    > The below USAGE code will display my SPROC results.
    >
    > USAGE:
    > EXEC SELECT_WITH_PAGING 'CustomerID, ShipName', 'OrderID',
    > 'Northwind.dbo_Orders', 3, 10, 1, '', 'OrderDate'
    > ************************ LISTING 1: ASP CODE
    > Set objConn = CreateObject("ADODB.Connection")
    > objConn.Open myDSN
    > Set objRS = Server.CreateObject("ADODB.Recordset")
    >
    > strSQL = "SELECT_WITH_PAGING " & SParms
    >
    > objRS.Open strSQL, objConn
    >
    >
    >
    >
    >
    > ************************
    > LISTING 2: SPROC
    >
    > CREATE PROCEDURE SELECT_WITH_PAGING (
    > @strFields VARCHAR(4000) ,
    > @strPK VARCHAR(100),
    > @strTables VARCHAR(4000),
    > @intPageNo INT = 1,
    > @intPageSize INT = NULL,
    > @blnGetRecordCount BIT = 0,
    > @strFilter VARCHAR(8000) = NULL,
    > @strSort VARCHAR(8000) = NULL,
    > @strGroup VARCHAR(8000) = NULL
    > )
    >
    > AS

    <snip>
    >
    > --IF WE NEED TO RETURN THE RECORDCOUNT
    > IF @blnGetRecordCount = 1
    > IF @strGroupCriteria != ''
    > EXEC (
    > 'SELECT COUNT(*) AS RECORDCOUNT FROM (SELECT COUNT(*) FROM ' +
    > @strTables + @strFilterCriteria + @strGroupCriteria + ') AS tbl (id)'
    > )
    > ELSE
    > EXEC (
    > 'SELECT COUNT(*) AS RECORDCOUNT FROM ' + @strTables +
    > @strFilterCriteria + @strGroupCriteria
    > )
    > GO


    I will leave it to others to comment on this use of dynamic sql (think "sql
    injection") ...

    You're returning a second resultset containing the count, so you would use
    the recordset's NextRecordset method to access it:

    set objRS = objRS.NextRecordset
    reccount=objRS("recordcount")

    I would be more inclined to use an output parameter, using a Command object
    to retrieve the value.

    Bob Barrows
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
     
    Bob Barrows [MVP], May 23, 2005
    #2
    1. Advertising

  3. Bob Barrows [MVP] wrote:
    >
    > I would be more inclined to use an output parameter, using
    > a Command object to retrieve the value.


    Can your preferred SP-as-method-of-connection technique[1] deal with output
    parameters?



    [1] I don't use it because it seem not to work in JScript
    --
    Dave Anderson

    Unsolicited commercial email will be read at a cost of $500 per message. Use
    of this email address implies consent to these terms. Please do not contact
    me directly or ask me to contact you directly for assistance. If your
    question is worth asking, it's worth posting.
     
    Dave Anderson, May 23, 2005
    #3
  4. Dave Anderson wrote:
    > Bob Barrows [MVP] wrote:
    >>
    >> I would be more inclined to use an output parameter, using
    >> a Command object to retrieve the value.

    >
    > Can your preferred SP-as-method-of-connection technique[1] deal with
    > output parameters?
    >


    No. That technique can only be used if there are no output parameters whose
    values need to be retrieved, and if the developer is not interested in
    retrieving the Return parameter value. An explicit Command object must be
    used in either of these situations.

    Bob Barrows
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
     
    Bob Barrows [MVP], May 23, 2005
    #4
  5. Dave Anderson wrote:
    > Bob Barrows [MVP] wrote:
    >>
    >> I would be more inclined to use an output parameter, using
    >> a Command object to retrieve the value.

    >
    > Can your preferred SP-as-method-of-connection technique[1] deal with
    > output parameters?
    > [1] I don't use it because it seem not to work in JScript


    It seems to work fine for me:

    <%@ Language=JavaScript %>
    <%
    var cn,strConn, rs;
    cn = new ActiveXObject("ADODB.Connection")
    strConn = "provider=sqloledb;data source=xxxxx;user id = xxxx;" +
    "password=xxxx;initial catalog=pubs"
    cn.open(strConn)
    rs = new ActiveXObject("ADODB.Recordset")
    //uncomment the following to test optional argument
    //cn.RecordCountProblemDemo("test",rs)
    //comment the following when testing optional argument
    cn.RecordCountProblemDemo(rs)
    Response.Write(rs.getstring());
    rs.close()
    cn.close()
    %>

    The procedure is:

    CREATE PROCEDURE [dbo].[RecordCountProblemDemo]
    (@SomeVarThatDoesntGetUsed VARCHAR(11) = NULL)
    AS
    BEGIN
    SET NOCOUNT ON
    IF @SomeVarThatDoesntGetUsed IS NULL
    SET @SomeVarThatDoesntGetUsed = 'hello'

    SELECT au_fname, au_lname,
    @SomeVarThatDoesntGetUsed FROM authors

    END

    Bob Barrows
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
     
    Bob Barrows [MVP], May 23, 2005
    #5
  6. scott wrote:
    > In LISTING 2, I have a SPROC that returns a recordset and a
    > recordcount in SQL QA. I can access the Recordset with no problem.
    > How can I grab the Recordcount with ASP code at the same time I'm
    > getting the Recordset? In QA, the RecordCount displays below the
    > Recordset.


    A useful generic way to get the recordcount is use GetRows() and examine the
    dimensions:

    RSArray = RS.GetRows()
    RecordCount = UBound(RSArray,2)

    http://msdn.microsoft.com/library/en-us/ado270/htm/mdmthgetrows.asp



    This has several advantages. The most obvious is that you can order the
    output any way you like.

    Secondly, you only need one SELECT in your stored procedure.

    Furthermore, GetRows() + array iteration is much faster than stepping
    through a recordset with MoveNext().

    In addition, you can immediately close your connection (or use it for
    something else) after GetRows(), which can give you additional performance
    gains.

    Lastly, if you have a [SELECT Count] to measure the size of a [SELECT],
    every change to one requires a change to the other. Measuring the output
    array always returns an accurate count, no matter what changes are made to
    the SELECT.



    --
    Dave Anderson

    Unsolicited commercial email will be read at a cost of $500 per message. Use
    of this email address implies consent to these terms. Please do not contact
    me directly or ask me to contact you directly for assistance. If your
    question is worth asking, it's worth posting.
     
    Dave Anderson, May 23, 2005
    #6
  7. scott

    scott Guest

    1st off, thanks for educating me on the NextRecordset prop, didn't know such
    exists. However, for learning purposes could you either supply a few lines
    of code similating retrieving a recordset followed by an output parameter? A
    link to an example would be fine.

    I didn't realize I could do 2 things at once with ASP.



    "Bob Barrows [MVP]" <> wrote in message
    news:...
    > scott wrote:
    >> In LISTING 2, I have a SPROC that returns a recordset and a
    >> recordcount in SQL QA. I can access the Recordset with no problem.
    >> How can I grab the Recordcount with ASP code at the same time I'm
    >> getting the Recordset? In QA, the RecordCount displays below the
    >> Recordset.
    >>
    >> The below USAGE code will display my SPROC results.
    >>
    >> USAGE:
    >> EXEC SELECT_WITH_PAGING 'CustomerID, ShipName', 'OrderID',
    >> 'Northwind.dbo_Orders', 3, 10, 1, '', 'OrderDate'
    >> ************************ LISTING 1: ASP CODE
    >> Set objConn = CreateObject("ADODB.Connection")
    >> objConn.Open myDSN
    >> Set objRS = Server.CreateObject("ADODB.Recordset")
    >>
    >> strSQL = "SELECT_WITH_PAGING " & SParms
    >>
    >> objRS.Open strSQL, objConn
    >>
    >>
    >>
    >>
    >>
    >> ************************
    >> LISTING 2: SPROC
    >>
    >> CREATE PROCEDURE SELECT_WITH_PAGING (
    >> @strFields VARCHAR(4000) ,
    >> @strPK VARCHAR(100),
    >> @strTables VARCHAR(4000),
    >> @intPageNo INT = 1,
    >> @intPageSize INT = NULL,
    >> @blnGetRecordCount BIT = 0,
    >> @strFilter VARCHAR(8000) = NULL,
    >> @strSort VARCHAR(8000) = NULL,
    >> @strGroup VARCHAR(8000) = NULL
    >> )
    >>
    >> AS

    > <snip>
    >>
    >> --IF WE NEED TO RETURN THE RECORDCOUNT
    >> IF @blnGetRecordCount = 1
    >> IF @strGroupCriteria != ''
    >> EXEC (
    >> 'SELECT COUNT(*) AS RECORDCOUNT FROM (SELECT COUNT(*) FROM ' +
    >> @strTables + @strFilterCriteria + @strGroupCriteria + ') AS tbl (id)'
    >> )
    >> ELSE
    >> EXEC (
    >> 'SELECT COUNT(*) AS RECORDCOUNT FROM ' + @strTables +
    >> @strFilterCriteria + @strGroupCriteria
    >> )
    >> GO

    >
    > I will leave it to others to comment on this use of dynamic sql (think
    > "sql
    > injection") ...
    >
    > You're returning a second resultset containing the count, so you would use
    > the recordset's NextRecordset method to access it:
    >
    > set objRS = objRS.NextRecordset
    > reccount=objRS("recordcount")
    >
    > I would be more inclined to use an output parameter, using a Command
    > object
    > to retrieve the value.
    >
    > Bob Barrows
    > --
    > Microsoft MVP -- ASP/ASP.NET
    > Please reply to the newsgroup. The email account listed in my From
    > header is my spam trap, so I don't check it very often. You will get a
    > quicker response by posting to the newsgroup.
    >
    >
     
    scott, May 23, 2005
    #7
  8. Dave Anderson wrote:
    > scott wrote:
    >> In LISTING 2, I have a SPROC that returns a recordset and a
    >> recordcount in SQL QA. I can access the Recordset with no problem.
    >> How can I grab the Recordcount with ASP code at the same time I'm
    >> getting the Recordset? In QA, the RecordCount displays below the
    >> Recordset.

    >
    > A useful generic way to get the recordcount is use GetRows() and
    > examine the dimensions:
    >


    I second this. I can't believe I failed to mention it.

    Bob Barrows
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
     
    Bob Barrows [MVP], May 23, 2005
    #8
  9. OK, here's a simple procedure:

    create procedure OutputTest (
    @out int output) AS
    BEGIN
    SET NOCOUNT ON
    SET @out = 33
    exec sp_who2
    END

    In ASP, you would do this (I'm using the ADO constants for readability. You
    should #include the adovbs.inc file, or use one of the methods shown here to
    define the constants - http://www.aspfaq.com/show.asp?id=2112):

    <%
    dim cn, cmd, rs, params
    set cn=createobject("adodb.connection")
    cn.open "<valid connection string>"
    set cmd=createobject("adodb.command")
    cmd.CommandText="OutputTest"
    cmd.ActiveConnection= cn
    cmd.CommandType = adCmdStoredProc
    set params=cmd.parameters
    params.append cmd.createparameter("@RETURN_VALUE", _
    adInteger, adParamReturnValue)
    params.append cmd.createparameter("@out", adInteger, _
    adParamOutput)
    set rs=cmd.Execute
    'the recordset has to be "consumed" before the output
    'value is available, so:
    response.write rs.GetString
    rs.close
    response.write "output parameter: " & params(1).value
    cn.close:set cn=nothing
    set rs=nothing
    %>

    But again, GetRows is a better solution for your situation.

    Bob Barrows
    scott wrote:
    > 1st off, thanks for educating me on the NextRecordset prop, didn't
    > know such exists. However, for learning purposes could you either
    > supply a few lines of code similating retrieving a recordset followed
    > by an output parameter? A link to an example would be fine.
    >
    > I didn't realize I could do 2 things at once with ASP.
    >
    >
    >
    > "Bob Barrows [MVP]" <> wrote in message
    > news:...
    >> scott wrote:
    >>> In LISTING 2, I have a SPROC that returns a recordset and a
    >>> recordcount in SQL QA. I can access the Recordset with no problem.
    >>> How can I grab the Recordcount with ASP code at the same time I'm
    >>> getting the Recordset? In QA, the RecordCount displays below the
    >>> Recordset.
    >>>
    >>> The below USAGE code will display my SPROC results.
    >>>
    >>> USAGE:
    >>> EXEC SELECT_WITH_PAGING 'CustomerID, ShipName', 'OrderID',
    >>> 'Northwind.dbo_Orders', 3, 10, 1, '', 'OrderDate'
    >>> ************************ LISTING 1: ASP CODE
    >>> Set objConn = CreateObject("ADODB.Connection")
    >>> objConn.Open myDSN
    >>> Set objRS = Server.CreateObject("ADODB.Recordset")
    >>>
    >>> strSQL = "SELECT_WITH_PAGING " & SParms
    >>>
    >>> objRS.Open strSQL, objConn
    >>>
    >>>
    >>>
    >>>
    >>>
    >>> ************************
    >>> LISTING 2: SPROC
    >>>
    >>> CREATE PROCEDURE SELECT_WITH_PAGING (
    >>> @strFields VARCHAR(4000) ,
    >>> @strPK VARCHAR(100),
    >>> @strTables VARCHAR(4000),
    >>> @intPageNo INT = 1,
    >>> @intPageSize INT = NULL,
    >>> @blnGetRecordCount BIT = 0,
    >>> @strFilter VARCHAR(8000) = NULL,
    >>> @strSort VARCHAR(8000) = NULL,
    >>> @strGroup VARCHAR(8000) = NULL
    >>> )
    >>>
    >>> AS

    >> <snip>
    >>>
    >>> --IF WE NEED TO RETURN THE RECORDCOUNT
    >>> IF @blnGetRecordCount = 1
    >>> IF @strGroupCriteria != ''
    >>> EXEC (
    >>> 'SELECT COUNT(*) AS RECORDCOUNT FROM (SELECT COUNT(*) FROM ' +
    >>> @strTables + @strFilterCriteria + @strGroupCriteria + ') AS tbl
    >>> (id)' )
    >>> ELSE
    >>> EXEC (
    >>> 'SELECT COUNT(*) AS RECORDCOUNT FROM ' + @strTables +
    >>> @strFilterCriteria + @strGroupCriteria
    >>> )
    >>> GO

    >>
    >> I will leave it to others to comment on this use of dynamic sql
    >> (think "sql
    >> injection") ...
    >>
    >> You're returning a second resultset containing the count, so you
    >> would use the recordset's NextRecordset method to access it:
    >>
    >> set objRS = objRS.NextRecordset
    >> reccount=objRS("recordcount")
    >>
    >> I would be more inclined to use an output parameter, using a Command
    >> object
    >> to retrieve the value.
    >>
    >> Bob Barrows
    >> --
    >> Microsoft MVP -- ASP/ASP.NET
    >> Please reply to the newsgroup. The email account listed in my From
    >> header is my spam trap, so I don't check it very often. You will get
    >> a quicker response by posting to the newsgroup.


    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
     
    Bob Barrows [MVP], May 23, 2005
    #9
  10. scott

    scott Guest

    I have 1 small problem with NextRecordSet property. The below does work, but
    I can't use the "NextRecordset" line until I finish my 1st RecordSet Loop.
    If I try and move the "NextRecordset" part at beginning, I get errors. I'd
    like to be able to display the iNumRecords (total records rendered by
    NextRecordset) before I display my Recordset table. Is there a way around
    this?


    Set objConn = CreateObject("ADODB.Connection")
    objConn.Open sDSN

    Set objRS = Server.CreateObject("ADODB.Recordset")

    sSQL = "SELECT_WITH_PAGING " & SParms
    objRS.Open sSQL, objConn

    Do While Not objRS.EOF
    ' Display Records
    objRS.MoveNext
    Loop

    Set objRS = objRS.NextRecordset
    iNumRecords=objRS("RecordCount")
    Response.Write "<br>iNumRecords: " & iNumRecords & "<br>"




    "Bob Barrows [MVP]" <> wrote in message
    news:u$...
    > OK, here's a simple procedure:
    >
    > create procedure OutputTest (
    > @out int output) AS
    > BEGIN
    > SET NOCOUNT ON
    > SET @out = 33
    > exec sp_who2
    > END
    >
    > In ASP, you would do this (I'm using the ADO constants for readability.
    > You
    > should #include the adovbs.inc file, or use one of the methods shown here
    > to
    > define the constants - http://www.aspfaq.com/show.asp?id=2112):
    >
    > <%
    > dim cn, cmd, rs, params
    > set cn=createobject("adodb.connection")
    > cn.open "<valid connection string>"
    > set cmd=createobject("adodb.command")
    > cmd.CommandText="OutputTest"
    > cmd.ActiveConnection= cn
    > cmd.CommandType = adCmdStoredProc
    > set params=cmd.parameters
    > params.append cmd.createparameter("@RETURN_VALUE", _
    > adInteger, adParamReturnValue)
    > params.append cmd.createparameter("@out", adInteger, _
    > adParamOutput)
    > set rs=cmd.Execute
    > 'the recordset has to be "consumed" before the output
    > 'value is available, so:
    > response.write rs.GetString
    > rs.close
    > response.write "output parameter: " & params(1).value
    > cn.close:set cn=nothing
    > set rs=nothing
    > %>
    >
    > But again, GetRows is a better solution for your situation.
    >
    > Bob Barrows
    > scott wrote:
    >> 1st off, thanks for educating me on the NextRecordset prop, didn't
    >> know such exists. However, for learning purposes could you either
    >> supply a few lines of code similating retrieving a recordset followed
    >> by an output parameter? A link to an example would be fine.
    >>
    >> I didn't realize I could do 2 things at once with ASP.
    >>
    >>
    >>
    >> "Bob Barrows [MVP]" <> wrote in message
    >> news:...
    >>> scott wrote:
    >>>> In LISTING 2, I have a SPROC that returns a recordset and a
    >>>> recordcount in SQL QA. I can access the Recordset with no problem.
    >>>> How can I grab the Recordcount with ASP code at the same time I'm
    >>>> getting the Recordset? In QA, the RecordCount displays below the
    >>>> Recordset.
    >>>>
    >>>> The below USAGE code will display my SPROC results.
    >>>>
    >>>> USAGE:
    >>>> EXEC SELECT_WITH_PAGING 'CustomerID, ShipName', 'OrderID',
    >>>> 'Northwind.dbo_Orders', 3, 10, 1, '', 'OrderDate'
    >>>> ************************ LISTING 1: ASP CODE
    >>>> Set objConn = CreateObject("ADODB.Connection")
    >>>> objConn.Open myDSN
    >>>> Set objRS = Server.CreateObject("ADODB.Recordset")
    >>>>
    >>>> strSQL = "SELECT_WITH_PAGING " & SParms
    >>>>
    >>>> objRS.Open strSQL, objConn
    >>>>
    >>>>
    >>>>
    >>>>
    >>>>
    >>>> ************************
    >>>> LISTING 2: SPROC
    >>>>
    >>>> CREATE PROCEDURE SELECT_WITH_PAGING (
    >>>> @strFields VARCHAR(4000) ,
    >>>> @strPK VARCHAR(100),
    >>>> @strTables VARCHAR(4000),
    >>>> @intPageNo INT = 1,
    >>>> @intPageSize INT = NULL,
    >>>> @blnGetRecordCount BIT = 0,
    >>>> @strFilter VARCHAR(8000) = NULL,
    >>>> @strSort VARCHAR(8000) = NULL,
    >>>> @strGroup VARCHAR(8000) = NULL
    >>>> )
    >>>>
    >>>> AS
    >>> <snip>
    >>>>
    >>>> --IF WE NEED TO RETURN THE RECORDCOUNT
    >>>> IF @blnGetRecordCount = 1
    >>>> IF @strGroupCriteria != ''
    >>>> EXEC (
    >>>> 'SELECT COUNT(*) AS RECORDCOUNT FROM (SELECT COUNT(*) FROM ' +
    >>>> @strTables + @strFilterCriteria + @strGroupCriteria + ') AS tbl
    >>>> (id)' )
    >>>> ELSE
    >>>> EXEC (
    >>>> 'SELECT COUNT(*) AS RECORDCOUNT FROM ' + @strTables +
    >>>> @strFilterCriteria + @strGroupCriteria
    >>>> )
    >>>> GO
    >>>
    >>> I will leave it to others to comment on this use of dynamic sql
    >>> (think "sql
    >>> injection") ...
    >>>
    >>> You're returning a second resultset containing the count, so you
    >>> would use the recordset's NextRecordset method to access it:
    >>>
    >>> set objRS = objRS.NextRecordset
    >>> reccount=objRS("recordcount")
    >>>
    >>> I would be more inclined to use an output parameter, using a Command
    >>> object
    >>> to retrieve the value.
    >>>
    >>> Bob Barrows
    >>> --
    >>> Microsoft MVP -- ASP/ASP.NET
    >>> Please reply to the newsgroup. The email account listed in my From
    >>> header is my spam trap, so I don't check it very often. You will get
    >>> a quicker response by posting to the newsgroup.

    >
    > --
    > Microsoft MVP -- ASP/ASP.NET
    > Please reply to the newsgroup. The email account listed in my From
    > header is my spam trap, so I don't check it very often. You will get a
    > quicker response by posting to the newsgroup.
    >
    >
     
    scott, May 23, 2005
    #10
  11. scott

    scott Guest

    will the GetRows() method work with a SPROC that returns a recordset, then a
    recordcount?

    "Dave Anderson" <> wrote in message
    news:...
    > scott wrote:
    >> In LISTING 2, I have a SPROC that returns a recordset and a
    >> recordcount in SQL QA. I can access the Recordset with no problem.
    >> How can I grab the Recordcount with ASP code at the same time I'm
    >> getting the Recordset? In QA, the RecordCount displays below the
    >> Recordset.

    >
    > A useful generic way to get the recordcount is use GetRows() and examine
    > the dimensions:
    >
    > RSArray = RS.GetRows()
    > RecordCount = UBound(RSArray,2)
    >
    > http://msdn.microsoft.com/library/en-us/ado270/htm/mdmthgetrows.asp
    >
    >
    >
    > This has several advantages. The most obvious is that you can order the
    > output any way you like.
    >
    > Secondly, you only need one SELECT in your stored procedure.
    >
    > Furthermore, GetRows() + array iteration is much faster than stepping
    > through a recordset with MoveNext().
    >
    > In addition, you can immediately close your connection (or use it for
    > something else) after GetRows(), which can give you additional performance
    > gains.
    >
    > Lastly, if you have a [SELECT Count] to measure the size of a [SELECT],
    > every change to one requires a change to the other. Measuring the output
    > array always returns an accurate count, no matter what changes are made to
    > the SELECT.
    >
    >
    >
    > --
    > Dave Anderson
    >
    > Unsolicited commercial email will be read at a cost of $500 per message.
    > Use of this email address implies consent to these terms. Please do not
    > contact me directly or ask me to contact you directly for assistance. If
    > your question is worth asking, it's worth posting.
    >
     
    scott, May 23, 2005
    #11
  12. scott wrote:
    > I have 1 small problem with NextRecordSet property. The below does
    > work, but I can't use the "NextRecordset" line until I finish my 1st
    > RecordSet Loop. If I try and move the "NextRecordset" part at
    > beginning, I get errors. I'd like to be able to display the
    > iNumRecords (total records rendered by NextRecordset) before I
    > display my Recordset table. Is there a way around this?
    >
    >

    Yes. Use GetRows to move your data into an array, then call NextRecordset
    (however, the Nextrecordset will no longer be necessary because you'll be
    able to use ubound to determine the number of records that were returned
    from the procedure ... )

    If you're truly adverse to arrays for some reason, you can create a copy of
    your recordset using the Clone method.

    Bob Barrows
    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows [MVP], May 23, 2005
    #12
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. =?Utf-8?B?UA==?=
    Replies:
    1
    Views:
    2,234
    Michelle Hlaing
    Nov 30, 2004
  2. Chad Micheal Lawson via .NET 247

    SQL 2k - ASP.NET - SPROC - "Input string was not in a correct format."

    Chad Micheal Lawson via .NET 247, Sep 24, 2004, in forum: ASP .Net
    Replies:
    4
    Views:
    626
    Greg Burns
    Sep 24, 2004
  3. =?Utf-8?B?c2tpcmtieQ==?=

    Problem using COMPUTE with SPROC on ASP Application

    =?Utf-8?B?c2tpcmtieQ==?=, Nov 1, 2004, in forum: ASP .Net
    Replies:
    1
    Views:
    308
    Scott Allen
    Nov 1, 2004
  4. Mike L.
    Replies:
    7
    Views:
    1,489
    Norman Yuan
    Jan 30, 2005
  5. Elmo Watson
    Replies:
    0
    Views:
    419
    Elmo Watson
    Jul 20, 2006
Loading...

Share This Page