Returning Scope_Identity From Stored Procedures

Hi:

I've seen a lot of posts about returning the identity after an insert but for every variation, the return value or output parameter is null.  Below is the sp, SQLDataSource and vb code for the return value attempt. 

Here is the stored procedure:

   ALTER PROCEDURE dbo.sp_InsertPlayer

   (

   @UserName varchar(64),

   @Email varchar(128)



   )

   AS

   INSERT INTO tblPlayer ([UserName], )

   VALUES (@UserName, @Email)

   RETURN SCOPE_IDENTITY()

 

If the sp is executed in the SQL Express that is included in VS 2005,  the return value is the last autogenerated PlayerId.

SqlDataSource:

   <asp:SqlDataSource ID="SqlDataPlayer" runat="server"

   ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

   SelectCommand ...

   InsertCommand="sp_InsertPlayer"

   InsertCommandType="StoredProcedure"

   >

... 

   <InsertParameters>

   <asp:Parameter Name="RETURN_VALUE" Direction="ReturnValue" Type="Int32" />

   <asp:Parameter Name="UserName" Type="String" />

   <asp:Parameter Name="Email" Type="String" />



   </InsertParameters>

</asp:SqlDataSource>

code behind vb:

   SqlDataPlayer.InsertParameters("UserName").DefaultValue = CreateUserWizard1.UserName

   SqlDataPlayer.InsertParameters("Email").DefaultValue = CreateUserWizard1.Email

   SqlDataPlayer.Insert()

   Profile.PlayerId = SqlDataPlayer.InsertParameters("RETURN_VALUE").DefaultValue

The DefaultValue is Nothing.

Thanks for your help again.

arora