how to determine auto generate key on insert 2

[glendacom]

From an ASP program how do I determine the key to an inserted record where the key is automatically generated by SQL*Server?

 

[Tarwn]

Try doing a search on @@identity for more information.

Basically you are going to do a select @@identity from TableName to get back the identity, or generated key, from the most recently added record.

One thing to consider is if your expecting a heavy load on your system it may be wise to lock the table first, then add your record, query for the identity, then unlock the table. This way you won't run into a problem of two simultaneous inserts happening and then two queries both returning the same primary key.

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

 

[Targol]

a Star for Tarwn for this "clear and short" answer !!! Water is not bad as long as it stays out human body ;-)

 

[JamesLean]

Good advice Tarwn. One thing though, to get the last identity value you don't need to specify the tablename:

INSERT ......

SELECT @@identity

 

[Tarwn]

Sorry, was posting last night before I went to sleep, so not quite awake, thanks for the correction James

(just don't tell onpnt )

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

 

[glendacom]

Thank-you thank-you thank-you a thousand times!!!!!! The @@identity worked! You folks are absolutely wonderful!

For those of you who might want more detailed information here's the code that worked:

<%
    ' define variables
    dim GmRecordSet
    dim GmConnection
    dim sqlGmInsert

    ' set connections and open DB for insert
    set GmConnection  = server.createobject(&quot;adodb.connection&quot;)
    GmConnection.Open WriteStrConnection

    ' build sql to insert one record with the field
    ' SomeValue (an integer in table gmTry) set to 966
    sqlGmInsert = &quot;insert into gmTry (SomeValue) values (966)&quot;

    ' execute insert statement
    GmConnection.Execute sqlGmInsert

    ' associate record set with connection and query
    ' @@identity to get key for last record inserted
    set GmRecordSet  = GmConnection.Execute(&quot;Select @@identity justInserted&quot;)

    response.write &quot;the last record inserted has a key of &quot; & gmRecordSet(&quot;justInserted&quot;)
%>

 

[Mdiaz]

To get your autonumber value from a just updated record can be done as follows:

Blah Blah Blah...

rs.addnew
rs("abc&quot= cba
rs("xyz&quot =zya
rs.update
varString = rs("IndexID&quot


'Since the record has now been added to the database but has not been closed you can retrieve values that were updated. So just ask for the autonumber value or whatever other column value after the update. In this case the auto number column was "IndexID".

Mike Diaz...

 

[glendacom]

Would you mind being more specific about your "blah blah blah"? I have tried your example but cannot get it to work with an Insert. I changed my above example to use a record set for the insert (as opposed to just a connection) and when I query the ID field I get a message that states:

Item cannot be found in the collection corresponding to the requested name or ordinal.

In the meantime the @@identity works fine.


thanks!

 

[onpnt]

I think the blah blah blah was indicating code before insert

Mdiaz
just thinking of the top of my head database design wise and have to say that I've never tried what your mentioning so if I'm wrong please do tell me.
I'll give it a shot after posting to prove myself wrong

in my thinking when you do a insert of data into a database via pretty much any method or tool to do the insert the record is written and you are thus on that row of records. however when you call a event such as update that event will then actually add that row of records and pull the cursor to the next row. this being a EOF or aemtpy row. so in thinking asking for a field of any type at that point would be in error. although a simple moveprevious (if my thinking here is accurate) would effectivly more you to the record just inserted intot he database.

anyhow, off to try it. _______________________________________________
[sub]{ str = "sleep is good for you. sleep gives you the energy you need to function";
ptr = /sleep/gi;Nstr = str.replace(ptr,"coffee"alert(Nstr); }[/sub]
_______________________________________________
for the best results to your questions: FAQ333-2924
has you're questio

 

[Tarwn]

That will work, I generally stay away from it however because using the recordset object's methods to add rows has more overhead.

If anyone wants I'm willing to do another benchmark comparison between using SQL inserts and Rs.addnew's.

Maybe I should start a section on my upcoming resources website for benchmarks

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

 

[Mdiaz]

Here is a snippet:

<%set rs = Server.CreateObject(&quot;ADODB.Recordset&quot
sqlstatement=&quot;Select * FROM table&quot;

rs.Open sqlstatement, &quot;DSN=dbName;UID=;PWD=;&quot;,1,3
rs.AddNew
rs(&quot;column1&quot=request.form(&quot;frField&quot
rs(&quot;column2&quot=request.form(&quot;frmField2&quot
rs.update
intNewID = rs(&quot;indexID&quot
rs.close
set rs = nothing

response.write &quot;New record autonumber is &quot; & intNewID & &quot;<BR>&quot;
%>

Tarwn, very interested in your bench mark comparison. Coding is more of a hobby for me and I'm learning all the time. Thanks for your input.

Mike Diaz...

 

[onpnt]

yup, tried it and it works. not sure if I would do it like that but it worked. still for some reason or another doesn't settle quite right. thats for the mice to work out in my head though

Tarwn, just do it. you know you want to! _______________________________________________
[sub]{ str = &quot;sleep is good for you. sleep gives you the energy you need to function&quot;;
ptr = /sleep/gi;Nstr = str.replace(ptr,&quot;coffee&quot;alert(Nstr); }[/sub]
_______________________________________________
for the best results to your questions: FAQ333-2924
has you're questio

 

[Tarwn]

Heh, fine, tomorrow afternoon instead of working on the resource submission code I will do some more benchmarks and add a new catagory to the resource library

Speaking of, public date is being pushed back, to much work recently to finish the database conversion over to the digital library version, so I'm looking at no later than Valentines day for semi-public release of the site. I'll be sure to post the address so people can try and break it

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