Professional Web Applications Themes

stored proc RETURN value in ASP - ASP Database

I have a stored proc which checks for errors, and returns the error value as a return code/value. how can i 'read' this from my asp code? Should I return it as a recordset instead? Here's how it currently is:- SP snippet: SET Err = ERROR RETURN Err ASP: Set conn = Server.CreateObject("ADODB.Connection") conn.Open MM_risknet_STRING sql = "EXEC proc var=" & var conn.Execute sql,,129...

  1. #1

    Default stored proc RETURN value in ASP

    I have a stored proc which checks for errors, and returns the error value as
    a return code/value. how can i 'read' this from my asp code? Should I return
    it as a recordset instead?

    Here's how it currently is:-
    SP snippet:
    SET Err = ERROR
    RETURN Err

    ASP:
    Set conn = Server.CreateObject("ADODB.Connection")
    conn.Open MM_risknet_STRING
    sql = "EXEC proc var=" & var
    conn.Execute sql,,129




    ben h Guest

  2. #2

    Default Re: stored proc RETURN value in ASP

    ben h wrote:
    > I have a stored proc which checks for errors, and returns the error
    > value as a return code/value. how can i 'read' this from my asp code?
    > Should I return it as a recordset instead?
    >
    > Here's how it currently is:-
    > SP snippet:
    > SET Err = ERROR
    > RETURN Err
    >
    > ASP:
    > Set conn = Server.CreateObject("ADODB.Connection")
    > conn.Open MM_risknet_STRING
    > sql = "EXEC proc var=" & var
    > conn.Execute sql,,129
    You need to use an explicit Command object to retrieve this value (as well
    as the values that would be returned if you used output
    parameters.).Unfortunately, the most efficient way to utilize a Command
    object's Parameters collection is also the trickiest to write and debug,
    which has led to the creation of command object code generators such as the
    one that i wrote that is available here:
    [url]http://www.thrasherwebdesign.com/index.asp?pi=links&hp=links.asp&c=&a=clear[/url]

    The asp page will generate the code to create and append the Parameter
    objects to the Command object's Parameters collection. It assumes a
    previously opened connection object (the source code is of course included
    so you can change the name I used for the connection and command object
    variables if you wish). After the Execute statement, you would read the
    return value by :

    returnvalue = cmd.Parameters("RETURN_VALUE").value

    The generator also assumes that you have included the definitions of all the
    ADO constants, either by including the adovbs.inc file, or by using the
    METADATA tag to reference the ADO type library as described here:


    HTH,
    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] Guest

  3. #3

    Default Re: stored proc RETURN value in ASP

    Oops - accidently hit the Send button ...
    ben h wrote:
    > I have a stored proc which checks for errors, and returns the error
    > value as a return code/value. how can i 'read' this from my asp code?
    > Should I return it as a recordset instead?
    >
    > Here's how it currently is:-
    > SP snippet:
    > SET Err = ERROR
    > RETURN Err
    >
    > ASP:
    > Set conn = Server.CreateObject("ADODB.Connection")
    > conn.Open MM_risknet_STRING
    > sql = "EXEC proc var=" & var
    > conn.Execute sql,,129
    You need to use an explicit Command object to retrieve this value (as well
    as the values that would be returned if you used output
    parameters). Unfortunately, the most efficient way to utilize a Command
    object's Parameters collection is also the trickiest to write and debug,
    which has led to the creation of command object code generators such as the
    one that i wrote that is available here:
    [url]http://www.thrasherwebdesign.com/index.asp?pi=links&hp=links.asp&c=&a=clear[/url]

    The asp page will generate the code to create and append the Parameter
    objects to the Command object's Parameters collection. It assumes a
    previously opened connection object (the source code is of course included
    so you can change the name I used for the connection and command object
    variables if you wish). After the Execute statement, you would read the
    return value by :

    returnvalue = cmd.Parameters("RETURN_VALUE").value

    The generator also assumes that you have included the definitions of all the
    ADO constants, either by #including the adovbs.inc file, or by using the
    METADATA tag to reference the ADO type library as described here:

    [url]http://www.aspfaq.com/show.asp?id=2112[/url]

    HTH,
    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] Guest

Similar Threads

  1. CF5 OLEDB & Stored Proc
    By solutionfinder in forum Coldfusion Database Access
    Replies: 9
    Last Post: March 31st, 11:46 PM
  2. cfc and stored proc
    By mcoop in forum Coldfusion - Advanced Techniques
    Replies: 1
    Last Post: April 13th, 05:09 PM
  3. ASP vs Stored Proc vs UDF
    By Brad in forum ASP Database
    Replies: 11
    Last Post: October 28th, 01:46 AM
  4. Nesting IF's in Stored Proc
    By Gary in forum Microsoft SQL / MS SQL Server
    Replies: 4
    Last Post: July 7th, 04:25 AM
  5. stored proc and tcp/ip
    By Helmut Wöss in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: June 30th, 08:13 AM

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