Re: Stored Procedure from ASP
From: Dan Guzman (danguzman_at_nospam-earthlink.net)
Date: 06/28/04
- Next message: Dan Guzman: "Re: Ideas on running a large UPDATE causing Locks..."
- Previous message: John Peterson: "Re: How to determine Registry "root" for SQL Server instance?"
- In reply to: George Hester: "Re: Stored Procedure from ASP"
- Next in thread: George Hester: "Re: Stored Procedure from ASP"
- Reply: George Hester: "Re: Stored Procedure from ASP"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 28 Jun 2004 08:31:27 -0500
> Was it a fluke or did you know that the RETURN_CODE Parameter must be
created first?
I created the return parm first because I always to it that way :-)
To be honest, I usually create stored procedure parameters in testing using
the Refresh method (not a Best Practice in production) and observed that the
return code parameter is always returned as the first ordinal with the name
@RETURN_VALUE (I mistakenly said @RETURN_CODE in my original response).
--
Hope this helps.
Dan Guzman
SQL Server MVP
"George Hester" <hesterloli@hotmail.com> wrote in message
news:eAILFiMXEHA.2972@TK2MSFTNGP12.phx.gbl...
Dang. Here is the issue. The RETURN_CODE parameter MUST be created first.
All I did was take what you have and change the order of your
CreateParameters. And it failed. Why did you write it that way? Was it a
fluke or did you know that the RETURN_CODE Parameter must be created first?
--
George Hester
__________________________________
"Dan Guzman" <danguzman@nospam-earthlink.net> wrote in message
news:uvxikpLXEHA.3476@tk2msftngp13.phx.gbl...
> 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
> > >
> > >
> >
> >
>
>
- Next message: Dan Guzman: "Re: Ideas on running a large UPDATE causing Locks..."
- Previous message: John Peterson: "Re: How to determine Registry "root" for SQL Server instance?"
- In reply to: George Hester: "Re: Stored Procedure from ASP"
- Next in thread: George Hester: "Re: Stored Procedure from ASP"
- Reply: George Hester: "Re: Stored Procedure from ASP"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
