CODEWITHSUNDEEP
.netvb.netstored-proceduresreturn-value
odiseh
odiseh

Reputation: 26517

VB.net: Why I can NOT get the return value from my own stored procedure?

Why I can NOT get the return value (Scope_Identity) by executing my own Stored procedure (SQL Server 2008) via this below code written in VB.NET 2008? The SP inserts the new record into Table1 but I have 0 as the return value!

What's wrong with it?

here is my vb.net code and my SP:

Public Function Insert(ByVal Obj As entity, connectionString As String) As Integer
    Dim ScopeIdentity As Integer
    Dim Connection As New SqlConnection(connectionString)
    Using Command As New SqlCommand
        With Command
            .Connection = Connection 
            .CommandTimeout = 300
            .CommandType = CommandType.StoredProcedure
            .CommandText = "S_Test"
            If .Connection.State <> ConnectionState.Open Then
                .Connection.Open()
            End If
            SqlCommandBuilder.DeriveParameters(Command)
            With .Parameters
                 .Item("@Name").Value = Obj.Name
                .Item("@Age").Value = Obj.Age
            End With
            Dim ScopeIdentityParameter As New SqlParameter("ReturnValue", SqlDbType.Int)
            ScopeIdentityParameter.Direction = ParameterDirection.ReturnValue
            Command.Parameters.Add(ScopeIdentityParameter)
            Command.ExecuteNonQuery()
            ScopeIdentity = System.Convert.ToInt32(ScopeIdentityParameter.Value)
        End With
    End Using
    Return ScopeIdentity
End Function

my own simple Stored Procedure body:

 insert into dbo.Table1(Name, Age) values(@Name, @Age)
 return SCOPE_IDENTITY()

Upvotes: 3

Views: 5739

Answers (3)

Magnus
Magnus

Reputation: 1754

I realize this is an old question, but I just had the same problem and it seems I figured out the correct answer.

It has to do with SqlCommandBuilder.DeriveParameters.

DeriveParameters automatically adds the returnValue pararameter and gives it the name "@RETURN_VALUE", so just refer to that and you will get its value.

You can also skip DeriveParameters and add all parameters yourself, for example with AddWithValue or similar, including your ReturnValue-parameter (with any name you like).

But both executing DeriveParameters and adding the ReturnValue-parameter yourself will not work.

Upvotes: 0

Shlomo Lev
Shlomo Lev

Reputation: 11

I think that you have a mistake in your code instead of the existing line

ScopeIdentity = System.Convert.ToInt32(ScopeIdentityParameter.Value)

You should use:

ScopeIdentity = System.Convert.ToInt32(ReturnValue.Value)

Upvotes: 1

grenade
grenade

Reputation: 32179

Either change your VB code to take an interest in the return value (eg: Command.ExecuteScalar() rather than Command.ExecuteNonQuery() and use SELECT SCOPE_IDENTITY() at the end of the proc) or change your stored procedure to set the OUT param eg: set @ReturnValue = SCOPE_IDENTITY() instead of return SCOPE_IDENTITY()

Your VB code currently expects the proc to set an output parameter but return SCOPE_IDENTITY() doesn't set the parameter.

See here for context on SCOPE_IDENTITY() as well: http://msdn.microsoft.com/en-us/library/ms190315.aspx

Upvotes: 2

Related Questions