Thanks for the great suggestions :o) Thanks, Mark
-----Original Message----- From: Mark A. Kruger - CFG [mailto:[EMAIL PROTECTED]] Sent: Saturday, July 13, 2002 5:59 PM To: CF-Talk Subject: RE: Counter Function 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 ______________________________________________________________________ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. 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
