Returning identity value when inserting new data

osani

Member
Joined
Apr 7, 2005
Messages
14
Programming Experience
1-3
Hi all,

My database is set up to assign a bigInt identity data type to the record when a new person is entered. The identity starts at 11000 and is incremented by 1.

I have 4 tables that are relationally joined so when a new record is entered into the main table a record with the same identity value needs to be entered into the other tables.

I want to assure that the same number is being entered into each table for the same person. Is there a way to return the identity value and then insert it into the other tables?

Thanks.
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,321
Location
Sydney, Australia
Programming Experience
10+

ManicCW

Well-known member
Joined
Jul 21, 2005
Messages
428
Location
Mostar
Programming Experience
Beginner
How to return ID value

After calling executenonquery on insert command use:

Dim selectSQL AsString = "SELECT @@Identity FROM Table"
Dim selectSQLCmd As SqlCommand = New SqlCommand(selectSQL, connObject)
Dim ID as Int = selectSQLCmd.ExecuteScalar
 

TechGnome

Well-known member
Joined
May 23, 2005
Messages
896
Programming Experience
10+
I'd like to follow that up with: And if someone ELSE manages to insert a record BEFORE you can SELECT @@Identity, you'll get theirs.

If it's all done inside a stored procedure it isn't such a big deal, but if you have to make a second trip back to the database, then it becomes a risk.

Tg
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,321
Location
Sydney, Australia
Programming Experience
10+
There is also SCOPE_IDENTITY and IDENT_CURRENT that have narrower scope than @@IDENTITY. If you are not using Access, I think you should also be able to use an output parameter and multiple SQL statements in the one command to get the ID for each row inserted.
 
Top Bottom