get & set unique ID in table after "insert into" sentence

[moroeh]

Hi:

I want to insert into a field in a table the value of the primary key of other table.

This is a 1 to N relationship and I need to make the INSERT INTO of both tables. I have been looking in other threads, and I have found the next sentence:

Set recordset = connection.Execute("select @@IDENTITY&quot
the autonumber = recs.Fields(0)

First of all is that I don´t know if I can use it because some people can be accessing the same page and inserting data at the same time (I mean if I can get the @@IDENTITY value of other customer instead of the one I want), and I dont know if the system itslef can block this from happening.

I am using SQL SERVER 2000.

The INSERT INTO should be like this:
...
HTML
...

<%
...
x = INSERT INTO event (field1,field2,field3) VALUES (1,2,3);

execute x

y = INSERT INTO date(id_event,field4,field5,field6) VALUES (&quot;Here a put the value of the ID from the first INSERT&quot;,4,5,6)

execute y

...
%>

...
HTML
...


Thanks in advance,

moroeh

 

[Tarwn]

You could always do a select back where ther 3 fields are equal to the three values you inserted, that way you insert it then query it back out and have the number you need.
Not exactly the prettiest way to do it, but should be more efficient than using recordset.AddNew or something like that

Another possibility is to use locking. You could put a write lock on the table before you insert. Do your insert, do your select for identity, release the write lock. That way your quaranteed to have been the only one to write to the table between inserting and reading the identity.

-Tarwn ________________________________________________________________________________
Want to get great answers to your Tek-Tips questions? Have a look at faq333-2924

 

[moroeh]

Tarwn:

But, how do you block the specific table while you do the insert and get the @@IDENTITY? I have just found a thread where they state that << the @@IDENTITY returns the last identity value on the same open connection to the database >>

Can it interfiere with other users that are working at the same time? (since always I have had problems with the use of concurrency)

Is enough blocking for writing or do I need to block it for reading also?

moroeh

 

[TonyU]

[tt]
Here's how I've done in the past (twice before)
I insert into my first table, creating a session(&quot;variable&quot of it, then proceeding to my second insert using my new session.

[sup]I haven't lost my mind, it's backedup on CD somewhere[/sup]​

[sup]

ASP Questions? go to:

http://www.aspfaq.com

[/sup]​