Haha.. so, you could just throw a CFIF in there and not even have to mess
with the database. Cool, thanks!
Scott M. Berry
--
College of the Canyons Web Design
661.259.7800 x3630
http://www.coc.cc.ca.us
----- Original Message -----
From: "Jennifer" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, May 02, 2000 12:15 PM
Subject: Re: Getting Key Field after Insert
> At 11:20 AM 5/2/00 -0700, you wrote:
> >Question: Does this initialize itself? If there is no data, will that
query
> >assume a 0 and start off at 1?
>
> No, so what I did was created a dummy record with an id number of one,
> inserted a real record and deleted the dummy. I don't know of any reason
> that the dummy should be numbered one instead of zero; that's just the
> number I picked. Theoretically, you could want the number to start really
> high to make something look more impressive, so you could set the dummy to
> any id number that you want. (Some one I know did this in a site with
> listings similar to personal ads. Since the id numbers started at 10,000
> and the id number was a URL parameter, it gave the illusion of 10,000
> previously successful ads. Of course, it was just a sneaky trick.)
>
> I guess you could seed the ID within the code. You could set an ID seed to
> one (or whatever you wanted as the seed) and if there are no records
> returned (LastID wouldn't be valid, so you couldn't add to it) you could
> set the ID number to the seed. Then, any records after that would be built
> on the record with the ID number of the seed. So you would want to do
this:
>
> <cfset IDSeed = 1>
>
> <cftransaction>
> <cfquery name="GetID" datasource="#application.Dsn#">
> SELECT MAX(UserID) as LastId
> FROM table;
> </cfquery>
>
> <cfif IsDefined('GetID.RecordCount') and GetID.RecordCount GTE 1>
> <cfset form.UserID=(GetId.LastID + 1)>
> <cfelse>
> <cfset form.UserID=IDSeed>
> </cfif>
>
> <cfquery datasource="#application.Dsn#">
> insert into table
> (column) values (#column#');
> </cfquery>
> </cftransaction>
>
> I haven't actually tried this; I just thought of it. But I think it would
> work pretty nicely.
>
>
> >I like this much better then using the Key in access, in that in Access
it
> >is a HARD key.. once a record is deleted, the key won't be reused and I
have
> >gaps in my listings. This method is a real world implementation, nice
code.
>
> Well, I can't take credit for the code, but you should realize that the id
> numbers for deleted records won't be reused unless the deleted records had
> the largest ID numbers. However, if you want to clean up the id numbers
> after a while, you can set up a program to renumber them starting with
some
> number.
>
>
> >Scott M. Berry
> >--
> >College of the Canyons Web Design
> >661.259.7800 x3630
> >http://www.coc.cc.ca.us
> >
> >----- Original Message -----
> >From: "Jennifer" <[EMAIL PROTECTED]>
> >To: <[EMAIL PROTECTED]>
> >Sent: Tuesday, May 02, 2000 10:48 AM
> >Subject: Re: Getting Key Field after Insert
> >
> >
> > > At 12:54 PM 5/2/00 -0400, you wrote:
> > > >I am using Microsoft Access Autonumber to create Key fields for me.
> > > >
> > > >1. Is this a wise thing to do?
> > > >2. If ok , anyone have a nice way of getting the key back. I am
> >currently
> > > >saving a temporary key in one the fields, then finding and changing.
> >This
> > > >seams like a lot of work to create an Insert.
> > > >3. If not, and this might explain all of the discussion about key
> >creation,
> > > >what was the consensous on the best way to generate a unique key?
> > >
> > > I generate keys in the page. That way, I don't have to search the
database
> > > for the key-- I already have it. For this method, it is extremely
> >important
> > > that you use CFTransaction. Otherwise you might get errors.
> > >
> > > <cftransaction>
> > > <cfquery name="GetID"
> >datasource="#application.Dsn#">
> > > SELECT MAX(UserID) as LastId
> > > FROM table;
> > > </cfquery>
> > >
> > > <cfset form.UserID=(GetId.LastID + 1)>
> > >
> > > <cfquery datasource="#application.Dsn#">
> > > insert into table
> > > (column) values (#column#');
> > > </cfquery>
> > >
> > > </cftransaction>
> > >
> > > This code selects the largest ID number, adds one to it and sets that
as
> > > the ID number of the new record. If you don't put this code in
> > > cftransaction, multiple simultaneous users could send records with the
> >same
> > > id number. This method was recommended to me by Mr GuruMan Raymond
Camden,
> > > and I've seen it mentioned here many times. It seems to be a
relatively
> > > standard method. I haven't had any problems with it.
> >
> --------------------------------------------------------------------------
> >----
> > > Archives: http://www.eGroups.com/list/cf-talk
> > > To Unsubscribe visit
> >http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk
or
> >send a message to [EMAIL PROTECTED] with 'unsubscribe' in
> >the body.
> > >
> >
>
>---------------------------------------------------------------------------
---
> >Archives: http://www.eGroups.com/list/cf-talk
> >To Unsubscribe visit
> >http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk
or
> >send a message to [EMAIL PROTECTED] with 'unsubscribe' in
> >the body.
>
> --------------------------------------------------------------------------
----
> Archives: http://www.eGroups.com/list/cf-talk
> To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
send a message to [EMAIL PROTECTED] with 'unsubscribe' in
the body.
>
------------------------------------------------------------------------------
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
