Professional Web Applications Themes

Stored Procedured doesn't return recordset in asp - ASP Database

Hi, I created simple Stoed Procedure: CREATE PROC my_proc id INT, name VARCHAR(50) AS INSERT INTO names (Id, Name) VALUES (id, name) SELECT IDENTITY AS lastid Then I run it with simple code: EXEC my_proc 24, 'KSSWD' Now, when I run it using OSQL everything is OK, it returns one record lastid equals 24 But when I run it form asp with code: sql = "EXEC my_proc 24, 'KSSWD' " set rs = conn.Execute(sql) last_id = rs("lastid") .................................... or sql = "EXEC my_proc 24, 'KSSWD' " set rs = Server.CreateObject("ADODB.Recordset") rs.open sql, conn, 1, 1 last_id = rs("lastid") ........................... I ...

  1. #1

    Default Stored Procedured doesn't return recordset in asp

    Hi,
    I created simple Stoed Procedure:

    CREATE PROC my_proc id INT, name VARCHAR(50)
    AS
    INSERT INTO names (Id, Name) VALUES (id, name)
    SELECT IDENTITY AS lastid

    Then I run it with simple code:

    EXEC my_proc 24, 'KSSWD'

    Now, when I run it using OSQL everything is OK, it returns one record lastid
    equals 24
    But when I run it form asp with code:

    sql = "EXEC my_proc 24, 'KSSWD' "
    set rs = conn.Execute(sql)
    last_id = rs("lastid")
    ....................................

    or

    sql = "EXEC my_proc 24, 'KSSWD' "
    set rs = Server.CreateObject("ADODB.Recordset")
    rs.open sql, conn, 1, 1
    last_id = rs("lastid")
    ...........................

    I got the error : ADODB Recordset, operation is not allowed when the object
    (recordset) is closed .
    The same happens regardless of the last stored procedure statement (select *
    from names for example).
    Why is that ? What's wrong?
    Regards Piotr


    piotr Guest

  2. #2

    Default Re: Stored Procedured doesn't return recordset in asp

    Try adding

    SET NOCOUNT ON to the sp.


    --
    Roji. P. Thomas
    Net Asset Management
    https://www.netassetmanagement.com


    "piotr" <fm> wrote in message
    news:phx.gbl... 


    Roji. Guest

  3. #3

    Default Re: Stored Procedured doesn't return recordset in asp

    That was it! Thanks a lot.

    Piotr

    Użytkownik "Roji. P. Thomas" <com> napisał w wiadomości
    news:phx.gbl... [/ref]
    (select 
    >
    >[/ref]


    piotr Guest

Similar Threads

  1. What Should You Do If You Want to Return a Recordset?
    By Geoff Robinson in forum ASP.NET Web Services
    Replies: 4
    Last Post: May 17th, 08:06 AM
  2. Recordset doesn't return identity field
    By A_X_L_V in forum ASP Components
    Replies: 7
    Last Post: October 21st, 02:48 PM
  3. return recordset value to textbox
    By Microsoft in forum Microsoft Access
    Replies: 0
    Last Post: July 21st, 05:21 PM
  4. How do I return recordset via webservice?
    By Simon Smith in forum ASP.NET Web Services
    Replies: 2
    Last Post: July 18th, 06:20 PM
  5. Stored Procedure vs. SQL Through Recordset.
    By craig v in forum Macromedia Dreamweaver
    Replies: 1
    Last Post: July 10th, 03:15 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