Unable to get returned valaue of Identity field from stored procedure 1

[mdiaz1328]

The return value is always blank, yet the inserts are happening.

Here is my stored procedure"
USE [stationdata]
GO
/****** Object: StoredProcedure [usrFireAdmin].[TMTC_SP_INSERT_USER] Script Date: 01/10/2012 07:07:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [usrFireAdmin].[TMTC_SP_INSERT_USER] (
@FirstName varchar(25),
@LastName varchar(25),
@Email varchar(90),
@password varchar(255)
)
as
SET NOCOUNT ON
insert into usrFireAdmin.TMTC_Users
(FirstName,
LastName,
EMAIL,
PASSWORD)
Values
(@firstname,@lastname,@password,@email)
--return IDENT_CURRENT('TMTC_Users')
return 99

Here is ther ASP Code:

<%
Const adParamReturnValue = 4
Const adInteger = 3

set cmdMain = Server.CreateObject("ADODB.Command")
Set objParameter = Server.CreateObject("AdoDB.Parameter")

Dim oConn, rs
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open connectionstring
sql = "exec TMTC_SP_INSERT_USER @FirstName='First1',@LastName='Last1',@Email='first.last@test.com',@password='password1'"
cmdMain.CommandText = sql
Set objParameter = cmdMain.CreateParameter("Return", adInteger,adParamReturnValue,,0)
cmdMain.Parameters.Append objParameter

cmdMain.ActiveConnection = oConn

Set rs = cmdMain.Execute
strUserID = cmdMain.Parameters(0)

Set rs = Nothing
Set oConn = nothing
Set cmdMain = nothing
%>

The New Users's ID is [ <%=strUserID%> ]


Any help would be appreciated. THANX !

 

[BigRed1212]

return(99)

wouldn't make any difference would it?

 

[BigRed1212]

If you are trying to return the id of the newly inserted item, Google shows me code that looks like

IF @@ERROR <> 0
BEGIN
-- Return -1 to the calling program to indicate failure.
PRINT "An error occurred loading the new author information"
SELECT @MyOuptputParameter = -1
END
ELSE
BEGIN
-- Return id to the calling program to indicate success.
PRINT "The new author information has been loaded"
SELECT @MyOuptputParameter = @@IDENTITY
END

and suggestions to use an output parameter for a value and only use return for a success/falure indication.

Maybe that is helpful.

 

[gmmastros]

I would suggest that you NOT use the return value from the stored procedure. In my humble opinion, return values should be used for status codes. Like 0 = everything is fine, 1 = Blank Password, 2 = insert failed, etc....

You would probably be better off using an output parameter and Scope_Identity. During busy times, ident_current could return the wrong value.

ALTER procedure [usrFireAdmin].[TMTC_SP_INSERT_USER] (
    @FirstName varchar(25),
    @LastName varchar(25),
    @Email varchar(90),
    @password varchar(255),
    @OutputValue Int OUTPUT
)
as
SET NOCOUNT ON
 insert into usrFireAdmin.TMTC_Users
    (FirstName,
    LastName,
    EMAIL,
    PASSWORD)
Values
(@firstname,@lastname,@password,@email)

Set @OutputValue = Scope_Identity()

You'll need to adjust your ASP code to accommodate the output parameter. While you're at it, you should make the other 4 parameters to the stored procedure be input parameters.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

BigRed1212,

Please do not use @@Identity for SQL Server. This may be acceptable for other database engines like Microsoft Access, but it is a recipe for disaster with SQL Server.

@@Identity returns the last identity value that was inserted. Unfortunately, if you insert in to a table with a trigger, and that trigger also inserts in to a table with an identity column, @@Identity will return the identity value of the 2nd table while Scope_Identity() will return the identity value of the main table.

This is particularly bad because you may be using a table without a trigger, and @@Identity works perfectly. 18 months later, you add a trigger and suddenly start getting the wrong value. It may be a couple days (or weeks) before you discover this problem. Now you have a real mess on your hands.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

BigRed1212,

I know I mentioned why @@Identity is bad, but I should also have mentioned that SQLCop can determine all places in your TSQL code where you are using @@Identity. Of all the problems SQLCop can find, this is probably one of the simplest to fix.

In case you don't know about SQLCop. SQLCop is a utility application I wrote a couple years ago. It is free to download and free to use (although donations are gladly accepted). You can download SQLCop by clicking the link in my signature.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[mdiaz1328]

Thanx gmmastros

I tried using "Set @OutputValue = Scope_Identity()" before I posted this issue.

I think once I updated the stored procudure, my ASP code was (is) at fault. Is there any advice on how to code this an classic ASP so that I can retrieve the identity value ?

 

[gmmastros]

It's been a little while since I wrote ASP code, but I'm reasonably certain this is on the right track:

cmdMain.CommandType = adCmdStoredProc
cmdMain.CommandText = "TMTC_SP_INSERT_USER"
Call cmdMain.CreateParameter("@FirstName", adVarChar, adParamInput, 25, "First1")
Call cmdMain.CreateParameter("@LastName", adVarChar, adParamInput, 25, "Last1")
Call cmdMain.CreateParameter("@Email", adVarChar, adParamInput, 90, "first.last@test.com")
Call cmdMain.CreateParameter("@Password", adVarChar, adParamInput, 255, "Password1")
Call cmdMain.CreateParameter("@OutputValue", adInteger, adParamOutput)
    
cmdMain.Execute
    
strUserId = cmdMain.Parameters.Item("@OutputValue").Value

Note how I create parameters for the input and the output parameter and then how I access the value of the output parameter.

You can also you this as a reference:

http://www.webconcerns.co.uk/asp/sqlqueries/sqlqueries.asp

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom