Re: Stored Procedure from ASP

From: Dan Guzman (danguzman_at_nospam-earthlink.net)
Date: 06/28/04 

Date: Sun, 27 Jun 2004 21:48:27 -0500

I'm not sure what is different with your code but below is the DDL and
VBScript I successfully tested with.

CREATE TABLE NewRegion
 (
    NewRegionID int NOT NULL IDENTITY(1,1)
        CONSTRAINT PK_NewRegion PRIMARY KEY,
    RegionDescription varchar(100) NOT NULL
 )
GO

CREATE PROCEDURE AddRegion
 @RegionDesc nchar(50),
 @NewRegionID int OUTPUT
AS
SET NOCOUNT ON
INSERT INTO NewRegion (RegionDescription)
VALUES (@RegionDesc)

SET @NewRegionID=@@IDENTITY

IF @@ERROR<>0
BEGIN
     RETURN 1
END
ELSE
BEGIN
     RETURN 0
END
GO

'VbScript
Set connection = CreateObject("ADODB.Connection")

connection.Open _
    "Provider=SQLOLEDB" & _
    ";Data Source=MyServer" & _
    ";Initial Catalog=MyDatabase" & _
    ";Integrated Security=SSPI"

Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = connection
cmd.CommandType = adCmdStoredProcedure
cmd.CommandText = "AddRegion"

Set pReturnCode = cmd.CreateParameter("@RETURN_CODE")
pReturnCode.Type = adInteger
pReturnCode.Direction = 4 'adParamReturnValue
cmd.Parameters.Append pReturnCode

Set pRegionDescription = cmd.CreateParameter("@RegionDesc")
pRegionDescription.Type = adVarchar
pRegionDescription.Direction = adParamInput
pRegionDescription.Size = 50
pRegionDescription.Value = "TimBukTo"
cmd.Parameters.Append pRegionDescription

Set pNewRegionID = cmd.CreateParameter("@NewRegionID")
pNewRegionID.Type = adInteger
pNewRegionID.Direction = adParamOutput
cmd.Parameters.Append pNewRegionID

cmd.Execute

MsgBox "@RETURN_CODE=" & cmd.Parameters("@RETURN_CODE").Value
MsgBox "@NewRegionID=" & cmd.Parameters("@NewRegionID").Value

connection.Close 

-- 
Hope this helps.
Dan Guzman
SQL Server MVP
"George Hester" <hesterloli@hotmail.com> wrote in message
news:uzNtP1KXEHA.716@TK2MSFTNGP11.phx.gbl...
Thanks Dan.  I did this:
Everything else is the same.  I changed the relevant portion of the ASP to
this:
Set pReturnCode = Server.CreateObject("ADODB.Parameter")
Set pReturnCode = cmd.CreateParameter
pReturnCode.Name = "@RETURN_VALUE"
pReturnCode.Type = adInteger
pReturnCode.Direction = adParamReturnValue
cmd.Parameters.Append pReturnCode
cmd.Execute lRecs, , adExecuteNoRecords 'line 41
The result was this:
Microsoft OLE DB Provider for SQL Server error '80040e14'
Procedure or function AddRegion has too many arguments specified.
/adotest/outputparam.asp, line 41
So there must be something that I have not included or included that is
leading to this issue.  I can fix this by using an explicit parameter in the
Stored Procedure but I am unable to NOT explicitly give a parameter there
and use the adParamReturnValue in any meaningful way.
-- 
George Hester
__________________________________
"Dan Guzman" <danguzman@nospam-earthlink.net> wrote in message
news:eUmxytGXEHA.1164@tk2msftngp13.phx.gbl...
> Try using '@RETURN_VALUE' as the parameter name.
>
>
> -- 
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> "George Hester" <hesterloli@hotmail.com> wrote in message
> news:%23RJ199wWEHA.3640@TK2MSFTNGP11.phx.gbl...
> This fails:
>
> CREATE PROCEDURE AddRegion
> @RegionDesc nchar(50),
> @NewRegionID int OUTPUT,
>  AS
> INSERT INTO NewRegion (RegionDescription)
> VALUES (@RegionDesc)
>
> SET @NewRegionID=@@IDENTITY
>
> IF @@ERROR<>0
> BEGIN
>      RETURN(1)
> END
> ELSE
> BEGIN
>      RETURN(0)
> END
> GO
>
> <%@language="VBScript"%>
> <%
> Option Explicit
> Dim cn, cmd, rs, lRecs
> Dim pRegionDescription
> Dim pNewRegionID
> Dim pReturnCode
>
> Set cn = Server.CreateObject("ADODB.Connection")
> cn.Open(Application("DBconn"))
> Set cmd = Server.CreateObject("ADODB.Command")
>
> cmd.CommandText = "AddRegion"
> cmd.CommandType = adCmdStoredProc
>
> Set cmd.ActiveConnection = cn
>
> Set pRegionDescription = Server.CreateObject("ADODB.Parameter")
> Set pRegionDescription = cmd.CreateParameter("RegionDesc")
> pRegionDescription.Type = adChar
> pRegionDescription.Direction = adParamInput
> pRegionDescription.Size = 50
> pRegionDescription.Value = "TimBukTo"
> cmd.Parameters.Append pRegionDescription
>
> Set pNewRegionID = Server.CreateObject("ADODB.Parameter")
> Set pNewRegionID = cmd.CreateParameter("NewRegionID")
> pNewRegionID.Type = adNumeric
> pNewRegionID.Direction = adParamOutput
> pNewRegionID.Precision = 4
> cmd.Parameters.Append pNewRegionID
>
> Set pReturnCode = Server.CreateObject("ADODB.Parameter")
> Set pReturnCode = cmd.CreateParameter("ReturnCode")
> pReturnCode.Type = adInteger
> pReturnCode.Direction = adParamReturnValue
> cmd.Parameters.Append pReturnCode 'BUMMER
>
> cmd.Execute lRecs, ,adExecuteNoRecords
> %>
> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
>   "http://www.w3.org/TR/1999/REC-html401-19991224/loose.dtd">
> <html>
> <head>
> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
> <meta http-equiv="Content-Language" content="en-us">
> <meta http-equiv="Content-Script-Type" content="text/javascript">
> <meta http-equiv="Content-Style-Type" content="text/css">
> <meta name="GENERATOR" content="Microsoft Visual Studio 6.0">
> <title>Output Param</title>
> </head>
> <body>
> <%="New Region: " & cmd.Parameters("NewRegionID").Value%>
> <%
> If cmd.Parameters("ReturnCode").Value = 0 Then
>  Response.Write "<br/>The Stored Procedure completed successfully"
> Else
>  Response.Write "<br/>ERROR"
> End If
> %>
> </body>
> </html>
> <%
> Response.End
> Set pReturnCode = Nothing
> Set pNewRegionID = Nothing
> Set pRegionDescription = Nothing
> If cn.State = adSateOpen Then
>  cn.Close
> End If
> Set cn = Nothing
> %>
>
> at BUMMER in the ASP.  "Too many parameters."  So  I probably missed your
> point.  The one I gave you in op does work just fine.  This one don't but
I
> believe it should.  Thanks.
>
> -- 
> George Hester
> __________________________________
> "Jaxon" <GregoryAJackson@hotmail.com> wrote in message
> news:#49$FrwWEHA.3988@tk2msftngp13.phx.gbl...
> > you are confusing your RETURN value with your OUTPUT Parameter.....
> >
> >
> >
> >
> > CREATE PROCEDURE AddRegion
> > @RegionDesc nchar(50),
> > @NewRegionID int OUTPUT,
> > @ReturnCode int OUTPUT
> >
> >
> >
> > SET @ReturnCode=1
> >
> >
> > this is NOT the same as:
> > RETURN 1
> > (This WOULD create a Return value that you could access as a RETURN
Param)
> >
> >
> > This should be defined as Output because that is how your sproc is
> > implementing it......
> > pReturnCode.Direction = adParamReturnValue
> >
> >
> >
> >
> > catch my point ?
> >
> > Hope this helps,
> >
> > Greg Jackson
> > PDX, Oregon
> >
> >
>
>

Relevant Pages

  • Re: Stored Procedure from ASP
    ... > Set pReturnCode = Server.CreateObject ... > and use the adParamReturnValue in any meaningful way. ... >> Set pRegionDescription = cmd.CreateParameter ... >> Set pNewRegionID = cmd.CreateParameter ...
    (microsoft.public.sqlserver.server)
  • Re: Stored Procedure from ASP
    ... Thanks Dan. ... > Set pReturnCode = Server.CreateObject ... > and use the adParamReturnValue in any meaningful way. ... >> Set pNewRegionID = cmd.CreateParameter ...
    (microsoft.public.sqlserver.server)
  • Re: Stored Procedure from ASP
    ... > "George Hester" wrote in message ... > Set pReturnCode = Server.CreateObject ... > and use the adParamReturnValue in any meaningful way. ... >> Set pNewRegionID = cmd.CreateParameter ...
    (microsoft.public.sqlserver.server)