Return primary key after adding a new record

[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")

 

[Richard]

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?

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)

 

[Miha Markic]

Hi Richard,

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

 

[Ron Allen]

See
http://msdn.microsoft.com/library/d...cpconRetrievingIdentityOrAutonumberValues.asp
for this.

Ron Allen

 

[Jason]

Thanks to all that contributed. I now have some info to go forward with.

 

[Jason]

Thanks to all that contributed. I now have some info to go forward with.