Title: How can i get the SCOPE_IDENTITY() From SP?
Tags: sql-server;vb.net;stored-procedures;dbnull;scope-identity
Question: Im trying to get the SCOPE_IDENTITY to my vb.NET App from a Stored Procedure
But i get DBNull Exception.
This is my SP:
ALTER PROCEDURE [dbo].[sp_XYZ]
@ID INT,
@OtherID INT OUTPUT
SELECT
@Name = name,
FROM dbo.PERSON
WHERE  id = @ID
INSERT INTO [SERVERSOLMAR\TSW].TSWDATA.dbo.t_Employees
Name
VALUES
@Name
SET @OtherID = SCOPE_IDENTITY()
My vb.NET Code:
Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("CON").ConnectionString)
Dim dataAdapter As New SqlDataAdapter("sp_XYZ", con)
Dim ARLI As Integer
Try
con.Open()
With dataAdapter.SelectCommand
.CommandType = CommandType.StoredProcedure
.Parameters.Add("@ID", SqlDbType.VarChar).Value = id
Dim identity As New SqlParameter("@OtherID", SqlDbType.Int)
valorInteger.Direction = ParameterDirection.Output
.Parameters.Add(identity)
.ExecuteNonQuery()
ARLI = identity.Value
End With
End try
The insert statement is working fine.
The problem is im getting "Conversion from type 'DBNull' to type 'Integer' is not valid."
I am using a Linked Server, could this be the problem?
Comment: I noticed you updated your proc code with the right syntax for IDENTITY_SCOPE() after I posted my answer.  Now I see another syntax error: your .net param for @ID is SqlDbType.VarChar where the proc is INT.  If you're still looking for help, run the proc from TSQL and verify that the OrderID comes out of the proc just using TSQL, that will isolate this to either a SQL problem or a .Net problem.


Here is another answer: Try using SCOPE_IDENTITY() instead of SCOPE_IDENTITY.

{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 1, 'CommentCount': 1, 'ContentLicense': 'CC BY-SA 3.0', 'CreationDate': '2013:00:06/07/13', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<sql-server><vb.net><stored-procedures><dbnull><scope-identity>', 'Title': 'How can i get the SCOPE_IDENTITY() From SP?', 'ViewCount': 655, 'id': 16991653, 'user_AboutMe': None, 'user_AccountId': 2608044.0, 'user_DisplayName': 'Emmanuel Santana', 'user_Reputation': 153.0, 'user_Views': 34.0, 'user_id': 2258786.0}}