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 PROCEDURE dbo.[YourCompany_LanCustomer_Insert]

    (

    @DNNUserID int,

    @FirstName nvarchar(50),

    @LastName nvarchar(50),

    @Address nvarchar(50),

    @Address2 nvarchar(50),

    @City nvarchar(50),

    @State nvarchar(50),

    @Zip nvarchar(50),

    @EmailAddress nvarchar(50),

    @PhoneNumber nvarchar(50),

    @CustomerID int OUTPUT

    )

    AS

INSERT INTO YourCompany_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.


Dan5150
www.5150creations.com
0
Dan5150
12/25/2007 2:43:52 AM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

8 Replies
1052 Views

Similar Articles

[PageSpeed] 52

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

 

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

Thanks

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


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

Electronic Screw
Website||Blog||Dub@i.net
0
e_screw
12/25/2007 5:38:13 AM

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.


Thanks,
Dhimant Trivedi
"When the going gets tough, tough gets going."

"Mark as Answer" the post(s) which helped you solve the problem
0
dhimant
12/25/2007 5:55:03 AM

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:ObjectDataSource ID="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:

Protected Sub NewItem(ByVal sender As Object, ByVal e As 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

End If

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

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

End If

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

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

End If

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

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

End If

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

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

End If

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

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

End If

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

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

End If

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

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

End If

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

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

End If

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

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

End If

Catch ex As Exception

ProcessModuleLoadException(Me, ex)

End Try

 

End Sub

 

 

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


Dan5150
www.5150creations.com
0
Dan5150
12/27/2007 7:22:14 AM

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.


Michael Jin.
Microsoft Online Community Support

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
0
Nai
12/28/2007 3:33:56 AM

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


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

Electronic Screw
Website||Blog||Dub@i.net
0
e_screw
12/28/2007 6:16:46 AM

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.


Michael Jin.
Microsoft Online Community Support

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
0
Nai
12/28/2007 8:47:20 AM

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


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

Electronic Screw
Website||Blog||Dub@i.net
0
e_screw
12/29/2007 10:03:45 AM

Hi,

To Dan5150,

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

First, in your Procedure:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[ProcName]

@TOINSERT NVARCHAR(50),
@RESULT INT OUTPUT -- THE OUTPUT Parameter has been set as OUTPUT

AS
 INSERT INTO MYTABLE(TOINSERT) VALUES (@TOINSERT)
 SET @RESULT = SCOPE_IDENTITY(); 
 

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

 

Public Function BusinessMethod(ByVal TOINSERT As String) As String 
    
    Dim conn As String = ConfigurationManager.ConnectionStrings("SampleDbConnectionString").ConnectionString 
    Dim myconn As New SqlConnection(conn) 
    
    Dim mycomm As New SqlCommand() 
    mycomm.Connection = myconn 
    mycomm.CommandText = "ProcName" 
    mycomm.CommandType = CommandType.StoredProcedure 
    
    Dim sp1 As New SqlParameter() 
    sp1.ParameterName = "TOINSERT" 
    sp1.Value = TOINSERT 
    
    Dim sp2 As 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 sender As Object, ByVal e As 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.


 


Michael Jin.
Microsoft Online Community Support

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
0
Nai
12/29/2007 2:44:43 PM
Reply:

Similar Artilces:

Sending SMS using vb.net or C#.net using vb.net or c#.net
Hi  My requirement is I hav one csv file with these fields id,mobilenum,messgae,status.intiallu staus is 0. once i read the all fileds and take that mobile number.using tat mobile number  i need to send sms .after sending sms i shuld change status as 1 How to send sms thru coding (please dont provide any links.if it is provide also please give working links becox i checked codeproject .i didnt get any nice link. and also provide the how to update the status field im csv file   Thank ssandhya   To send SMS, you need some third party SMS providers. if you consul...

used a c#.net to vb.net converter and am now getting a syntax error.
ok I used a cool utility at http://www.kamalpatel.net/ConvertCSharp2VB.aspx to convert some c#.net to vb.net and it spit out a bunch of code which almost works but I am getting a syntax error on this line Imports (StreamReader sr = Shadows Function)() As StreamReader(objResponse.GetResponseStream()) I tried it that way and also like Imports (StreamReader sr = Shadows Function)() As StreamReader(objResponse.GetResponseStream()) but either way gave the same error Compiler Error Message: BC30035: Syntax error. Source Error: Line 26: Line ...

use VB.NET and C#.NET code in the same C#.NET project
All-- Here is a sample that is "off the beaten path", (at least for me). Is it possible, in an ASP.NET application, using the code-behind page building technique, to have both pages written in VB.NET and pages written C#.NET?At http://www.WebLogicArts.com/DemoList.aspx there is a sample that shows that, (contrary to popular belief), it IS possible to mix ASP.NET pages built with C#.NET with ASP.NET pages built with VB.NET in the same VS.NET 2003 project. Note that this is just a "fun" sample to see if it can be done and I do not recommend this practice as a "standard" way of develo...

How to get the selected text HREF value using vb.net
Hello friends,                     If i have a page opened in a web browser control for example www.google.com .if i select a particular content in google web page i have to get the href value of the selected text.how to do this vb.net.can anyone help me. with regards, Balaji.   Balaji,Chennai...

Use VB.Net to get the column properties of a SQL table
I have a repeater, but I don't realy think that matters, what I want to to is find the length of a sql table column. so that I can set the length of a text box to it. the data I am working with comes from a datatable. I'm seeing something out there that might be the answer and it is DataTable.GetSchema but I'm not finding anything to show me how to use it. If that is the answer does someone know how to get the column lenth of the column you are looking for out of it, or how to get what I'm after so other way.  you can issue this sql statement select * ...

use of vb.net and c#.net
i make a small website using vb.net,  in it i make a 2 class in C#.net. that i want to use in that website, i dont know how to call that class. let your class is  - class clsMyClass    {        public void myFunction()        {            // do something        }    } now the first way - create objectclsMyClass myObj = new clsMyClass();myObj.myFunction(); // call function================...

Get value of primary key after INSERT statement using Web Matrix (VB.NET)
I am using the following function developed using Web Matrix to insert a record into a table when the user clicks the submit button.  How can i get the "primary key" or value of the inserted record back to the application.  The following is my insert statement:Sub btnSubmit_Click(sender As Object, e As EventArgs)      CreateMyRecord("98", "UU1")       '==> need the primary key of the inserted record here.......End SubFunction CreateMyRecord(ByVal streetNumber As Integer, ByVal authorityRef As String) As Integer&nbs...

anyone know using AdoTransation in vb.net Cant Return identity column value Afert updateData?
using AdoTransation in vb.net Cant Return identity column value Afert updateData,but using Transation in vb.net Can Return Identity column value .. thks. Assuming you are participating in the 1.5 beta, I would open a case on this. On 10 Jan 2005 18:59:55 -0800, "yang" <followingrain@126.com> wrote: > using AdoTransation in vb.net Cant Return identity column value Afert >updateData,but using Transation in vb.net Can Return Identity column value >. > >thks. > > ...

How to get the HREF value of the Anchor tag in a web page using the web browser control in vb.net
Hi Friends,               i have a webserver control in my application.i load the web browser control on a button click using the below code Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click                AxWebBrowser1.Navigate("http://www.rateitall.com/i-322335-.aspx", False) End sub once the page is loaded in the browser i want the <a> HREF value in that page.For example if that page has ...

how can i write this line using vb.net : container.dataitem ?.... in vb.net its not working
Dim container As GridView = CType(newLinkButton.NamingContainer, GridView) Dim dataValue As Object = DataBinder.Eval(container.dataitem, _ColName)   _ColName its just a string a name of the field in my database   This is Vb.Net code man what you want? You can convert any format from this link http://www.developerfusion.com/tools/convert/vb-to-csharp/  If this post is answer of your question then don't forgot to Click Mark As Answer...

How to use InterBase with VS.NET (vb.net)??
Hi all; Q1:How can I use "InterBase" as a RDMS with Visual Studio.NET (VB.NET) for developing a small application?? Q2:What should I use for a Data Provider? Q3:How ConnectionString will be build?? Thanks in advanced!Regards,CS4Ever~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Dont forget to click "Mark as Answer" on the post that helped you.This credits that member, earns you a point and marks your thread as Resolved so we will ALL know you have been helped. This will help me, you and others....

How to get RS (From VB6) to .NET (VB.NET)
Hi Friends,         I have requirement, there is one function in VB6 it returns a RS, I need to call that function in  VB.NET, how to get those values into .NET, if can any body can help (pass me some code snippets), that will be greate, Thank you.Madhu... -Madhu hey guys i got this one just by using fill method.-Madhu...

Attach SQL DB to SQL Server 2005 using VB.Net
Is there any way to attach a SQL DB to SQL Server 2005 using VB.Net?  If so can you also set security? Can you rephrase your question? Attach SQL DB from which version of SQL server ? Look up books online for sp_attachdb and sp_detachdb.***********************Dinakar NethiLife is short. Enjoy it.*********************** I have a DB that was created in SQL Server 2005.  I have a website that displays data from systems all over the world.  Each system sends it's data to it's own DB.  The customer can then go to a website and see the data at real time.  What ...

how can i caonvert positive value (in integer) to a negative value?
for example i have the number 4 i want to get the number -4 ? this wayint a = 1; a = -a;MAKMark as Answer if this reply helps youMVP ASP/ASP.NetASP.Net Hosting : Host DepotMy Site : ASPSnippets int a = 4 a= a - a*2 ie. a=4 a*2 = 8 4-8 = - 4  Now a becomes -4ByPT.SivakumarIndia...

Web resources about - Get Scope Identity Value using ObjectDataSource and Vb.Net - asp.net.sql-datasource

Forums Forums Database Error
This is a discussion forum powered by vBulletin. To find out about vBulletin, go to http://www.vbulletin.com/ .

Using The LINQDataSource
-*+There is a new datasource control available in Visual Studio that works in much the same way as the SQLDataSource and the ObjectDataSource. ...

ASP.NET Web Forms DynamicData FieldTemplates for DbGeography Spatial Types
... a FormView in ASP.NET Web Forms. Notice the ItemType is set, rather than using Eval(). We're also using SelectMethod rather than an ObjectDataSource. ...

LINQ Tutorials : LINQ Tutorials, Articles and Resources
Articles and Helpful Tutorials for LINQ and ASP.NET

About Mikesdotnetting
I started this site as somewhere to store answers to bits and pieces that I see asked frequently in the forums at www.asp.net . Rather than type ...

Runnable code for JQuery, Codeigniter, NodeJS, PHP, Python and more - Runnable
Runnable code for dart-vm, express, paypal, google, evernote, everyauth, ejs, rendr, CakePHP, php-mysql, php-facebook, codeigniter, imagemagick, ...

ASP.NET Pivot Table - RadPivotGrid for ASP.NET AJAX
... of all data reports. Try now! Use any declarative data source control (AccessDataSource, SqlDataSource, LinqDataSource, EntityDataSource, ObjectDataSource, ...

Miscellaneous « AJ\’s blog
AJ\’s blog January 28, 2012 2011 in review Filed under: Miscellaneous — ajdotnet @ 7:41 pm The WordPress.com stats helper monkeys prepared a ...

ObjectDataSource - Selectcount method with custom parameters and custom paging.
protected void obds_Selecting(object sender, ObjectDataSourceSelectingEventArgs e) { if (!e.ExecutingSelectCount) { e.InputParameters["filter"] ...

Archive
Archive - Forever Breathes The Lonely Word

Resources last updated: 12/31/2015 6:56:04 PM