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 PROCEDUREdbo.sp_InsertPlayer

(

@.UserNamevarchar(64),

@.Emailvarchar(128)

)

AS

INSERT INTOtblPlayer ([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:SqlDataSourceID="SqlDataPlayer"runat="server"

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

SelectCommand ...

InsertCommand="sp_InsertPlayer"

InsertCommandType="StoredProcedure"

>

...

<InsertParameters>

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

<asp:ParameterName="UserName"Type="String"/>

<asp:ParameterName="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

What I end up doing is something like this:

...
SELECT ScopeIdentity = SCOPE_IDENTITY()

That was back in the .NET 1.1 days, so I don't know if that would be of use with the SqlDataSource. Does that work?

|||

That doeswork (in fact that is my workaround) but just the integer identity value needs to be returned.

Using the SELECT, a result set with one column ScopeIdentity is returned.

I have to use code similar to below to access the identity value:

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

Dim argsAs DataSourceSelectArguments =New DataSourceSelectArguments()

Dim dvAs Data.DataView

dv = SqlDataPlayer.Select(args)

Session("PlayerId") = dv.Table.Rows(0).Item("PlayerId")

and the sp is:

ALTER PROCEDUREdbo.sp_PlayerIDFromUserName

(

@.UserNamevarchar(64)

)

AS

SELECTPlayerIDFrom[tblPlayer]

WHERE[UserName] = @.UserName

It just seems there has to be an easier way than returning a table for one value.

|||The return value is only available in the SqlDataSource_Inserted event.|||

Thanks Motley.

Here's a link with an example.

http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource.inserted.aspx