Getting ID of new record

[Microbe]

Hey everyone,

When creating a new record, I want to get the ID of that record and put it in a session variable. Obviously, it isn't available until the insert is done.

The way I thought of doing it was to do the insert, then immediately do a descending select and grab the first record.

I can see that this is "dangerous" if the db is heavily accessed as there could be another insert before I do the select.

So, I need to lock the table for a moment. I have the following code:

insertCmd.CommandText = strSQL
insertCmd.LockType = 1
insertCmd.Execute

It looks right, but there is no way to actually test it (or is there?) so I have no certainty.

Or does anyone have an idea of a better way of getting the ID of the new record? Steve Davis
hey.you@hahaha.com.au

http://www.hahaha.com.au

 

[foxbox]

cSql = "set nocount on insert into MyTable " &_
"(MyFieldList) " &_
"values( FieldValues ) " &_
"select @@identity"

Set rs = conn.Execute(cSql)
nID = rs(0)
br
Gerard

 

[Microbe]

hmmm...just thought of a problem there.

So, say I have successfully locked the file, doesn't it unlock itself after the successful insert?

If so, how do I keep it locked while I do a quick select then unlock it when I have the record...

Just thinking more...I suppose I could get the last record and if the userID doesn't match my user I can look at the previous record...this avoids the locking issue...but I would still like to know how to do it and it sounds a lot tidier than searching blindly for the correct record. Steve Davis
hey.you@hahaha.com.au

http://www.hahaha.com.au