Professional Web Applications Themes

returning values from stored proc error! - ASP Database

Hi all Win 2k pro sql 2k dev ed asp - vbscript I have the following code (below) which is returning an error... if I change the commandtype to adcmdUnknown or adcmdtext then it works ok except for the fact I dont get a return value at all which is not what I want. I want a recordset and a return value returned. the error is: "syntax error or access violation" on the set rs= cmd.execute () line 11 can anyone help pls? thanks Al 1 set cmd = server.CreateObject("ADODB.Command") 2 cmd.activeconnection = moConnection 3 cmd.CommandText = mecutedSQL 4 5 ...

  1. #1

    Default returning values from stored proc error!

    Hi all

    Win 2k pro
    sql 2k dev ed
    asp - vbscript


    I have the following code (below) which is returning an error... if I
    change the commandtype to adcmdUnknown or adcmdtext then it works ok
    except for the fact I dont get a return value at all which is not what
    I want. I want a recordset and a return value returned.

    the error is:
    "syntax error or access violation" on the set rs= cmd.execute () line
    11

    can anyone help pls?

    thanks
    Al


    1 set cmd = server.CreateObject("ADODB.Command")
    2 cmd.activeconnection = moConnection
    3 cmd.CommandText = mecutedSQL
    4
    5 cmd.commandtype = adCmdStoredProc
    6
    7 cmd.parameters.append cmd.createparameter ("RETURN_VALUE",
    adInteger, adParamReturnValue)
    8 cmd.parameters.append cmd.createparameter ("name", adVarChar,
    adParamInput, 100)
    9
    10 cmd.parameters("name") = "doc green"
    11 set rs= cmd.execute ()
    12 out "sSQL: " & mecutedSQL
    13out "<P>COMMAND TEXT OUT: #" & cmd.parameters("RETURN_VALUE") & "#"

    Stored Proc:
    CREATE PROCEDURE dbo.usp_TEST_retval
    name varchar(100)= 'Doc Green'
    AS
    SET NOCOUNT ON
    select * from test where username = name
    --RAISERROR ('return value raiseerror!',10,1)
    RETURN 999
    Harag Guest

  2. #2

    Default Re: returning values from stored proc error!

    What's in mecutedSQL? It should be just the name of the stored procedure:
    nothing else.

    Bob Barrows
    Harag wrote:
    > Hi all
    >
    > Win 2k pro
    > sql 2k dev ed
    > asp - vbscript
    >
    >
    > I have the following code (below) which is returning an error... if I
    > change the commandtype to adcmdUnknown or adcmdtext then it works ok
    > except for the fact I dont get a return value at all which is not what
    > I want. I want a recordset and a return value returned.
    >
    > the error is:
    > "syntax error or access violation" on the set rs= cmd.execute () line
    > 11
    >
    > can anyone help pls?
    >
    > thanks
    > Al
    >
    >
    > 1 set cmd = server.CreateObject("ADODB.Command")
    > 2 cmd.activeconnection = moConnection
    > 3 cmd.CommandText = mecutedSQL
    > 4
    > 5 cmd.commandtype = adCmdStoredProc
    > 6
    > 7 cmd.parameters.append cmd.createparameter ("RETURN_VALUE",
    > adInteger, adParamReturnValue)
    > 8 cmd.parameters.append cmd.createparameter ("name", adVarChar,
    > adParamInput, 100)
    > 9
    > 10 cmd.parameters("name") = "doc green"
    > 11 set rs= cmd.execute ()
    > 12 out "sSQL: " & mecutedSQL
    > 13out "<P>COMMAND TEXT OUT: #" & cmd.parameters("RETURN_VALUE") & "#"
    >
    > Stored Proc:
    > CREATE PROCEDURE dbo.usp_TEST_retval
    > name varchar(100)= 'Doc Green'
    > AS
    > SET NOCOUNT ON
    > select * from test where username = name
    > --RAISERROR ('return value raiseerror!',10,1)
    > RETURN 999


    Bob Barrows Guest

  3. #3

    Default Re: returning values from stored proc error!

    The other thing you need to realize is that the return parameter will not be
    available until the last record in your recordset is accessed. This means,
    with a default server-side recordset, that the recordset must be closed. You
    could use a client-side recordset, but I don't recommend that unless you
    need other functionality provided by a client-side recordset, such as
    scrollability.

    Instead, use either getrows or getstring to stuff the data from your
    recordset into a local variable, and then close the recordset. This code
    illustrates using a getrows array:

    dim moConnection, rs, cmd, ar, i, j
    set moConnection=server.CreateObject("adodb.connection ")
    moConnection.Open "provider=sqloledb;data source=x;" & _
    "Initial Catalog=test;user id=;password=xx"
    set cmd = server.CreateObject("ADODB.Command")
    cmd.activeconnection = moConnection
    cmd.CommandText = "usp_TEST_retval"

    cmd.commandtype = adCmdStoredProc

    cmd.parameters.append cmd.createparameter ("RETURN_VALUE", _
    adInteger, adParamReturnValue)
    cmd.parameters.append cmd.createparameter ("name", _
    adVarChar,adParamInput, 100,"doc green")

    set rs= cmd.execute ()
    if not rs.eof then ar = rs.getrows
    rs.close
    set rs=nothing
    for i =0 to ubound(ar,2)
    for j = 0 to UBound(ar)
    Response.Write ar(j,i) & "; "
    next
    Response.Write "<BR>"
    next
    Response.Write "<P>COMMAND TEXT OUT: #" & _
    cmd.parameters("RETURN_VALUE") & "#"

    HTH,
    Bob Barrows

    Harag wrote:
    > Hi all
    >
    > Win 2k pro
    > sql 2k dev ed
    > asp - vbscript
    >
    >
    > I have the following code (below) which is returning an error... if I
    > change the commandtype to adcmdUnknown or adcmdtext then it works ok
    > except for the fact I dont get a return value at all which is not what
    > I want. I want a recordset and a return value returned.
    >
    > the error is:
    > "syntax error or access violation" on the set rs= cmd.execute () line
    > 11
    >
    > can anyone help pls?
    >
    > thanks
    > Al
    >
    >
    > 1 set cmd = server.CreateObject("ADODB.Command")
    > 2 cmd.activeconnection = moConnection
    > 3 cmd.CommandText = mecutedSQL
    > 4
    > 5 cmd.commandtype = adCmdStoredProc
    > 6
    > 7 cmd.parameters.append cmd.createparameter ("RETURN_VALUE",
    > adInteger, adParamReturnValue)
    > 8 cmd.parameters.append cmd.createparameter ("name", adVarChar,
    > adParamInput, 100)
    > 9
    > 10 cmd.parameters("name") = "doc green"
    > 11 set rs= cmd.execute ()
    > 12 out "sSQL: " & mecutedSQL
    > 13out "<P>COMMAND TEXT OUT: #" & cmd.parameters("RETURN_VALUE") & "#"
    >
    > Stored Proc:
    > CREATE PROCEDURE dbo.usp_TEST_retval
    > name varchar(100)= 'Doc Green'
    > AS
    > SET NOCOUNT ON
    > select * from test where username = name
    > --RAISERROR ('return value raiseerror!',10,1)
    > RETURN 999


    Bob Barrows Guest

  4. #4

    Default Re: returning values from stored proc error!

    thanks bob.. the SQL had '' where i removed the name from an earlier
    test

    it now just has

    mecutedSQL = "usp_TEST_retval"

    and it works ok... EXCEPT: I'm still not getting the return value of
    999 from the stored proc

    result output is:

    sSQL: usp_TEST_retval
    COMMAND TEXT OUT: ##

    should be:
    sSQL: usp_TEST_retval
    COMMAND TEXT OUT: #999#

    before I added the "Name" parameter and just returned the value it
    returned 999 fine its since I added the name thats its not returned.

    al






    On Sat, 13 Sep 2003 10:17:17 -0400, "Bob Barrows"
    <reb_01501> wrote:
    >What's in mecutedSQL? It should be just the name of the stored procedure:
    >nothing else.
    >
    >Bob Barrows
    >Harag wrote:
    >> Hi all
    >>
    >> Win 2k pro
    >> sql 2k dev ed
    >> asp - vbscript
    >>
    >>
    >> I have the following code (below) which is returning an error... if I
    >> change the commandtype to adcmdUnknown or adcmdtext then it works ok
    >> except for the fact I dont get a return value at all which is not what
    >> I want. I want a recordset and a return value returned.
    >>
    >> the error is:
    >> "syntax error or access violation" on the set rs= cmd.execute () line
    >> 11
    >>
    >> can anyone help pls?
    >>
    >> thanks
    >> Al
    >>
    >>
    >> 1 set cmd = server.CreateObject("ADODB.Command")
    >> 2 cmd.activeconnection = moConnection
    >> 3 cmd.CommandText = mecutedSQL
    >> 4
    >> 5 cmd.commandtype = adCmdStoredProc
    >> 6
    >> 7 cmd.parameters.append cmd.createparameter ("RETURN_VALUE",
    >> adInteger, adParamReturnValue)
    >> 8 cmd.parameters.append cmd.createparameter ("name", adVarChar,
    >> adParamInput, 100)
    >> 9
    >> 10 cmd.parameters("name") = "doc green"
    >> 11 set rs= cmd.execute ()
    >> 12 out "sSQL: " & mecutedSQL
    >> 13out "<P>COMMAND TEXT OUT: #" & cmd.parameters("RETURN_VALUE") & "#"
    >>
    >> Stored Proc:
    >> CREATE PROCEDURE dbo.usp_TEST_retval
    >> name varchar(100)= 'Doc Green'
    >> AS
    >> SET NOCOUNT ON
    >> select * from test where username = name
    >> --RAISERROR ('return value raiseerror!',10,1)
    >> RETURN 999
    >
    >
    Harag Guest

  5. #5

    Default Re: returning values from stored proc error!


    just done a quick test.

    if I change the Name para to an invalid name eg "doc greens" then the
    row is not found and it returns the 999 return value.

    but if it finds a row with the name then the return value is NOT
    returned.

    how do i return BOTH the rows AND the return value?

    thanks again.
    AL


    On Sat, 13 Sep 2003 10:17:17 -0400, "Bob Barrows"
    <reb_01501> wrote:
    >What's in mecutedSQL? It should be just the name of the stored procedure:
    >nothing else.
    >
    >Bob Barrows
    >Harag wrote:
    >> Hi all
    >>
    >> Win 2k pro
    >> sql 2k dev ed
    >> asp - vbscript
    >>
    >>
    >> I have the following code (below) which is returning an error... if I
    >> change the commandtype to adcmdUnknown or adcmdtext then it works ok
    >> except for the fact I dont get a return value at all which is not what
    >> I want. I want a recordset and a return value returned.
    >>
    >> the error is:
    >> "syntax error or access violation" on the set rs= cmd.execute () line
    >> 11
    >>
    >> can anyone help pls?
    >>
    >> thanks
    >> Al
    >>
    >>
    >> 1 set cmd = server.CreateObject("ADODB.Command")
    >> 2 cmd.activeconnection = moConnection
    >> 3 cmd.CommandText = mecutedSQL
    >> 4
    >> 5 cmd.commandtype = adCmdStoredProc
    >> 6
    >> 7 cmd.parameters.append cmd.createparameter ("RETURN_VALUE",
    >> adInteger, adParamReturnValue)
    >> 8 cmd.parameters.append cmd.createparameter ("name", adVarChar,
    >> adParamInput, 100)
    >> 9
    >> 10 cmd.parameters("name") = "doc green"
    >> 11 set rs= cmd.execute ()
    >> 12 out "sSQL: " & mecutedSQL
    >> 13out "<P>COMMAND TEXT OUT: #" & cmd.parameters("RETURN_VALUE") & "#"
    >>
    >> Stored Proc:
    >> CREATE PROCEDURE dbo.usp_TEST_retval
    >> name varchar(100)= 'Doc Green'
    >> AS
    >> SET NOCOUNT ON
    >> select * from test where username = name
    >> --RAISERROR ('return value raiseerror!',10,1)
    >> RETURN 999
    >
    >
    Harag Guest

  6. #6

    Default Re: returning values from stored proc error!

    Ahh just got this msg after posting my previous 2
    (I use a offline reader)

    thanks for the info Bob.

    Al.

    On Sat, 13 Sep 2003 10:36:32 -0400, "Bob Barrows"
    <reb_01501> wrote:
    >The other thing you need to realize is that the return parameter will not be
    >available until the last record in your recordset is accessed. This means,
    >with a default server-side recordset, that the recordset must be closed. You
    >could use a client-side recordset, but I don't recommend that unless you
    >need other functionality provided by a client-side recordset, such as
    >scrollability.
    >
    >Instead, use either getrows or getstring to stuff the data from your
    >recordset into a local variable, and then close the recordset. This code
    >illustrates using a getrows array:
    >
    >dim moConnection, rs, cmd, ar, i, j
    >set moConnection=server.CreateObject("adodb.connection ")
    >moConnection.Open "provider=sqloledb;data source=x;" & _
    >"Initial Catalog=test;user id=;password=xx"
    > set cmd = server.CreateObject("ADODB.Command")
    > cmd.activeconnection = moConnection
    > cmd.CommandText = "usp_TEST_retval"
    >
    > cmd.commandtype = adCmdStoredProc
    >
    > cmd.parameters.append cmd.createparameter ("RETURN_VALUE", _
    > adInteger, adParamReturnValue)
    > cmd.parameters.append cmd.createparameter ("name", _
    > adVarChar,adParamInput, 100,"doc green")
    >
    > set rs= cmd.execute ()
    > if not rs.eof then ar = rs.getrows
    >rs.close
    >set rs=nothing
    >for i =0 to ubound(ar,2)
    > for j = 0 to UBound(ar)
    > Response.Write ar(j,i) & "; "
    > next
    > Response.Write "<BR>"
    >next
    >Response.Write "<P>COMMAND TEXT OUT: #" & _
    >cmd.parameters("RETURN_VALUE") & "#"
    >
    >HTH,
    >Bob Barrows
    >
    >Harag wrote:
    >> Hi all
    >>
    >> Win 2k pro
    >> sql 2k dev ed
    >> asp - vbscript
    >>
    >>
    >> I have the following code (below) which is returning an error... if I
    >> change the commandtype to adcmdUnknown or adcmdtext then it works ok
    >> except for the fact I dont get a return value at all which is not what
    >> I want. I want a recordset and a return value returned.
    >>
    >> the error is:
    >> "syntax error or access violation" on the set rs= cmd.execute () line
    >> 11
    >>
    >> can anyone help pls?
    >>
    >> thanks
    >> Al
    >>
    >>
    >> 1 set cmd = server.CreateObject("ADODB.Command")
    >> 2 cmd.activeconnection = moConnection
    >> 3 cmd.CommandText = mecutedSQL
    >> 4
    >> 5 cmd.commandtype = adCmdStoredProc
    >> 6
    >> 7 cmd.parameters.append cmd.createparameter ("RETURN_VALUE",
    >> adInteger, adParamReturnValue)
    >> 8 cmd.parameters.append cmd.createparameter ("name", adVarChar,
    >> adParamInput, 100)
    >> 9
    >> 10 cmd.parameters("name") = "doc green"
    >> 11 set rs= cmd.execute ()
    >> 12 out "sSQL: " & mecutedSQL
    >> 13out "<P>COMMAND TEXT OUT: #" & cmd.parameters("RETURN_VALUE") & "#"
    >>
    >> Stored Proc:
    >> CREATE PROCEDURE dbo.usp_TEST_retval
    >> name varchar(100)= 'Doc Green'
    >> AS
    >> SET NOCOUNT ON
    >> select * from test where username = name
    >> --RAISERROR ('return value raiseerror!',10,1)
    >> RETURN 999
    >
    >
    Harag Guest

Similar Threads

  1. Error returning CFHTTP values
    By samb1 in forum Coldfusion - Advanced Techniques
    Replies: 0
    Last Post: March 3rd, 09:44 PM
  2. Calling webservice from within a stored proc 407 error...
    By Doug in forum ASP.NET Web Services
    Replies: 0
    Last Post: September 15th, 09:18 PM
  3. returning error code form Stored Procedure
    By Bob Bakhtiari in forum ASP Database
    Replies: 11
    Last Post: December 17th, 10:00 PM
  4. EXEC in TSQL stored proc causes ERROR
    By don schilling in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: September 29th, 09:06 PM
  5. Stored Procedure not returning values.
    By Bob Barrows in forum ASP
    Replies: 2
    Last Post: August 14th, 12: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