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()
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
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)
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.
精彩评论