Returning Stored Proc ReturnValue in ADO

pg.242w_at_gmail.com
Date: 01/14/05 

Date: 14 Jan 2005 09:00:54 -0800

I am trying to return a ReturnValue from a SQL Server 2000 stored proc
from an ASP page using the ADO Command and Parameter objects.

I have a stored proc that accepts 3 parameters and its basically doing
a select statement based on these input parameters...

At the end of the proc, i am returning the rowcount...@@RowCount to the
ASP page.

ALTER PROCEDURE S_ClientsReturn_Client
( @chrRegisteredStatus varchar(01)= 'A' ,
@inyClientOwner TinyInt = NULL,
@chvSearchValue varchar(77) = NULL,
@chvSearchType Varchar(08) = NULL,
@inyTypeID TinyInt = 0)

AS
DECLARE @intRows INT
.........
Inside the proc,

I have a few conditions that will do select statements...

select XX,
XXXX
from YYYY

SET @intRows = @@RowCount

At the end...
RETURN @intRows

The procedure only returns one set of select results...

On the ASP side:
Dim m_oConn, m_oRS, m_lRecs, m_iIndex, m_oCmd, m_oParam
Const MAX_RECS = 10

Set m_oConn = Server.CreateObject("ADODB.Connection")

With m_oConn
.ConnectionString = DSN
.CursorLocation = adUseClient
.Open
End With 'm_oConn

Set m_oCmd = Server.CreateObject("ADODB.Command")
With m_oCmd
.ActiveConnection = m_oConn
.CommandText = "S_ClientsReturn_Client"
.CommandType = adCmdStoredProc
.NamedParameters = True

' Create and add 1st param to command
Set m_oParam = .CreateParameter
With m_oParam
.Direction = adParamInput
.Name = "@chrRegisteredStatus"
.Size = 1
.Type = adChar
.Value = "R"
End With 'm_oParam
.Parameters.Append m_oParam

' Create and add 2nd param to command
Set m_oParam = .CreateParameter
With m_oParam
.Direction = adParamInput
.Name = "@inyClientOwner"
.Type = adTinyInt
.Value = 1
End With 'm_oParam
.Parameters.Append m_oParam

' Create and add param for ReturnValue to command
Set m_oParam = .CreateParameter
With m_oParam
.Direction = adParamReturnValue
.Name = "@intRows"
.Type = adInteger
End With 'm_oParam
.Parameters.Append m_oParam

Set m_oRS = .Execute
' Display number of rows
Response.Write("<BR>Number Of Rows: " & .Parameters("@intRows").Value
& "<BR>")
End With 'm_oCmd

The preceding code returns the following error:

Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E14)
@intRows is not a parameter for procedure S_ClientsReturn_Client.
How can I access the ReturnValue (@@RowCount) through ASP code?

Thanks

Relevant Pages

  • Accessing Sql Stored Proc ReturnValue through ASP
    ... I am trying to return a ReturnValue from a SQL Server 2000 stored proc ... from an ASP page using the ADO Command and Parameter objects. ...
    (microsoft.public.data.ado)
  • Re: How to execute a command line in Perlscript
    ... I have implemented "shell" and "puts" functions in my ASP code ... but `$_` return no result in @output array with a simple 'dir' command. ... >> This syntax works fine in a perl program but not in PerlScript ...
    (comp.lang.perl.misc)
  • Re: How to get Perlscript in ASP / IIS to execute command line system calls
    ... Perlscript is running inside of an ASP webpage, ... in the way and works with the DOS command interpreter without problems. ... Obviously there is no problem with perl calling DOS commands via ... ASP (and IIS) are "locked down" for security purposes. ...
    (comp.lang.perl.misc)
  • Re: vbscript WshShell.exec call in ASP
    ... The asp process would be excuting in the same directory as the source ... > Option Explicit ... No matter what command ... > try to exec, I get the error. ...
    (microsoft.public.inetserver.iis.activeserverpages)
  • ASP "stop" command works, but breakpoint does not
    ... I'm using VS.NET 2003 to debug an ASP application locally. ... for ASP debugging under Project Properties--no asp.net debugging. ... Set command = Server.CreateObject ... ' Next statement causes an error, which is why I'm trying to debug. ...
    (microsoft.public.vsnet.debugging)