I always use the ident_current('tablename') function in SQLServer2000,
but then again I'm not likely to change that in the forseeable future.
But another way to do it, that would be independent of database would
be to select based on the criteria you just inserted such as:
<cfquery name="insert" datasource="#request.dsn#">
INSERT into tablename (firstname,lastname,phone,postcode,country)
VALUES
('#form.firstname#','#form.lastname#','#form.phone#','#form.postcode#','#form.country#')
</cfquery>
<cfquery name="getid" datasource="#request.dsn#" maxrows="1">
SELECT CustomerID FROM tablename
WHERE firstname ='#form.firstname#'
AND lastname = '#form.lastname#'
AND phone = '#form.phone#'
AND postcode = '#form.postcode#'
AND country ='#form.country#'
ORDER BY CustomerID DESC
</cfquery>
The maxrows="1" is in case there are more records with the same data
in the table. Another way around that would be to test if
#getid.recordcount# GT '1'
It's clumsy, but it works for all databases and is therefore fully
portable bwtween databases.
Cheers
Mike Kear
Windsor, NSW, Australia
Certified Advanced ColdFusion Developer
AFP Webworks
http://afpwebworks.com
ColdFusion, PHP, ASP, ASP.NET hosting from AUD$15/month
On Mon, 31 Jan 2005 17:21:47 -0500, Dave Watts <[EMAIL PROTECTED]> wrote:
> > > > That's only reliable inside a transaction. Don't leave that
> > > > part out.
> > >
> > > Actually, depending on your database, a transaction may not
> > > be required just as Barney states. For example, if you're
> > > using MS Access, the entire table is locked when you write
> > > to it, so a transaction wouldn't be required.
> >
> > Yes, but wouldn't there be a possibility that someone could
> > sneak in and do an insert between the time that you do the
> > insert and then select for the last auto_increment?
>
> Not if you use the database's own transaction-safe mechanism for selecting
> the ID, such as "SELECT @@IDENTITY ...". That's what Barney was suggesting,
> I think.
>
> Dave Watts, CTO, Fig Leaf Software
> http://www.figleaf.com/
>
> Fig Leaf Software provides the highest caliber vendor-authorized
> instruction at our training centers in Washington DC, Atlanta,
> Chicago, Baltimore, Northern Virginia, or on-site at your location.
> Visit http://training.figleaf.com/ for more information!
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Discover CFTicket - The leading ColdFusion Help Desk and Trouble
Ticket application
http://www.houseoffusion.com/banners/view.cfm?bannerid=48
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:192482
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
