473,394 Members | 1,693 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,394 software developers and data experts.

Getting The new Identity Value

I have a VB.NET app in which I am using the following code to add a new row.
ds.Tables("UnitsTable").Rows.Add(dr) ' Add the new row

da.InsertCommand = cmdBuilder.GetInsertCommand

da.Update(ds, "UnitsTable")

At this point I want to get the value of the Identity field that has just
been added to the underlying table ("UnitsTable" is based on a Select
against a table named "Units")
I searched google but the examples I found were using recordsets and I could
not adapt them to work. Pointers to any examples or explinations will be
appreciated.

Wayne
Nov 21 '05 #1
2 2513
For SQL Server, there is a way to send 2 statements separated by a
semi-colon.

The first is your update statement the second is:
Select @@Scope_Identity

So you should be able to set your ID to the returned Identity value if you
send 2 statements.

================================================== ====
For Access and Oracle (and SQL Server if you don't use multiple statements):
You have to trap the RowUpdated event and then post the new identity value.
================================================== ==============
In my update method I have some code like this:

'handle the RowUpdated event to get the Identity value back from SQL Server
'w/o the real Identity value, the child records won't be added to SQL
Server.

AddHandler da_Eimhdr.RowUpdated, AddressOf da_Handle_RowUpdated

'parent table
da_Eimhdr.Update(NewEimhdrRecords)

'child table
da_Eimln.Update(NewEimlnRecords)
================================================== ==============
'this is how to handle the insert of each row:

Private Sub da_Handle_RowUpdated(ByVal sender As Object, ByVal e As
SqlRowUpdatedEventArgs)
If e.Status = UpdateStatus.Continue And e.StatementType =
StatementType.Insert Then
e.Row("eimkey") = GetIdentity(e.Command.Connection)
e.Row.AcceptChanges()

'use this if you do not want to AcceptChanges for each row.
'e.Status = UpdateStatus.SkipCurrentRow
End If
End Sub
================================================== ==============
Private Function GetIdentity(ByRef cnn As SqlConnection) As Integer
Dim oCmd As New SqlCommand("SELECT @@IDENTITY", cnn)
Dim x As Object = oCmd.ExecuteScalar()
Return CInt(x)
End Function
================================================== ==============
--
Joe Fallon


"Wayne Wengert" <wa***************@wengert.com> wrote in message
news:On**************@TK2MSFTNGP11.phx.gbl...
I have a VB.NET app in which I am using the following code to add a new row. ds.Tables("UnitsTable").Rows.Add(dr) ' Add the new row

da.InsertCommand = cmdBuilder.GetInsertCommand

da.Update(ds, "UnitsTable")

At this point I want to get the value of the Identity field that has just
been added to the underlying table ("UnitsTable" is based on a Select
against a table named "Units")
I searched google but the examples I found were using recordsets and I could not adapt them to work. Pointers to any examples or explinations will be
appreciated.

Wayne

Nov 21 '05 #2
Joe;

Thanks for all that information. I used your function approach and that
works fine. I am trying to understand the handler approach and will also try
that to see what I learn.

Wayne

"Joe Fallon" <jf******@nospamtwcny.rr.com> wrote in message
news:%2***************@TK2MSFTNGP09.phx.gbl...
For SQL Server, there is a way to send 2 statements separated by a
semi-colon.

The first is your update statement the second is:
Select @@Scope_Identity

So you should be able to set your ID to the returned Identity value if you
send 2 statements.

================================================== ====
For Access and Oracle (and SQL Server if you don't use multiple statements): You have to trap the RowUpdated event and then post the new identity value. ================================================== ==============
In my update method I have some code like this:

'handle the RowUpdated event to get the Identity value back from SQL Server 'w/o the real Identity value, the child records won't be added to SQL
Server.

AddHandler da_Eimhdr.RowUpdated, AddressOf da_Handle_RowUpdated

'parent table
da_Eimhdr.Update(NewEimhdrRecords)

'child table
da_Eimln.Update(NewEimlnRecords)
================================================== ==============
'this is how to handle the insert of each row:

Private Sub da_Handle_RowUpdated(ByVal sender As Object, ByVal e As
SqlRowUpdatedEventArgs)
If e.Status = UpdateStatus.Continue And e.StatementType =
StatementType.Insert Then
e.Row("eimkey") = GetIdentity(e.Command.Connection)
e.Row.AcceptChanges()

'use this if you do not want to AcceptChanges for each row.
'e.Status = UpdateStatus.SkipCurrentRow
End If
End Sub
================================================== ==============
Private Function GetIdentity(ByRef cnn As SqlConnection) As Integer
Dim oCmd As New SqlCommand("SELECT @@IDENTITY", cnn)
Dim x As Object = oCmd.ExecuteScalar()
Return CInt(x)
End Function
================================================== ==============
--
Joe Fallon


"Wayne Wengert" <wa***************@wengert.com> wrote in message
news:On**************@TK2MSFTNGP11.phx.gbl...
I have a VB.NET app in which I am using the following code to add a new

row.
ds.Tables("UnitsTable").Rows.Add(dr) ' Add the new row

da.InsertCommand = cmdBuilder.GetInsertCommand

da.Update(ds, "UnitsTable")

At this point I want to get the value of the Identity field that has just been added to the underlying table ("UnitsTable" is based on a Select
against a table named "Units")
I searched google but the examples I found were using recordsets and I

could
not adapt them to work. Pointers to any examples or explinations will be
appreciated.

Wayne


Nov 21 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Benny | last post by:
Dear All, Suppose in the program a record is added to a table whose primary key is a identity field. If I really want to get the lastest value for that field after the insertion, is it the best...
2
by: Edward | last post by:
SQL 7.0 I have a form in ASP.NET and I want to write the values to the SQL Server tables. The tables are Customer and Address tables. There will be an insert into the Customer table, and I...
5
by: Eugene | last post by:
I have a table EugeneTest(id_num, fname, minit, lname) where field "id_num" is type IDENTITY and has UNIQUE constraint Let's say 2 user execute this code at the same time: DECLARE @return...
4
by: Nathan Sokalski | last post by:
I am using ASP.NET to insert records into a Microsoft Access Database. My primary keys are of type Autonumber. However, because some of the tables have relationships I need to know the value of the...
3
by: Jason L James | last post by:
Hi all, I recently wrote a vb.net app using oledb to an access database. When I inserted new rows in my datatable the identity column was automatically created. This app used an un-typed...
41
by: pb648174 | last post by:
In a multi-user environment, I would like to get a list of Ids generated, similar to: declare @LastId int select @LastId = Max(Id) From TableMania INSERT INTO TableMania (ColumnA, ColumnB)...
1
by: Brad Eck | last post by:
In Access, newID returns a unique for the table. In SQL Server, newid() returns a GUID - unique in the world. I do not need or desire that complexity. Is there a way to get a simple unique int on...
3
by: Atul | last post by:
Hi, I am running .NET Framework 2.0 on windows XP SP2. I am stuck in a situation where I need to find out a list of all active sessions running in IIS for a web application. I know that .NET...
33
by: JamesB | last post by:
I am writing a service that monitors when a particular app is started. Works, but I need to get the user who is currently logged in, and of course Environment.UserName returns the service logon...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.