Get Scope Identity Value using ObjectDataSource and Vb.Net

Hi,

I have been trying to get the scope Identity after inserting a record using an ObjectDataSource.

I can't find what event, or how to get the value that the scope identity returns.

Here is my Sproc.

ALTER PROCEDUREdbo.[YourCompany_LanCustomer_Insert]

(

@.DNNUserIDint,

@.FirstNamenvarchar(50),

@.LastNamenvarchar(50),

@.Addressnvarchar(50),

@.Address2nvarchar(50),

@.Citynvarchar(50),

@.Statenvarchar(50),

@.Zipnvarchar(50),

@.EmailAddressnvarchar(50),

@.PhoneNumbernvarchar(50),

@.CustomerIDint OUTPUT

)

AS

INSERT INTOYourCompany_LanCustomer

(DNNUserID, FirstName, LastName, Address, Address2, City, State, Zip, EmailAddress, PhoneNumber, DateEntered)

VALUES(@.DNNUserID, @.FirstName, @.LastName, @.Address, @.Address2, @.City, @.State, @.Zip, @.EmailAddress, @.PhoneNumber,getdate())

SET@.CustomerID =Scope_Identity()

RETURN

When I try to execute the stored procedure in Sql Manager I get the CustomerID Value, how do I get this value in the VB.Net code behind?

Any help is greatly appreciated.

In the Inserted event of the ObjectDataSource and you use the OutputParameters collection to get the value

Protected Sub ObjectDataSource1_Inserted(ByVal senderAs Object,ByVal eAs ObjectDataSourceStatusEventArgs)Dim _customerIdAs Integer =CInt(e.OutputParameters("@.CustomerID"))End Sub

Thanks

-Mark post(s) as "Answer" that helped you

|||

You'll get the collection of the insert parameters for the data source. You can retrieve the value of any parameter using yourDataSourceID.InsertParameters("parameterName"). You haven't explained how you're executing the stored procedure here. If possible post the aspx page code so that we can know how you've setup the data source.

|||

Now I'm lost, do I get the Scope Identity at the iteminserting or the iteminserted. I tried the first sample and get a parameter is not equal error.

Here is my ascx (I'm using dotnetnuke) for the objectDataSource.

<asp:ObjectDataSourceID="ObjectDataSource_Customer"runat="server"TypeName="YourCompany.Modules.Lan.LanCustomerController"SelectMethod="LanCustomer_GetCustomers"DataObjectTypeName="YourCompany.Modules.Lan.LanCustomerInfo"DeleteMethod="LanCustomer_Delete"OldValuesParameterFormatString="original_{0}"InsertMethod="LanCustomer_Insert">

</asp:ObjectDataSource>

here is the code behind (vb.net) that I am using on insert:

ProtectedSub NewItem(ByVal senderAsObject,ByVal eAs System.Web.UI.WebControls.FormViewInsertEventArgs)Handles CustomerFormView.ItemInserting

Try

e.Values.Item("CustomerId") = 0

If e.Values.Item("DNNUserID") ="-1"Then

e.Values.Item("DNNUserID") ="0"

Else

e.Values.Item("DNNUserID") = UserId

EndIf

If e.Values.Item("FirstName") =""Then

e.Values.Item("FirstName") = Null.NullString

EndIf

If e.Values.Item("LastName") =""Then

e.Values.Item("LastName") = Null.NullString

EndIf

If e.Values.Item("Address") =""Then

e.Values.Item("Address") = Null.NullString

EndIf

If e.Values.Item("Address2") =""Then

e.Values.Item("Address2") = Null.NullString

EndIf

If e.Values.Item("City") =""Then

e.Values.Item("City") = Null.NullString

EndIf

If e.Values.Item("State") =""Then

e.Values.Item("State") = Null.NullString

EndIf

If e.Values.Item("Zip") =""Then

e.Values.Item("Zip") = Null.NullString

EndIf

If e.Values.Item("EmailAddress") =""Then

e.Values.Item("EmailAddress") = Null.NullString

EndIf

If e.Values.Item("PhoneNumber") =""Then

e.Values.Item("PhoneNumber") = Null.NullString

EndIf

Catch exAs Exception

ProcessModuleLoadException(Me, ex)

EndTry

EndSub

The insert sproc worked before, I just can't get it to work now.

|||

Hi,

SET @.CustomerID = Scope_Identity()

From the code you provided, the @.CustomerID is an OUTPUT parameter you set, right?

And we assume that you are using SqlCommand to execute the stored procedure in your business object method, and then you can retrieve the OUTPUT parameter in stored procedure by declaring a SqlParameter which in an OUTPUT direction. Make your business object method return the parameter's value after you invoking ExecuteNonQuery() method.

And then, in ObjectDataSource1_Selected event, try to get the value from RetrunValue property of ObjectDataSourceStatusEventArgs.

Thanks.

|||

Nai-Dong Jin - MSFT:

you can retrieve the OUTPUT parameter in stored procedure by declaring a SqlParameter which in an OUTPUT direction. Make your business object method return the parameter's value after you invoking ExecuteNonQuery() method.

And then, in ObjectDataSource1_Selected event, try to get the value from RetrunValue property of ObjectDataSourceStatusEventArgs.

Why do we need to "RETURN" the "OUTPUT PARAMETER" ? Do you know that RETURN values and OUTPUT parameters are independent of each other and we could retrieve either "RETURN" value or "OUTPUT" parameter or both of them?

Note: I dont know how the thread was marked as "Answer"

Thanks

-Mark post(s) as "Answer" that helped you

|||

Hi e_screw,

First, I think you've misunderstood my words. What I suggest is to declare an OUTPUT parameter in his stored procedure, and then assign the parameter with the value of Identity_Scope(). That's all. What the rest is retrieving parameters in .NET application by using SqlParameter which is in OUTPUT direction. Is there anything wrong? In stored procedure level, can you find any words on "RETURN" in my previous post?

Make your business object method return the parameter's value after you invoking ExecuteNonQuery() method.

And since the original poster was using ObjectDataSource, so he must had invoked the ExecuteNonQuery() in the business object method, right? What I said "return the parameter's value" means return the value from the business method. In this stage, that's totally nothing related with the OUTPUT parameter in procs.

Now I'm lost,

Second,of course, you also can use "Return" to achieve that, but since the original poster was lost, kept asking against previous solution and no one followed up, I just provide another solution for him to refer.

So if you are able to help him further with your solution, I appreciate it. And it also can be beneficial to other community members reading the thread.

Thanks.

|||

This is your previous post:

Nai-Dong Jin - MSFT:

And then, in ObjectDataSource1_Selected event, try to get the value from RetrunValue property of ObjectDataSourceStatusEventArgs.

Last post:

Nai-Dong Jin - MSFT:

First, I think you've misunderstood my words. What I suggest is to declare an OUTPUT parameter in his stored procedure, and then assign the parameter with the value of Identity_Scope(). That's all. What the rest is retrieving parameters in .NET application by using SqlParameter which is in OUTPUT direction. Is there anything wrong? In stored procedure level, can you find any words on "RETURN" in my previous post?

In the first you said, get the value from the ReturnValue property , after assigning the value of OUTPUT parameters to it. In the second, you are just talking about OUTPUT parameters.

Have you had looked at the ObjectDataSourceStatusEventArgs, there is OutputParameters (which returns a collection of output parameters and their values) and a ReturnValue (which gets the return value returned by the business object, if any). Now read your replies again.

Note: Its not with my solution or your solution. Its all about a correct solution, which helps many other community members.

Thanks

|||

Hi,

To Dan5150,

Here's the sample code for you which describes the solution in my previous posts.

First, in your Procedure:

set ANSI_NULLSONset QUOTED_IDENTIFIERONgoALTER PROCEDURE [dbo].[ProcName]@.TOINSERTNVARCHAR(50),@.RESULTINT OUTPUT-- THE OUTPUT Parameter has been set as OUTPUTAS INSERT INTO MYTABLE(TOINSERT)VALUES (@.TOINSERT)SET@.RESULT = SCOPE_IDENTITY();

Second, here's the method in business object class:

Public Function BusinessMethod(ByVal TOINSERTAs String)As String Dim connAs String = ConfigurationManager.ConnectionStrings("SampleDbConnectionString").ConnectionStringDim myconnAs New SqlConnection(conn)Dim mycommAs New SqlCommand() mycomm.Connection = myconn mycomm.CommandText ="ProcName" mycomm.CommandType = CommandType.StoredProcedureDim sp1As New SqlParameter() sp1.ParameterName ="TOINSERT" sp1.Value = TOINSERTDim sp2As New SqlParameter() sp2.ParameterName ="RESULT"' This parameter has been set in OUTPUT direction sp2.Direction = ParameterDirection.Output sp2.Size = 4 sp2.SqlDbType = SqlDbType.Int mycomm.Parameters.Add(sp1) mycomm.Parameters.Add(sp2) myconn.Open() mycomm.ExecuteNonQuery() myconn.close()' Return the parameter in OUTPUT direction.Return sp2.Value.ToString()End Function

Third, you can get the value in Inserted event of ODS by accessing ReturnValue property.

Protected Sub ObjectDataSource1_Inserted(ByVal senderAs Object,ByVal eAs ObjectDataSourceStatusEventArgs) Response.Write(e.ReturnValue.ToString())' You can get the id of new inserted row here.End Sub

To e_screw,

Please read my codes, and especially the comment parts in bold. And let's back to your solution which given in the second post:

Protected Sub ObjectDataSource1_Inserted(ByVal sender As Object, ByVal e As ObjectDataSourceStatusEventArgs)
Dim _customerId As Integer = CInt(e.OutputParameters("@.CustomerID"))
End Sub

You can use OutputParameters collection to retrieve the value, while output parameters would be ByRef (out in C#) parameters.

But since the original poster hadn't posted out his business method signature, how can you make sure that he was declaring parameters that are passed to the business object method by reference? If the parameters was passed by val, how could he get the value in OutputParameters collection?

Thanks.