Professional Web Applications Themes

Help - stored procedure not returning a recordset - ASP Database

I have an ASP page that calls a SQL Server stored proc that should return multiple recordsets, but it appears to be returning something else, or it is missing properties and methods of a recordset. ASP code: Set cmdGetPOData = Server.CreateObject("ADODB.Command") Set rsPOData = Server.CreateObject("ADODB.Recordset") With cmdGetPOData .ActiveConnection = Cnxn .CommandType = adCmdStoredProc .CommandText = "uspGetPOData" .Parameters.Append .CreateParameter("RETURN_VALUE", adInteger, adParamReturnValue) .Parameters.Append .CreateParameter("refNum", adVarChar, adParamInput, 15) .Parameters("refNum") = strRefNum rsPOData = .Execute End With ------------------------------------------------------ SQL code: select d.*, a.*, v.* from tblPODoent d left join tblAddressCodes a on d.sShipAddrCode = a.sAddressID left join tblAPVendor v on d.sPlayerID = v.sVendorID where ...

  1. #1

    Default Help - stored procedure not returning a recordset

    I have an ASP page that calls a SQL Server stored proc
    that should return multiple recordsets, but it appears
    to be returning something else, or it is missing
    properties and methods of a recordset.


    ASP code:

    Set cmdGetPOData = Server.CreateObject("ADODB.Command")
    Set rsPOData = Server.CreateObject("ADODB.Recordset")

    With cmdGetPOData
    .ActiveConnection = Cnxn
    .CommandType = adCmdStoredProc
    .CommandText = "uspGetPOData"

    .Parameters.Append .CreateParameter("RETURN_VALUE",
    adInteger, adParamReturnValue)
    .Parameters.Append .CreateParameter("refNum",
    adVarChar, adParamInput, 15)
    .Parameters("refNum") = strRefNum

    rsPOData = .Execute
    End With

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

    SQL code:

    select d.*, a.*, v.*
    from tblPODoent d
    left join tblAddressCodes a on d.sShipAddrCode = a.sAddressID
    left join tblAPVendor v on d.sPlayerID = v.sVendorID
    where sReferenceNum = refNum

    select count(*) as NumDetailItems
    from tblPODoent d
    join tblPOTrans t on d.dDocID = t.dDocID
    where sReferenceNum = refNum

    select t.*
    from tblPODoent d
    join tblPOTrans t on d.dDocID = t.dDocID
    where sReferenceNum = refNum

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

    When the stored proc executes, I do get data back, but it
    does not appear to be a record or recordset. The database
    fieds are there, but it is missing the following properties
    and methods (plus others)

    rsPOData.EOF
    rsPOData.State
    rsPOData.Fields
    rsPOData.NextRecordset
    rsPOData.RecordCount


    The first select in the proc will only return 1 record. I
    don't need the 2nd select - *IF* rsPOData.RecordCount starts
    to work as it appears it should...

    If rsPOData is not a recordset, what would it be?
    Is there anything obvious that I am doing wrong?

    tia,
    Bill
    Bill S. Guest

  2. #2

    Default Re: Help - stored procedure not returning a recordset

    Bill S. wrote:
    > SQL code:
    >
    Start the procedure with:
    SET NOCOUNT ON

    This prevents the "x number of records affected" informational messages from
    being returned as separate resultsets.
    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] Guest

  3. #3

    Default Re: Help - stored procedure not returning a recordset

    Remember to SET NOCOUNT OFF at the end of the proc too

    "Bob Barrows [MVP]" <reb01501NOyahoo.SPAMcom> wrote in message news:<eJ5oV8O8DHA.3052TK2MSFTNGP09.phx.gbl>...
    > Bill S. wrote:
    > > SQL code:
    > >
    > Start the procedure with:
    > SET NOCOUNT ON
    >
    > This prevents the "x number of records affected" informational messages from
    > being returned as separate resultsets.
    > Bob Barrows
    Samuel Hon Guest

  4. #4

    Default Re: Help - stored procedure not returning a recordset

    Samuel Hon wrote:
    > Remember to SET NOCOUNT OFF at the end of the proc too
    >
    It's not a bad idea to do so, but it is not necessary. The SET NOCOUNT
    statement is only in effect during the process that invokes it, so the
    setting will revert to the original setting when the stored procedure
    completes anyways. Having said that, I tend to go along with the maxim of:
    if I turn it on, I turn it off, so I usually set nocount back to off at the
    end of my procedures. However, I'm not religious about it.

    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 Guest

  5. #5

    Default Re: Help - stored procedure not returning a recordset

    > Remember to SET NOCOUNT OFF at the end of the proc too

    Why? The setting only lasts for the scope of the proc (or the proc that
    called it).

    I always SET NOCOUNT ON and out of n stored procedures I have, not one
    includes SET NOCOUNT OFF. Seems silly to enable it unless you are debugging
    in query yzer and can't be bothered with ROWCOUNT.

    --
    Aaron Bertrand
    SQL Server MVP
    [url]http://www.aspfaq.com/[/url]


    Aaron Bertrand - MVP Guest

  6. #6

    Default Re: Help - stored procedure not returning a recordset

    Think its just the country bumpkin in me, open a gate and close it...

    :D

    "Aaron Bertrand - MVP" <aaronTRASHaspfaq.com> wrote in message news:<uKoOQhX8DHA.2952TK2MSFTNGP09.phx.gbl>...
    > > Remember to SET NOCOUNT OFF at the end of the proc too
    >
    > Why? The setting only lasts for the scope of the proc (or the proc that
    > called it).
    >
    > I always SET NOCOUNT ON and out of n stored procedures I have, not one
    > includes SET NOCOUNT OFF. Seems silly to enable it unless you are debugging
    > in query yzer and can't be bothered with ROWCOUNT.
    Samuel Hon Guest

Similar Threads

  1. Suppressing second recordset from stored procedure
    By A_X_L_V in forum ASP Database
    Replies: 5
    Last Post: November 6th, 09:54 PM
  2. Stored Procedure not returning values.
    By Bob Barrows in forum ASP
    Replies: 2
    Last Post: August 14th, 12:31 PM
  3. Stored Procedure vs. SQL Through Recordset.
    By craig v in forum Macromedia Dreamweaver
    Replies: 1
    Last Post: July 10th, 03:15 PM
  4. Stored Procedure Not Returning
    By Amanda Osment in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 9th, 02:07 PM
  5. Help with Stored Procedure returning recordset
    By Ivar in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 2nd, 04:31 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139