what is the best way to handle?

[2onboard]

When information is submited into a DB, an autonumber field creates the next available number (to be used as an ID). How can I best find this newly created autonumber so I can use it in another DB entry (a different table).

[Dan_Bracuk]

depends on the db software.

[Kapitaine]

Insert your new record into the table with CFQUERY. Next, do a lookup on that table using MAX() function in SQL to grab the highest ID (the last one inserted). Then hold that into a variable and use that to insert into another table.

Use transactional processing and locking as required by using the CFTRANSACTION and CFLOCK tags. This will hopefully ensure that you are not grabbing the wrong ID.

Hope this helps.

Mikey.

[Newsgroup_User]

> When information is submited into a DB, an autonumber field creates the next
> available number (to be used as an ID). How can I best find this newly created
> autonumber so I can use it in another DB entry (a different table).

You might be as well off asking DB-specific questions on a DB-specific
forum, as the question isn't really related to CF, per-se.

As Dan said: it's difficult to answer your question based on the info
you've given us.

Solutions to this for various DB systems have been asked and answered on
here before... it might be an idea to search and see if the ground has
already been covered. Generally using google to search these forums rather
than its own search engine works better.

--
Adam

[SafariTECH]

I know it's been a while since you were asking about this, but as long as your DB table has a field that is set to be an identifier and is autonumbering, you can simply add a SELECT request (to the DBMS identity scope) to the current INSERT request and pull the ID that was assigned to the latest insert ... it will eliminate a new quesry as suggested above.

So your general query would look something like :

<CFQUERY datasource="DBsrc" name="queryName">
INSERT into TABLE (x,y,z)
VALUES ('1','2','3')

SELECT SCOPE_IDENTITY() as TheID
</CFQUERY>

You would then call the variable as #queryName.TheID#

[ProjectedSurplus]

1.) MySQL has a LAST_INSERT_ID() function -- I'm not sure if other db's have something similar ( http://dev.mysql.com/doc/refman/5.0/en/getting-unique-id.html: LAST_INSERT_ID(expr)
Returns the last automatically generated value that was inserted into an AUTO_INCREMENT column.

2.) If you have a different UNIQUE KEY in your table you can use a select statement to find the autofield number where its value = that of the unique data just inserted

[pflynn02]

Depending on what version of CF (i know this is on 8) you are using, you can do a dump of whatever the name of your insert query was named and there should be a variable that will tell you the ID of the record you just inserted. Im not sure of the name of the variable off hand.