How does this approach improve upon pulling the max id after an insert? If
<cftransaction> will keep two requests from pulling the same NextNbr before
the first request has updated it, why won't it work with the max id method?
The max id method is much cleaner without the need for a work around.
Maybe I missed something . . . if so . . . whoops . . .
Dan
-----Original Message-----
From: Karl Simanonok [mailto:[EMAIL PROTECTED]]
Sent: Monday, July 24, 2000 10:29 AM
To: [EMAIL PROTECTED]
Subject: RE: Newbie needs a little PRO help
Both of these approaches suggested will usually work but both are prone to
occasional failure. There have been several posts here dealing with the
same problem in SQL Server, in
which the best solution has been to use a stored procedure to return the
identity value of the PK field just inserted. Since you can't do that in
Access, here's another method that's
almost as good and far more reliable than either method suggested so far:
Create a new table Next_Nbr with two fields: FieldName and NextNbr.
FieldName is a text field and the PK, use it to hold the name of any PK
fields in tables you want to run INSERT
queries on. NextNbr is a numeric field, not autonumbering, and so should be
the PK fields in the tables you're INSERTing new records into. Write a
little CF routine (and turn it
into a custom tag if you'll be using it a lot) that first queries the
Next_Nbr table to get the next number for the appropriate field when you
want to insert a new record into a
table, and then increments the number in the Next_Nbr field by one. Then
you just use the value you grabbed from the Next_Nbr table in your INSERT
query and for any other processing
you need after the INSERT query is done. Put the queries to Next_Nbr and
your other queries inside a CFTRANSACTION tag so they'll all roll back if
there's any problem.
This method has worked very well for me in many situations. Unfortunately I
can't post the code because I didn't write it and don't have permission to
give it away, but it's very
easy to put together once you understand the basic concept. Once when doing
some work for a client where it wasn't possible to add a new table to their
database, I modified the
routine to update a number in a text file instead of a NextNbr table, and
that worked well too, though it wasn't the best option from a performance
perspective. Still, it beats the
hell out of hoping that there have been no other INSERTs before you do a
SELECT Max(ID) on a table or that a #content_just_submitted# value doesn't
match a #content_submitted_earlier#
record somewhere.
Regards,
Karl Simanonok
Original Messages:
============
Date: Sun, 23 Jul 2000 01:45:22 -0600
From: "Stephen R. Cassady" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Subject: Newbie needs a little PRO help
Message-ID: <000001bff479$f5894f20$[EMAIL PROTECTED]>
Isn't there a problem there with
> SELECT Max(ID) AS mid FROM Table_Name
in a busy database? As in somebody may have submitted another record in the
microsecond that elapsed between the post and the search of the ID?
Wouldn't it be better to
Select ID
>From Table_Name
Where BlahColumn Like "#content_just_submitted#"
(this works best where #content_just_submitted#" is a text field, or, add a
couple AND BlahColumn2 = "content2" AND Blah Column3 = ... ... ...
Stephen R. Cassady
Publisher, Spank! Youth Culture Online
http://www.spankmag.com
[EMAIL PROTECTED]
Date: Sat, 22 Jul 2000 16:45:09 -0700
From: "Mike Weaver" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Subject: Re: Newbie needs a little PRO help
Message-ID: <022c01bff436$df7102c0$31ba3dd0@main>
Thanks very much. That was it!!!
Mike
----- Original Message -----
From: "Stewart, Mark" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Saturday, July 22, 2000 2:26 PM
Subject: RE: Newbie needs a little PRO help
> If I understand you correctly, you need the "autonumber" that was just
> created when you inserted data into your db. If this is correct, try this:
>
> <cfquery name="name" datasource="datasource">
> SELECT Max(ID) AS mid FROM Table_Name
> </cfquery>
>
> Hope that helps...
>
> Mark
>
> -----Original Message-----
> From: Mike Weaver [mailto:[EMAIL PROTECTED]]
> Sent: Saturday, July 22, 2000 7:20 PM
> To: [EMAIL PROTECTED]
> Subject: Newbie needs a little PRO help
>
>
> This is a multi-part message in MIME format.
>
> ------=_NextPart_000_01FC_01BFF3F8.AC1EC580
> Content-Type: text/plain;
> charset="iso-8859-1"
> Content-Transfer-Encoding: quoted-printable
>
> I am developing an application which is going pretty well. I have =
> stumbled at a point where I need a query to generate the last entry in =
> an access database. I need the last entry in the "auto number" field =
> that is generated each time you make an entry. All assistance =
> appreciated.
>
> Mike
------------------------------
Date: Sun, 23 Jul 2000 01:48:00 -0600
From: "Stephen R. Cassady" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Subject: Newbie needs a little PRO help
Message-ID: <000101bff47a$54be2c40$[EMAIL PROTECTED]>
Or, would you lock the tables used in the set of queries that include the
> SELECT Max(ID) AS mid FROM Table_Name?
Sorry, still thinking aloud.
Stephen R. Cassady
Publisher, Spank! Youth Culture Online
http://www.spankmag.com
[EMAIL PROTECTED]
> If I understand you correctly, you need the "autonumber" that was just
> created when you inserted data into your db. If this is correct, try this:
>
> <cfquery name="name" datasource="datasource">
> SELECT Max(ID) AS mid FROM Table_Name
> </cfquery>
>
> Hope that helps...
>
> Mark
----------------------------------------------------------------------------
--
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
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.mail-archive.com/cf-talk@houseoffusion.com/
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.
