Return primary key after adding a new record

  • Thread starter Thread starter z
  • Start date Start date
Z

z

I have a table that has a primary key field that autonumbers. I want to be
able to add a record to the table and know what the added records primary
key value is. The value would then be used to add children records to
another table. How do I go about achieving this?

In VB 6 and ADO, this was possible using recordsets in a connected manner
e.g.
dim rsVideo as Recordset
dim strSQL as String
dim intVideoID as Integer

Set rsVideo=New Recordset
strSQL="SELECT * FROM Video WHERE VideoID=0"
rsVideo.Open strSQL cnVideo, , adLockOptimistic

rsVideo.AddNew
rsVideo.Fields("Title")="The Matrix"
rsVideo.Fields("Studio")="Village Roadshow"
rsVideo.Update
intVideoID=rsVideo.Fields("VideoID")
 
z said:
I have a table that has a primary key field that autonumbers. I want
to be able to add a record to the table and know what the added
records primary key value is. The value would then be used to add
children records to another table. How do I go about achieving this?
Click to expand...

If you are using SQLServer (or access) you will need to execute a
"SELECT @@IDENTITY"
and get the value of the last autonumber used.

Other databases have different ways of returning the value.

--
Regards,
Richard

http://jcframework.sourceforge.net
(Open Source Object Relational Persistence for .NET and VB6)
 
Hi Richard,

Actually, the correct statament for Sql Server is SELECT SCOPE_IDENTITY()
 
Back
Top