Get ID (primary key) immediately on inserting new record.

[mokaplanjr]

I need to be able to pull data from a new record at the time it is inserted. I want to be able to insert the record and on the action page create a hyperlink that queries the new record for it's primary key.

I know how to create the hyperlink query, but I can't find a way to get the ID field from the new record right on the insert page action page. I think if there is a way to pull the data from whatever is the newest record in the table, that would do it, but the books aren't very helpful (and not nearly as useful as this forum.)

 

[CFHub]

Try this tutorial on "transaction" proccessing. With this method you force both your insert statments to be related and can "rollback" your initial insert if the second one fails.

It also introduces the concept of @@identity. Good for Access and SQL databases, this value holds the "new" key generated in a table.

 

[mokaplanjr]

Got hyperlink?

 

[rmz8]

<CFQUERY DATASOURCE=&quot;#DSN#&quot; NAME=&quot;getID&quot; MAXROWS=&quot;1&quot;>SELECT ID FROM Users ASC</CFQUERY>

Now, to refer to the value, set a variable with something like this:

<CFSET NewID=&quot;#Val(ID + 1)#&quot;>

and use the variable NewID within your template. In the QUERY above, ID refers to the primary key field.

This basically takes the last value that was entered into the database under the ID field and adds 1 to it. It is always effective unless you are constantly deleting records from the database.

Ryan ;-]

 

[rmz8]

Ohh, if you want to grab the value after it has been inserted, there is no need to add 1. Simply do this:

<CFQUERY DATASOURCE=&quot;#DSN#&quot; NAME=&quot;getID&quot; MAXROWS=&quot;1&quot;>SELECT ID FROM Users ASC</CFQUERY>

and refer to the variable ID within your template.

Ryan ;-]

 

[timgill]

The only problem with the previous technique is that if you have many people using the database at the same time, a new record could be slipped in between you adding a record, and running the above code to retrieve the ID.

Using CFTransaction to do the insert, and then using select @@identity is the best way to go to be sure.

Example:
<cftransaction action=&quot;BEGIN&quot;>
<cftry>
<cfquery name=&quot;qryInsert1&quot; ...>
INSERT dbo.tblSessions( SessionName)
VALUES ('TEST')
</cfquery>

<cfquery name=&quot;qryInsert2&quot;...>
select @@identity AS id
</cfquery>
<!--- You can no refer to the new ID as #qryInsert2.ID# --->
..... Any other processing here ....


<!--- If you made it this far, commit the records --->
<cftransaction action=&quot;COMMIT&quot;></cftransaction>
<!--- Catch any database errors --->
<cfcatch type=&quot;database&quot;>
Run your error routine, whatever it is, here
<!--- Rollback the transaction so the records are not inserted --->
<cftransaction action=&quot;ROLLBACK&quot;></cftransaction>
</cfcatch>
</cftry>
</cftransaction>

Hope that helps.
Tim Gill
Gill Consulting

http://www.dagills.com