Dreamweaver - ASP.NET RETURN_VALUE from a Stored Procedure

Author

ASP.NET RETURN_VALUE from a Stored Procedure

NikL42

2004-02-24, 7:29 pm

I have a stored procedue that is returning a value after an insert, and after
trying tons of different things, I am unable to get the @RETURN_VALUE to work.
My stored procedure inserts the data just fine, but I get a
System.NullReferenceException: Object reference not set to an instance of an
object. error whenever I put the
pCustomerAdd.ParameterValue("@RETURN_VALUE").ToString() on the page in any
format. The Stored procedure looks like this-
CREATE PROCEDURE pCustomerAdd
(@name varchar (50),
@live bit)
AS
SET NoCount ON
INSERT INTO customers
([name], live)
VALUES
(@name, @live)
RETURN @@IDENTITY
GO

any suggestions or help would be greatly appreciated. Thank you.

Jon Spivey

2004-02-24, 7:29 pm

Hi,
why not return it as an output -
CREATE PROCEDURE pCustomerAdd
(@name varchar (50),
@live bit,
@newid int OUTPUT
)
AS
SET NoCount ON
INSERT INTO customers
([name], live)
VALUES
(@name, @live)
select @newid = @@identity

and in your code
dim newid as sqlparameter = new sqlparamater("@newid",sqldbtype.int,4)
newid.direction = ParameterDirection.Output
yourcommand.parameters.add(newid)
' new id is
' yourcommand.parameters("@newid").value

if youre on sql server2000 better to use @newid=scope_identity()

Jon

"NikL42" <webforumsuser@macromedia.com> wrote in message
news:c1gkpq$5lo$1@forums.macromedia.com...
> I have a stored procedue that is returning a value after an insert, and
after
> trying tons of different things, I am unable to get the @RETURN_VALUE to
work.
> My stored procedure inserts the data just fine, but I get a
> System.NullReferenceException: Object reference not set to an instance of
an
> object. error whenever I put the
> pCustomerAdd.ParameterValue("@RETURN_VALUE").ToString() on the page in any
> format. The Stored procedure looks like this-
> CREATE PROCEDURE pCustomerAdd
> (@name varchar (50),
> @live bit)
> AS
> SET NoCount ON
> INSERT INTO customers
> ([name], live)
> VALUES
> (@name, @live)
> RETURN @@IDENTITY
> GO
>
> any suggestions or help would be greatly appreciated. Thank you.
>

NikL42

2004-02-24, 8:28 pm

I actually have tested with returning it as an output, it is the dreamweaver
side that I suspect contains the problem. It appears as though drewamweaver
attempts to render pCustomerAdd.ParameterValue("@userID").ToString() when the
page initially loads. Since the insert has not been executed there is no
return value yet, so the page gives the Object reference not set to an instance
of an object. error. i would like to be able to use the return value within
dreamweavers code, as a last result i will have to code everything by hand and
forget dreamweavers asp.net functionality. it seems as though there should be
some solution though, as dreamweavers bindings interface has support for
Return_values and OUTPUT values as well. when i setup the @@identity as an
ouput @userID and then i define a dreamweaver paramater
<Parameter Name="@userID" Type="Int" Direction="Output" />
i always recieve the same error(Object reference not set to an instance of an
object.) whenever i try to reference that userID-
pCustomerAdd.ParameterValue("@userID").ToString()

Thanks again!