returning values from stored proc error!

[Harag]

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 = msExecutedSQL
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: " & msExecutedSQL
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]

What's in msExecutedSQL? 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 = msExecutedSQL
> 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: " & msExecutedSQL
> 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]

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=xxxxxxx;" & _
"Initial Catalog=test;user id=xxxxxx;password=xxxxxxxx"
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 = msExecutedSQL
> 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: " & msExecutedSQL
> 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]

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

it now just has

msExecutedSQL = "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@yahoo.com> wrote:

>What's in msExecutedSQL? 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 = msExecutedSQL
>> 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: " & msExecutedSQL
>> 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]

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@yahoo.com> wrote:

>What's in msExecutedSQL? 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 = msExecutedSQL
>> 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: " & msExecutedSQL
>> 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]

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@yahoo.com> 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=xxxxxxx;" & _
>"Initial Catalog=test;user id=xxxxxx;password=xxxxxxxx"
> 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 = msExecutedSQL
>> 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: " & msExecutedSQL
>> 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

>
>