Returning recordsets from a stored procedure

Discussion in 'ASP General' started by stjulian, Jul 27, 2007.

  1. stjulian

    stjulian Guest

    (IIS 6.0, SQL Server 2000)

    I have a block of code that populates a recordset from a stored procedure.
    The problem is, the recordset seems to be forward only (which would be OK),
    but can't jump with the "AbsolutePage" property (which isn't OK)

    How do I define the recordset that will allow this?

    Julian


    ----------------------------------------------------------------------------------
    Set adocmd = Server.CreateObject("ADODB.Command")
    adocmd.CommandTimeout = 120
    adocmd.ActiveConnection = conn
    adocmd.CommandType = adCmdStoredProc
    adocmd.CommandText = "dbo.spr_searchALL"

    Set rsSearchM = Server.CreateObject ("ADODB.Recordset")
    rsSearchM.ActiveConnection = conn
    rsSearchM.CursorLocation = 3

    ' write to database using spr
    With adocmd

    set param = .CreateParameter("@criteria", adVarchar, adParamInput, 50,
    "red" )
    .parameters.append param

    set param = .createparameter("@numrows", adInteger, adParamOutput)
    .parameters.append param

    On Error Resume Next

    errorstring = ""
    errornumber = 0
    valid = 0

    rsSearchM.open = .execute

    '-- check the return value
    If Err.Number <> 0 Then
    errorstring = "<p>Error Number " & Err.Number & "<br>" & "The Error
    Code was: " & Err.Description & "</p>"
    errornumber = Err.Number
    Response.Write(errorstring)
    Response.Redirect(HomePath & "/error.asp?eid=unknown")
    End If

    .execute
    numrowsM = .Parameters("@numrows").Value

    End With
    On Error GoTo 0
    set adocmd = nothing

    pagesize = 1000
    rsSearchM.PageSize = pagesize
    numpages = rsSearchM.PageCount

    rsSearchM.MoveFirst
    rsSearchM.AbsolutePage = currpage

    ----------------------------------------------------------------------------------
     
    stjulian, Jul 27, 2007
    #1
    1. Advertising

  2. stjulian wrote:
    > (IIS 6.0, SQL Server 2000)
    >
    > I have a block of code that populates a recordset from a stored
    > procedure. The problem is, the recordset seems to be forward only (which
    > would
    > be OK), but can't jump with the "AbsolutePage" property (which isn't OK)
    >
    > How do I define the recordset that will allow this?
    >
    > Julian
    >
    >
    > ----------------------------------------------------------------------------------
    > Set adocmd = Server.CreateObject("ADODB.Command")
    > adocmd.CommandTimeout = 120
    > adocmd.ActiveConnection = conn
    > adocmd.CommandType = adCmdStoredProc
    > adocmd.CommandText = "dbo.spr_searchALL"
    >
    > Set rsSearchM = Server.CreateObject ("ADODB.Recordset")
    > rsSearchM.ActiveConnection = conn
    > rsSearchM.CursorLocation = 3


    I'm assuming that's adUseClient (too lazy to look it up). Why not use the
    constant instead of the "3"?

    >
    > ' write to database using spr
    > With adocmd
    >
    > set param = .CreateParameter("@criteria", adVarchar, adParamInput,
    > 50, "red" )
    > .parameters.append param
    >
    > set param = .createparameter("@numrows", adInteger, adParamOutput)
    > .parameters.append param
    >
    > On Error Resume Next
    >
    > errorstring = ""
    > errornumber = 0
    > valid = 0
    >
    > rsSearchM.open = .execute


    That is very strange syntax! It really works? Anyways, the Execute method
    creates a NEW recordset with default properties. I'm not sure why that Open
    method is allowing the recordset returned from Execute to be assigned to
    rsSearchM, but given your results, it certainly seems to be. The correct
    syntax of course is:

    rs.Open adocmd

    >
    > '-- check the return value
    > If Err.Number <> 0 Then
    > errorstring = "<p>Error Number " & Err.Number & "<br>" & "The
    > Error Code was: " & Err.Description & "</p>"
    > errornumber = Err.Number
    > Response.Write(errorstring)
    > Response.Redirect(HomePath & "/error.asp?eid=unknown")
    > End If
    >
    > .execute


    You're executing it twice?? There is no reason to do that. The open
    statement above should provide a client-side static cursor. As long as you
    have a SET NOCOUNT STATEMENT in your stored procedure, and the procedure
    only returns a single recordset, you should be able to read the output
    parameter at this point without calliing .Execute again.

    > numrowsM = .Parameters("@numrows").Value
    >

    Curious. I would not have expected you to be able to get the parameter
    value at this point without consuming the recordset returned by the
    procedure. You've confirmed that you are actually getting a value at this
    point?




    --
    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], Jul 27, 2007
    #2
    1. Advertising

  3. stjulian wrote:
    > (IIS 6.0, SQL Server 2000)
    >
    > I have a block of code that populates a recordset from a stored
    > procedure. The problem is, the recordset seems to be forward only (which
    > would
    > be OK), but can't jump with the "AbsolutePage" property (which isn't OK)
    >

    PS. You should investigate more efficient methods to accomplish
    record-paging. Here are a couple examples:
    http://databases.aspfaq.com/database/how-do-i-page-through-a-recordset.html
    http://www.adopenstatic.com/experiments/recordsetpaging.asp
    --
    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], Jul 27, 2007
    #3
  4. stjulian

    stjulian Guest

    Yup.

    I do a lot of copying and pasting from my old code. Just got used to the 3 I
    guess.

    I have even done a simple

    SET rsSearchM = .execute

    That doesn't give the desired results either.

    There is a SET NOCOUNT ON statement before the SELECT that makes the
    recordset in the SP

    The @numrows comes from

    SET @numrows = @@ROWCOUNT

    and the value is not returned without the second ".execute" stement.

    Any other ideas?

    This should be easier for me. But, thank You for all the help.

    Julian




    "Bob Barrows [MVP]" <> wrote in message
    news:...
    > stjulian wrote:
    >> (IIS 6.0, SQL Server 2000)
    >>
    >> I have a block of code that populates a recordset from a stored
    >> procedure. The problem is, the recordset seems to be forward only (which
    >> would
    >> be OK), but can't jump with the "AbsolutePage" property (which isn't OK)
    >>
    >> How do I define the recordset that will allow this?
    >>
    >> Julian
    >>
    >>
    >> ----------------------------------------------------------------------------------
    >> Set adocmd = Server.CreateObject("ADODB.Command")
    >> adocmd.CommandTimeout = 120
    >> adocmd.ActiveConnection = conn
    >> adocmd.CommandType = adCmdStoredProc
    >> adocmd.CommandText = "dbo.spr_searchALL"
    >>
    >> Set rsSearchM = Server.CreateObject ("ADODB.Recordset")
    >> rsSearchM.ActiveConnection = conn
    >> rsSearchM.CursorLocation = 3

    >
    > I'm assuming that's adUseClient (too lazy to look it up). Why not use the
    > constant instead of the "3"?
    >
    >>
    >> ' write to database using spr
    >> With adocmd
    >>
    >> set param = .CreateParameter("@criteria", adVarchar, adParamInput,
    >> 50, "red" )
    >> .parameters.append param
    >>
    >> set param = .createparameter("@numrows", adInteger, adParamOutput)
    >> .parameters.append param
    >>
    >> On Error Resume Next
    >>
    >> errorstring = ""
    >> errornumber = 0
    >> valid = 0
    >>
    >> rsSearchM.open = .execute

    >
    > That is very strange syntax! It really works? Anyways, the Execute method
    > creates a NEW recordset with default properties. I'm not sure why that
    > Open method is allowing the recordset returned from Execute to be assigned
    > to rsSearchM, but given your results, it certainly seems to be. The
    > correct syntax of course is:
    >
    > rs.Open adocmd
    >
    >>
    >> '-- check the return value
    >> If Err.Number <> 0 Then
    >> errorstring = "<p>Error Number " & Err.Number & "<br>" & "The
    >> Error Code was: " & Err.Description & "</p>"
    >> errornumber = Err.Number
    >> Response.Write(errorstring)
    >> Response.Redirect(HomePath & "/error.asp?eid=unknown")
    >> End If
    >>
    >> .execute

    >
    > You're executing it twice?? There is no reason to do that. The open
    > statement above should provide a client-side static cursor. As long as you
    > have a SET NOCOUNT STATEMENT in your stored procedure, and the procedure
    > only returns a single recordset, you should be able to read the output
    > parameter at this point without calliing .Execute again.
    >
    >> numrowsM = .Parameters("@numrows").Value
    >>

    > Curious. I would not have expected you to be able to get the parameter
    > value at this point without consuming the recordset returned by the
    > procedure. You've confirmed that you are actually getting a value at this
    > point?
    >
    >
    >
    >
    > --
    > 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"
    >
     
    stjulian, Jul 27, 2007
    #4
  5. stjulian wrote:
    > Yup.
    >
    > I do a lot of copying and pasting from my old code. Just got used to
    > the 3 I guess.
    >
    > I have even done a simple
    >
    > SET rsSearchM = .execute
    >
    > That doesn't give the desired results either.


    It shouldn't. Did you read my initial reply carefully? Execute always
    returns a server-side forwardonly recordset (unless you have earlier
    modified the cursorlocation property of the Connection object to make
    adUseClient the default cursor location)

    SQL Server will not return the output parameter value until all recordsets
    returned by the procedure have been consumed. With a server-side cursor,
    that means you need to move to the last record in the recordset, or close
    it, before attempting to read the output parameter value. With a client-side
    cursor, the entire resultset is sent to the client (ADO) and put into a
    client-side cursor, so the output parameter value should be available
    immediately after the recordset.open statement.

    >
    > There is a SET NOCOUNT ON statement before the SELECT that makes the
    > recordset in the SP


    Actually, SET NOCOUNT ON should be the first statement in the procedure, but
    it shouldn't matter where it is in the procedure.
    >
    > The @numrows comes from
    >
    > SET @numrows = @@ROWCOUNT
    >
    > and the value is not returned without the second ".execute" stement.


    I'm amazed that it is returned. When you execute the procedure again, it
    does all the actions that it performed the first time, including sending the
    resultset again. Hmm, perhaps ADO consumes those results when you don't
    assign them to a recordset variable ...
    >
    > Any other ideas?


    I'm still unclear as to what I am trying to fix. Did you revise your code
    per my initial reply? Specifically, eliminate both Execute statements and
    use

    rs.Open adocmd

    If so, show me your new code as well as the text of the stored procedure.

    --
    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], Jul 27, 2007
    #5
    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. Maziar Aflatoun

    Returning a varchar from a stored procedure

    Maziar Aflatoun, Apr 22, 2005, in forum: ASP .Net
    Replies:
    4
    Views:
    14,746
    Maziar Aflatoun
    Apr 22, 2005
  2. Joe Van Meer
    Replies:
    1
    Views:
    1,240
    Joe Van Meer
    Feb 1, 2006
  3. Mike P
    Replies:
    0
    Views:
    3,334
    Mike P
    Jun 19, 2006
  4. stjulian
    Replies:
    1
    Views:
    194
    Bob Barrows [MVP]
    Nov 1, 2006
  5. Dooza
    Replies:
    3
    Views:
    201
    Dooza
    Jun 5, 2008
Loading...

Share This Page