Same as Mikes but using the inbuilt functions and avoiding @@identity
<cfquery datasource="#DSN#" result="myResult">
INSERT INTO MyTable (col1)
VALUES ('col1')
</cfquery>
<!--- Tag based output --->
<cfoutput>Inserted ID is: #myResult["GENERATEDKEY"]#</cfoutput>
On Wed, Jan 27, 2016 at 11:06 AM, Mark King <m...@ampersand.net.au> wrote:
> Hi Brian.
>
>
>
> Well first of all if you are using SQL Server try this to get the new ID
> in the same transaction rather than doing another query;
>
>
>
> <cfquery datasource="[datasource]" username="[username]"
> password="[password]" name="qry">
>
> SET NOCOUNT ON
>
> INSERT INTO orders
>
> (
>
> ** Your order fields **
>
> )
>
> VALUES
>
> (
>
> ** Your data **
>
> )
>
> SELECT @@Identity AS orderID
>
> SET NOCOUNT OFF
>
> </cfquery>
>
> <cfset attributes.orderID=qry.orderID>
>
>
>
> I am not sure what you use the unique number for other than for something
> to put onto the receipts or something?
>
>
>
> However, if you want to obscure the ID in a link you could encrypt it. I
> have done this is the past where I sent users and email but didn’t want
> them to be able to just increment the link to see someone else’s order,
> e.g. displayorder.cfm?orderID=1234
>
>
>
> So I did this:
>
>
>
> <cfset myKey="[** your generated AES key **]">
>
> <cfset
> AESorderID=urlencodedformat(encrypt(attributes.orderID,myKey,"AES"))>
>
>
>
> Because the orderID is unique the encrypted value should be unique as well.
>
>
>
> See
> http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-7c2f.html
>
>
>
> I am not sure if that is what you actually wanted but I hope it helps!
>
>
>
>
>
> *From:* cfaussie@googlegroups.com [mailto:cfaussie@googlegroups.com] *On
> Behalf Of *Brian Knott
> *Sent:* Wednesday, 27 January 2016 9:31 AM
> *To:* cfaussie@googlegroups.com
> *Subject:* [cfaussie] SQL identity issue
>
>
>
> Hi everyone.
>
>
>
> I have an issue with inserting orders into a database. What I currently
> do is create a random number and insert that number into the database when
> an order is inserted. I then query the database to get the order number
> (unique ID generated by SQL Server). Using this order number I then insert
> the items into an item table.
>
>
>
> The issue is that its possible for 2 orders to end up with the same unique
> number, this is because the random function is not actually that random.
> If two people are submitting orders at the same time, they get the
> same random number. This means that all of the items ordered go to one
> customer, and the other customer gets no items.
>
>
>
> Current code for the random number is
>
>
>
> <cfset session.order.uniquenumber = RandRange(1,99999999) + now()>
>
>
>
> Is there a more reliable way of doing this?
>
>
>
> Brian
>
>
>
> --
> You received this message because you are subscribed to the Google Groups
> "cfaussie" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to cfaussie+unsubscr...@googlegroups.com.
> To post to this group, send email to cfaussie@googlegroups.com.
> Visit this group at https://groups.google.com/group/cfaussie.
> For more options, visit https://groups.google.com/d/optout.
>
> --
> You received this message because you are subscribed to the Google Groups
> "cfaussie" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to cfaussie+unsubscr...@googlegroups.com.
> To post to this group, send email to cfaussie@googlegroups.com.
> Visit this group at https://groups.google.com/group/cfaussie.
> For more options, visit https://groups.google.com/d/optout.
>
--
Paul Kukiel
--
You received this message because you are subscribed to the Google Groups
"cfaussie" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to cfaussie+unsubscr...@googlegroups.com.
To post to this group, send email to cfaussie@googlegroups.com.
Visit this group at https://groups.google.com/group/cfaussie.
For more options, visit https://groups.google.com/d/optout.
