CODEWITHSUNDEEP
asp.net.netsql-servervb.nett-sql
Art F
Art F

Reputation: 4202

Getting the auto_increment value in .NET

I am relatively new to .NET and SQL Server. I am trying to execute a stored procedure from .NET and get the auto_increment value back. This is my code in .NET (VB.NET):

myComm.Parameters.AddWithValue("@newkey", newKey)
myComm.Parameters.AddWithValue("@newimg", DirectCast(imgByteArray, Object))
Dim resultReader As SqlDataReader = myComm.ExecuteReader
Dim resultId As Integer
While resultReader.Read
     resultId = resultReader.GetInt16("@returnid")
End While

And this is my stored procedure: 

-- Add the parameters for the stored procedure here
@newkey nvarchar(MAX),
@newimg varbinary(MAX),
@returnid integer = 0
AS
BEGIN

SET NOCOUNT ON;

INSERT INTO dbo.AmazonS3Preview(myKey,previewImage) VALUES (@newkey,@newimg)
SET @returnid=(SELECT @@IDENTITY AS [@@IDENTITY])
END

Currently in .NET I am always getting '0' for resultId, can anyone tell me what I am doing wrong?

Upvotes: 0

Views: 244

Answers (2)

MikeSmithDev
MikeSmithDev

Reputation: 15797

You are setting the value but not returning it. Don't need to even set a variable. Make the last line:

SELECT SCOPE_IDENTITY() as returnid

And look at using ExecuteScalar to get the returnId:

myComm.Parameters.AddWithValue("@newkey", newKey)
myComm.Parameters.AddWithValue("@newimg", DirectCast(imgByteArray, Object))
Dim resultId As Integer    
resultId = Convert.ToInt32(myComm.ExecuteScalar())

Upvotes: 3

Jeff S
Jeff S

Reputation: 7484

In the stored procedure, you are setting the parameter returnid to the value. But in your code, you are capturing output from a select statement. I'd change the stored proc to:

-- Add the parameters for the stored procedure here
@newkey nvarchar(MAX),
@newimg varbinary(MAX)
AS
BEGIN

   SET NOCOUNT ON;

   INSERT INTO dbo.AmazonS3Preview(myKey, previewImage) 
   OUTPUT Inserted.ID 
   VALUES (@newkey, @newimg)
END

Two changes. First, removing the returnid parameter. Second, using the Output clause in the SP to get the id back. This should work without requiring any changes to your vb.net code.

Upvotes: 0

Related Questions