Or you can SELECT MAX(id) FROM mytable before you run the query to INSERT
INTO mytable.
Marianne Daye
Programmer/Analyst
Information Delivery Systems (IDS)
http://ids.rti.org
-----Original Message-----
From: Tony Schreiber [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, March 20, 2001 12:11 PM
To: CF-Talk
Subject: RE: incrementer
I've always heard bad things about auto-number fields, but I use them
anyway...
But one thing I've seen is to make a table in your database, call it
nextid or something and put two columns, tablename and id. Store the next
id for each table in the id column...Update it within a transaction after
any insert.
So when you go to insert:
insert into mytable (id,col1,col2)
values ((select id from nextid where tablename = mytable),col1val,col2val)
then:
update nextid
setup id = id + 1
where tablename = mytable
> depend on what database you are writing to: for example if access
> then use autonumber and make the id the primary key - this will increment
> automatically for you - if using MS Squirrel then when creating the table
> choose integer as datatype, untick allow nulls and tick identity -
identity
> seed and identity increment should then both have 1 in - etc. etc.
>
> Also, I don't think that your code will work as you should not be able to
> read and insert simultaneously from a table (?) - but the idea is good -
if
> you can't get your datasource to increment for you (which you should let
it
> do if it can) then query the table first with your select max stuff -
create
> a cfset which increments the maxid by one and stuff that variable into
your
> code at "id" instead - though I'm sure others will tell you a neater way
of
> doing it without using two queries!!! ;)
>
> James
>
> "The Force is strong in this one..." - Darth Vader
>
>
> -----Original Message-----
> From: Savan Thongvanh [mailto:[EMAIL PROTECTED]]
> Sent: 20 March 2001 16:25
> To: CF-Talk
> Subject: incrementer
>
>
>
>
> seems like a gimme but...is
> where id is integer and the primary key
>
> insert into mytable (id,col1,col2) values ((select max(id) from
> mytable+1),col1val,col2val)
>
> can you do that and is it a legit way to get a primary key?
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
