freelanceprogrammers.org Forum Index » Cold Fusion
getting information from added record
Joined: 27 May 2006
Posts: 2
getting information from added record
Hola, I`ve ran into a bit of a snag in one of my projects. I`m adding
information to a database (see code below). Is there any way to get a
reference point to that record in the database after it`s been
submitted? There is an autonumbered field called SLAID that I`d like
to reference further in the code after the database entry has been added.
<cfquery name="InsertSLA" datasource="testDB">
INSERT INTO SLAFile
(Department, Sla_Status)
VALUES
(`#form.Department#`, `0`)
</cfquery>
Any help is appreciated.
Joined: 28 May 2006
Posts: 1
getting information from added record
Jeffrey,
Depending on the DB you are using, you may or not be able to
retrieve that... however there is an alternative method.
First, for mysql, the following inside the query:
SELECT last_insert_id()
For mssql, try the following:
SELECT @@Identity as lastID
an alternative method would be two queries, wrapped in a
CFTRANSACTION tag. the first to insert, the second to select with a
maxrows attribute of 1, ie:
cftransaction
cfquery
INSERT INTO myTable (field1,field2,field3)
end cfquery
cfquery
SELECT myID FROM myTable ORDER BY myID DESC
end cfquery
end cftransaction
V/r,
Charles
--- In cold_fusion@yahoogroups.com, "Jeffrey" <blackberry@...> wrote:
>
> Hola, I`ve ran into a bit of a snag in one of my projects. I`m
adding
> information to a database (see code below). Is there any way to
get a
> reference point to that record in the database after it`s been
> submitted? There is an autonumbered field called SLAID that I`d
like
> to reference further in the code after the database entry has
been added.
>
> <cfquery name="InsertSLA" datasource="testDB">
> INSERT INTO SLAFile
> (Department, Sla_Status)
> VALUES
> (`#form.Department#`, `0`)
> </cfquery>
>
> Any help is appreciated.
>
Joined: 27 May 2006
Posts: 2
getting information from added record
Thanks for the real quick response Charles,
Would that not cause some type of collision if two users were adding a
record to the database close together?
Currently it`s an access database; however, once I`m more comfortable
with things... I plan to tackle moving it to mysql. If you have any
suggestions in that direction too. I`m thrilled to hear `em.
--- In cold_fusion@yahoogroups.com, "Charles E. Pelkey" <pelkeyc@...>
wrote:
>
> Jeffrey,
>
> Depending on the DB you are using, you may or not be able to
> retrieve that... however there is an alternative method.
>
> First, for mysql, the following inside the query:
> SELECT last_insert_id()
>
> For mssql, try the following:
> SELECT @@Identity as lastID
>
> an alternative method would be two queries, wrapped in a
> CFTRANSACTION tag. the first to insert, the second to select with a
> maxrows attribute of 1, ie:
>
> cftransaction
> cfquery
> INSERT INTO myTable (field1,field2,field3)
> end cfquery
> cfquery
> SELECT myID FROM myTable ORDER BY myID DESC
> end cfquery
> end cftransaction
>
> V/r,
> Charles
> --- In cold_fusion@yahoogroups.com, "Jeffrey" <blackberry@> wrote:
> >
> > Hola, I`ve ran into a bit of a snag in one of my projects. I`m
> adding
> > information to a database (see code below). Is there any way to
> get a
> > reference point to that record in the database after it`s been
> > submitted? There is an autonumbered field called SLAID that I`d
> like
> > to reference further in the code after the database entry has
> been added.
> >
> > <cfquery name="InsertSLA" datasource="testDB">
> > INSERT INTO SLAFile
> > (Department, Sla_Status)
> > VALUES
> > (`#form.Department#`, `0`)
> > </cfquery>
> >
> > Any help is appreciated.
> >
>
Joined: 03 Jun 2006
Posts: 1
getting information from added record
Please remember, @@Identity only works for SQL Server as far as I know.
I have gotten into the practice of creating sequence tables which
basically hold the table names of all the tables in the database, and
whenever I am inserting, I increment the corresponding record, return
the new value, and that becomes my unique ID for the table. Hence, I
know the ID before I insert, so if I need that ID for related records I
have it. This practice makes the @@Identity like routine compatible to
all databases.
Jeffrey wrote:
>Thanks for the real quick response Charles,
>
>Would that not cause some type of collision if two users were adding a
>record to the database close together?
>
>Currently it`s an access database; however, once I`m more comfortable
>with things... I plan to tackle moving it to mysql. If you have any
>suggestions in that direction too. I`m thrilled to hear `em.
>
>--- In cold_fusion@yahoogroups.com, "Charles E. Pelkey" <pelkeyc@...>
>wrote:
> >
> > Jeffrey,
> >
> > Depending on the DB you are using, you may or not be able to
> > retrieve that... however there is an alternative method.
> >
> > First, for mysql, the following inside the query:
> > SELECT last_insert_id()
> >
> > For mssql, try the following:
> > SELECT @@Identity as lastID
> >
> > an alternative method would be two queries, wrapped in a
> > CFTRANSACTION tag. the first to insert, the second to select with a
> > maxrows attribute of 1, ie:
> >
> > cftransaction
> > cfquery
> > INSERT INTO myTable (field1,field2,field3)
> > end cfquery
> > cfquery
> > SELECT myID FROM myTable ORDER BY myID DESC
> > end cfquery
> > end cftransaction
> >
> > V/r,
> > Charles
> > --- In cold_fusion@yahoogroups.com, "Jeffrey" <blackberry@> wrote:
> > >
> > > Hola, I`ve ran into a bit of a snag in one of my projects. I`m
> > adding
> > > information to a database (see code below). Is there any way to
> > get a
> > > reference point to that record in the database after it`s been
> > > submitted? There is an autonumbered field called SLAID that I`d
> > like
> > > to reference further in the code after the database entry has
> > been added.
> > >
> > > <cfquery name="InsertSLA" datasource="testDB">
> > > INSERT INTO SLAFile
> > > (Department, Sla_Status)
> > > VALUES
> > > (`#form.Department#`, `0`)
> > > </cfquery>
> > >
> > > Any help is appreciated.
> > >
> >
>
>
>
>
>
>
>
>SPONSORED LINKS
>Cold fusion development
><http://groups.yahoo.com/gads?t=ms&k=Cold+fusion+development&w1=Cold+fusion+dev
elopment&w2=Cold+fusion+web+development&w3=Cold+fusion+hosting&w4=Cold+fusion+mx
+hosting&w5=Cold+fusion+mx+web+hosting&w6=Cold+fusion+web+hosting&c=6&s=176&..si
g=weeFTH4MOfX4dAdvD_c17A>
> Cold fusion web development
><http://groups.yahoo.com/gads?t=ms&k=Cold+fusion+web+development&w1=Cold+fusion
+development&w2=Cold+fusion+web+development&w3=Cold+fusion+hosting&w4=Cold+fusio
n+mx+hosting&w5=Cold+fusion+mx+web+hosting&w6=Cold+fusion+web+hosting&c=6&s=176&
.sig=R91yswIHTEuv7YvdLUOPVg>
> Cold fusion hosting
><http://groups.yahoo.com/gads?t=ms&k=Cold+fusion+hosting&w1=Cold+fusion+develop
ment&w2=Cold+fusion+web+development&w3=Cold+fusion+hosting&w4=Cold+fusion+mx+hos
ting&w5=Cold+fusion+mx+web+hosting&w6=Cold+fusion+web+hosting&c=6&s=176&.sig=xuK
CT7EtOMYshNz0oT_V9w>
>
>Cold fusion mx hosting
><http://groups.yahoo.com/gads?t=ms&k=Cold+fusion+mx+hosting&w1=Cold+fusion+deve
lopment&w2=Cold+fusion+web+development&w3=Cold+fusion+hosting&w4=Cold+fusion+mx+
hosting&w5=Cold+fusion+mx+web+hosting&w6=Cold+fusion+web+hosting&c=6&s=176&.sig=
lepH9jOicLNAb0WBzUFk5A>
> Cold fusion mx web hosting
><http://groups.yahoo.com/gads?t=ms&k=Cold+fusion+mx+web+hosting&w1=Cold+fusion+
development&w2=Cold+fusion+web+development&w3=Cold+fusion+hosting&w4=Cold+fusion
+mx+hosting&w5=Cold+fusion+mx+web+hosting&w6=Cold+fusion+web+hosting&c=6&s=176&.
sig=9HMzIM_Wt1RThy3dz7BHQA>
> Cold fusion web hosting
><http://groups.yahoo.com/gads?t=ms&k=Cold+fusion+web+hosting&w1=Cold+fusion+dev
elopment&w2=Cold+fusion+web+development&w3=Cold+fusion+hosting&w4=Cold+fusion+mx
+hosting&w5=Cold+fusion+mx+web+hosting&w6=Cold+fusion+web+hosting&c=6&s=176&..si
g=J4YC2kuOd4XQBFytMt0Eeg>
>
>
>
>-------------------------------------------------------------------------------
-
>YAHOO! GROUPS LINKS
>
> * Visit your group "cold_fusion
> <http://groups.yahoo.com/group/cold_fusion>" on the web.
>
> * To unsubscribe from this group, send an email to:
> cold_fusion-unsubscribe@yahoogroups.com
> <mailto:cold_fusion-unsubscribe@yahoogroups.com?subject=Unsubscribe>
>
> * Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service
> <http://docs.yahoo.com/info/terms/>.
>
>
>-------------------------------------------------------------------------------
-
>
>.
>
>
All times are GMT
Page 1 of 1
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
Freelace Website Designer - Customer web design and software building.
China Wholesale - Electronics Products
Character Studio - Tutorials and Help
China Wholesale - Electronics Products
Character Studio - Tutorials and Help