Return @@IDENTITY Error

[DH]

Hello,

I am receiving the following error when trying to return the identity of the record just insterted...

Error:

String or binary data would be truncated. The statement has been terminated. The 'Insert_New_File' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead.

Here is a snippet of the code behind:
-------------------------------------------

'Prepare command object
Dim cmdsql As SqlCommand
Dim parmReturnValue As SqlParameter

cmdsql = New SqlCommand
cmdsql.Connection = oConn
cmdsql.CommandType =CommandType.StoredProcedure
cmdsql.CommandText = "Insert_New_File"

'Pass in parameters
cmdsql.Parameters.Add("@userinfoID", 1)
cmdsql.Parameters.Add("@purposeloan", purpose.SelectedValue)
cmdsql.Parameters.Add("@referralsource", leadsource.SelectedValue)

'Return parameter
parmReturnValue = cmdsql.Parameters.Add("Return_Value", SqlDbType.Int)
parmReturnValue.Direction = ParameterDirection.ReturnValue

'Open connection
oConn.Open()

'Perform insert
cmdsql.ExecuteNonQuery()

-----------------------------------------
Error happens at cmdsql.ExecuteNonQuery()

Here is the stored procedure:
-----------------------------------------

CREATE PROCEDURE Insert_New_File
(
@userinfoID int,
@purposeloan Varchar( 50 ),
@referralsource Varchar( 50 )
)
AS
INSERT [File](
userinfoID,
purposeloan,
referralsource
) VALUES (
@userinfoID,
@purposeloan,
@referralsource
)
RETURN @@Identity
GO
--------------------------------------------

The insert takes place on a table named File. There is an
AFTER INSERT Trigger that takes the new FileID and inserts it into other tables.

When I run the above code on a similar table with any Triggers it works fine but when I run on the table with the Trigger and try to return the @@Identity it blows up. Not sure what is happening here.

Please help!

Thanks,

DH

 

[vongrunt]

RETURN in stored procedure has different purpose than you think. In this case use OUTPUT parameter instead.

And - @@IDENTITY is not good in some situations (SCOPE_IDENTITY() is better).

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[vongrunt]

Almost forgot: truncation error is caused by too long data passed by - for example, attempt to insert value with 30 characters into varchar(20). You may also need to specify data types/lengths for all Cmd.Parameters that pass character data.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[DH]

Thank you,

I changed the Return statement in the Stored Proc to read:

RETURN Scope_Identity()

An all is well now and working.

Thank you...

DH