Datebase Question!

Database question. I have a insert statment. I'm inserting a new record in the table. The table has a primary key (empID) Here is the code <cftransaction action="BEGIN"> <cfquery name="addNewEmp" datasource="db"> INSERT INTO ASSETS (   empName,   empDesk,   empAsset ) VALUES (   'blog',   '1,   'ast10' ) </cfquery> </cftransaction> I want to retreive the primary key (empID) value immediately after this statement. I tried this: <cfoutput>#addNewEmp.empID#</cfoutput> but get an error msg "Element empID is undefined in addNewEmp Can somebody please help Best Regards, cfcoder         __________________________________ Do you Yahoo!? Friends.  Fun.  Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/

----- Excess quoted text cut - see Original Post for more ----- You need to look into scope_identity() (if you're using SQL Server 2000) or @@identity (for SQL Server 7) Set nocount on Insert into table (fields) Values (values) Select scope_identity() as myID Set nocount on This will "return" the newly added identity field The problem with @@identity is that it returns the "last" identity inserted, so if you have triggers on your table which add to another table, then you will end up with the wrong identity field

What database is it? SQL/Access/MySQL? Database question. I have a insert statment. I'm inserting a new record in the table. The table has a primary key (empID) Here is the code <cftransaction action="BEGIN"> <cfquery name="addNewEmp" datasource="db"> INSERT INTO ASSETS (   empName,   empDesk,   empAsset ) VALUES (   'blog',   '1,   'ast10' ) </cfquery> </cftransaction> I want to retreive the primary key (empID) value immediately after this statement. I tried this: <cfoutput>#addNewEmp.empID#</cfoutput> but get an error msg "Element empID is undefined in addNewEmp Can somebody please help Best Regards, cfcoder         __________________________________ Do you Yahoo!? Friends.  Fun.  Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/

SQL

In Microsoft SQL server you can do something like: INSERT INTO table (col1, col2) VALUES 'val1', 'val2') SELECT thisUserID = @@IDENTITY SET NOCOUNT OFF; I hope that helps > > SQL

Thanks so much Mark and Philip for your help. It works best regards, cfcoder