ASP: Retrieve autonumber field on INSERT

[ben.werdmuller]

Hi,

Is there an easy way in ASP/VBscript to grab an autonumber (primary
key) field just after an SQL insert? This is probably easy, but I'm
stuck ..

Cheers.

[shumaker]

I think you would have to do a query to pull up the the record after
it's inserted, because the SQL insert statement is like a for loop in
that you can't insert records one at a time and then examine the
results such as an autonumber field.

This would be somewhat hackish IMHO. You could literally insert
records one at a time, and just before you insert the record, save it's
values locally, and after you insert, then do a query to find that
record(assuming you can find some combination of values that will be
unique) and examine it's autonumber field. I don't know alot about
SQL, but this would probably run much slower than an insert statement
that isnerts all the records as a batch.

Perhaps you should consider generating the primary key yourself? So
that you know what it is. Instead of having an autonumber field, setup
a primary key field that is some concatination of other fields or just
a number you generate based on what numbers already don't exist in that
column. This is similar to using multiple primary keys, but without
dealing with seperate keys. Even with 2 primary keys then normally
complicated queries get super complicated(at least for me).

I'm a noob BTW, so take my suggestions with a grain of salt.

[shumaker]

"SQL insert statement is like a for loop in"
This should have read:
"SQL insert statement is not like a for loop in"

And I'm referring to an isenrt statement that inserts multiple records
in one statement. You can't somehow look at the results of each record
insert as the insert statement is running. That's why you would need
to make you're own for loop. But I think making your own for loop is
fvery hackish round about way of solving the problem. I really think
you should somehow generate a fully deterministic primary key if it is
important you know what the primary key is of a record when it is
inserted, rather than using an autonumber field.

[RoyVidar]

Hi!

You probably have a connection variable,
on which you execute the insert. Use
select @@identity on it:

cnn.execute "your insert....."
set rs=cnn.execute("select @@identity",,adcmdtext)
msgbox rs.fields(0).value

I don't know much about script syntax,
but the above should work in VBA, given
rs is declared as adodb.recordset.

[e-mail] wrote:

Hi,

Is there an easy way in ASP/VBscript to grab an autonumber (primary
key) field just after an SQL insert? This is probably easy, but I'm
stuck ..

Cheers.