Mark,
First - let the DB do the work for you. If you are using access, MS SQL or
MySQL there is a column type with a property that does this for you. In the
case of access its called "autonumber" (or "counter" if you are doing DDL),
in the case of Mssql it's an "identity" property that you set in EM (or w/
ddl it's ...myColumn int identity(1,1) ), and with MySQL I believe it's
called an "autoincrement" (not sure on that one). When you do the insert
you insert into all the columns EXCEPT the identity column - then you can
retrieve it for as the PK value of your new record. Here's an MSSQL
example:
Create table myTable (tb_id int identity(1,1), col1 char(10), col2 char(20)
col3 smalldatetime)
<cfquery name="Put" datasource="mydsn">
Insert into myTable (col1, col2, col3)
values ('hello', 'world', getdate())
SELECT @@Identity AS new_id
</cfquery>
Your database now contains 1, hello, world, 07/12/2002
The value of #Put.New_id# is 1
If you don't have an option of this special type of column property then you
are going to do as you expected.
<cfquery name="getNewID" datasource="myDsn">
SELECT MAX(tb_id) + 1 AS new_id
</cfquery>
<cfquery name="Put" datasource="mydsn">
Insert into myTable (tb_id, col1, col2, col3)
values (#getNewId.new_id#, 'hello', 'world', getdate())
</cfquery>
hope this helps. good luck
-----Original Message-----
From: Mark Leder [mailto:[EMAIL PROTECTED]]
Sent: Saturday, July 13, 2002 4:42 PM
To: CF-Talk
Subject: Counter Function
I want to do an insert into a db of a query row incremented by one?
I know how to retrieve the MAX row from the db, but how do I write the
function that adds 1 to the value.
For example, query MAX returns a value of 1000.
Then I want to CFSET newvalue = 1000 + 1>
Thanks,
Mark
______________________________________________________________________
Get the mailserver that powers this list at http://www.coolfusion.com
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
