ID of Record just created?

Forum|Forum|17 years ago
January 14, 2009
3 replies
1676 views

Hello all,

Is it possible to get the record ID of a record you just created with the CFInsert tag?
For example in the Query :

<CFQUERY name="AddRoommate" datasource="movingdocs">
INSERT INTO
roommates
(
userID,
listingtitle,
name,
email,
country,
stateprovince,
city,
dateadded

)
VALUES
(
<cfqueryparam value="#Form.UserID#" cfsqltype="CF_SQL_INTEGER" maxlength="50">,
<CFIF Trim(Form.listingtitle) IS NOT "">
<cfqueryparam value="#Form.listingtitle#" cfsqltype="CF_SQL_VARCHAR">,
<CFELSE>
<cfqueryparam value="" null="Yes" cfsqltype="CF_SQL_VARCHAR">,
</CFIF>

<CFIF Trim(Form.name) IS NOT "">
<cfqueryparam value="#Form.name#" cfsqltype="CF_SQL_VARCHAR">,
<CFELSE>
<cfqueryparam value="" null="Yes" cfsqltype="CF_SQL_VARCHAR">,
</CFIF>

<CFIF Trim(Form.email) IS NOT "">
<cfqueryparam value="#Form.email#" cfsqltype="CF_SQL_VARCHAR">,
<CFELSE>
<cfqueryparam value="" null="Yes" cfsqltype="CF_SQL_VARCHAR">,
</CFIF>

<CFIF Trim(Form.country) IS NOT "">
<cfqueryparam value="#Form.country#" cfsqltype="CF_SQL_VARCHAR">,
<CFELSE>
<cfqueryparam value="" null="Yes" cfsqltype="CF_SQL_VARCHAR">,
</CFIF>

<CFIF Trim(Form.state) IS NOT "">
<cfqueryparam value="#Form.state#" cfsqltype="CF_SQL_VARCHAR">,
<CFELSE>
<cfqueryparam value="" null="Yes" cfsqltype="CF_SQL_VARCHAR">,
</CFIF>

<CFIF Trim(Form.city) IS NOT "">
<cfqueryparam value="#Form.city#" cfsqltype="CF_SQL_VARCHAR">,
<CFELSE>
<cfqueryparam value="" null="Yes" cfsqltype="CF_SQL_VARCHAR">,
</CFIF>

<cfqueryparam value=#CreateODBCDate(now())# cfsqltype="CF_SQL_DATE">

)
</CFQUERY>

// Can I someone send the Record ID to another page immediately after?
// This does not work obviously :)

<cflocation url="postrmimage.cfm?ID=#AddRoommate.ID#">

THanks!
Tim

Database access

This topic has been closed for replies.

D

Dan_Bracuk

Inspiring

With MS Access, it's

select max(id)
from thetable
where as many possible conditions you can think of are met

If you have access to the db, and you think it's worth the time and effort, you could always add a char (33) field, populate it, and make it the primary key. If there are foreign keys involved, the amount of work would be more.

It it was up to me, I'd use the select max query

K

Kronin555

Participating Frequently

If you go the select max(id) route, make sure you wrap your <cfquery..>INSERT INTO...</cfquery> and <cfquery>SELECT MAX(id) FROM ...</cfquery> in a <cftransaction>, otherwise you might not get the correct ID. This also removes the need to have WHERE criteria, you can just do SELECT MAX(id) FROM mytable.

<cftransaction>
<cfquery..>
INSERT
...
</cfquery>
<cfquery..>
SELECT MAX(ID) FROM ...
</cfquery>
</cftransaction>

D

Dan_Bracuk

Inspiring

There are a variety of ways to do this and some are database specific. One way that will always work is:

Make your id column char (33) instead of numeric.
Create a cold fusion variable of a UUID without any hyphens and use it for your id field.

T

tromanNMVAuthor

Known Participant

Thanks for the responses guys,

Apparently #yourResultName.IDENTITYCOL# only works with SQL Server. :(
And for this job, they are using MS Access. Must remember that for later though :)

Also, the ID field is currently a Primary Key in their database table, so not sure about changing that to char(33)...

:(
Tim

-

-__cfSearching__-

Inspiring

> Is it possible to get the record ID of a record you just
> created with the CFInsert tag?

I do not use CFINSERT, but I do not think so. In ColdFusion 8, you can use cfquery's "result" attribute to return the newly created id in a structure. The name of id column varies by database. For ms sql its

#yourResultName.IDENTITYCOL#

http://livedocs.adobe.com/coldfusion/8/htmldocs/Tags_p-q_17.html#1102316

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded