cf9 query not returning identityCol
I am frustrated. :( In the cf9 docs, it says that cfquery is supposed to return, in the result variable, the identity column value of the inserted record. However it is not working from me. Would one of you kind souls point out my mistake? cfquery datasource=someDsn name=insertRecord result=theResult INSERT INTO someTable ( firstName, lastName ) VALUES ( cfqueryparam cfsqltype=cf_sql_varchar maxlength=20 value=#arguments.firstName#, cfqueryparam cfsqltype=cf_sql_varchar maxlength=20 value=#arguments.lastName# ) /cfquery cfoutput#theResult.identityCol#/cfoutput When I run this, identityCol is not there. I am indeed using an autonumbering identity column in the database. Yes, I know I can just add a select to this query to get at it, but I really want this to work, dammit! ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:345066 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cf9 query not returning identityCol
It's theResult.generatedKey On Jun 4, 2011 4:46 PM, Robert Rhodes rrhode...@gmail.com wrote: I am frustrated. :( In the cf9 docs, it says that cfquery is supposed to return, in the result variable, the identity column value of the inserted record. However it is not working from me. Would one of you kind souls point out my mistake? cfquery datasource=someDsn name=insertRecord result=theResult INSERT INTO someTable ( firstName, lastName ) VALUES ( cfqueryparam cfsqltype=cf_sql_varchar maxlength=20 value=#arguments.firstName#, cfqueryparam cfsqltype=cf_sql_varchar maxlength=20 value=#arguments.lastName# ) /cfquery cfoutput#theResult.identityCol#/cfoutput When I run this, identityCol is not there. I am indeed using an autonumbering identity column in the database. Yes, I know I can just add a select to this query to get at it, but I really want this to work, dammit! ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:345069 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cf9 query not returning identityCol
That's for MYSQL. I am using SQL Server 2005. The docs say identityCol But I will give it a try.. Nope., didn't work. Thanks, though. :) On Sat, Jun 4, 2011 at 6:56 PM, Jason Durham jqdur...@gmail.com wrote: It's theResult.generatedKey On Jun 4, 2011 4:46 PM, Robert Rhodes rrhode...@gmail.com wrote: I am frustrated. :( In the cf9 docs, it says that cfquery is supposed to return, in the result variable, the identity column value of the inserted record. However it is not working from me. Would one of you kind souls point out my mistake? cfquery datasource=someDsn name=insertRecord result=theResult INSERT INTO someTable ( firstName, lastName ) VALUES ( cfqueryparam cfsqltype=cf_sql_varchar maxlength=20 value=#arguments.firstName#, cfqueryparam cfsqltype=cf_sql_varchar maxlength=20 value=#arguments.lastName# ) /cfquery cfoutput#theResult.identityCol#/cfoutput When I run this, identityCol is not there. I am indeed using an autonumbering identity column in the database. Yes, I know I can just add a select to this query to get at it, but I really want this to work, dammit! ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:345076 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cf9 query not returning identityCol
It works on SQL 2008. On Jun 4, 2011 6:35 PM, Robert Rhodes rrhode...@gmail.com wrote: That's for MYSQL. I am using SQL Server 2005. The docs say identityCol But I will give it a try.. Nope., didn't work. Thanks, though. :) On Sat, Jun 4, 2011 at 6:56 PM, Jason Durham jqdur...@gmail.com wrote: It's theResult.generatedKey On Jun 4, 2011 4:46 PM, Robert Rhodes rrhode...@gmail.com wrote: I am frustrated. :( In the cf9 docs, it says that cfquery is supposed to return, in the result variable, the identity column value of the inserted record. However it is not working from me. Would one of you kind souls point out my mistake? cfquery datasource=someDsn name=insertRecord result=theResult INSERT INTO someTable ( firstName, lastName ) VALUES ( cfqueryparam cfsqltype=cf_sql_varchar maxlength=20 value=#arguments.firstName#, cfqueryparam cfsqltype=cf_sql_varchar maxlength=20 value=#arguments.lastName# ) /cfquery cfoutput#theResult.identityCol#/cfoutput When I run this, identityCol is not there. I am indeed using an autonumbering identity column in the database. Yes, I know I can just add a select to this query to get at it, but I really want this to work, dammit! ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:345077 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cf9 query not returning identityCol
http://blog.bittersweetryan.com/2011/01/getting-generated-key-from-query-in.html. On Jun 4, 2011 6:40 PM, Jason Durham jqdur...@gmail.com wrote: It works on SQL 2008. On Jun 4, 2011 6:35 PM, Robert Rhodes rrhode...@gmail.com wrote: That's for MYSQL. I am using SQL Server 2005. The docs say identityCol But I will give it a try.. Nope., didn't work. Thanks, though. :) On Sat, Jun 4, 2011 at 6:56 PM, Jason Durham jqdur...@gmail.com wrote: It's theResult.generatedKey On Jun 4, 2011 4:46 PM, Robert Rhodes rrhode...@gmail.com wrote: I am frustrated. :( In the cf9 docs, it says that cfquery is supposed to return, in the result variable, the identity column value of the inserted record. However it is not working from me. Would one of you kind souls point out my mistake? cfquery datasource=someDsn name=insertRecord result=theResult INSERT INTO someTable ( firstName, lastName ) VALUES ( cfqueryparam cfsqltype=cf_sql_varchar maxlength=20 value=#arguments.firstName#, cfqueryparam cfsqltype=cf_sql_varchar maxlength=20 value=#arguments.lastName# ) /cfquery cfoutput#theResult.identityCol#/cfoutput When I run this, identityCol is not there. I am indeed using an autonumbering identity column in the database. Yes, I know I can just add a select to this query to get at it, but I really want this to work, dammit! ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:345078 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cf9 query not returning identityCol
It does work and thats the way to do it. Are you 100% it's an auto inc field? http://cookbooks.adobe.com/post_Generated_key___Get_the_ID_of_the_inserted_record-16593.html Paul. On Jun 5, 2011, at 9:35 AM, Robert Rhodes wrote: That's for MYSQL. I am using SQL Server 2005. The docs say identityCol But I will give it a try.. Nope., didn't work. Thanks, though. :) On Sat, Jun 4, 2011 at 6:56 PM, Jason Durham jqdur...@gmail.com wrote: It's theResult.generatedKey On Jun 4, 2011 4:46 PM, Robert Rhodes rrhode...@gmail.com wrote: I am frustrated. :( In the cf9 docs, it says that cfquery is supposed to return, in the result variable, the identity column value of the inserted record. However it is not working from me. Would one of you kind souls point out my mistake? cfquery datasource=someDsn name=insertRecord result=theResult INSERT INTO someTable ( firstName, lastName ) VALUES ( cfqueryparam cfsqltype=cf_sql_varchar maxlength=20 value=#arguments.firstName#, cfqueryparam cfsqltype=cf_sql_varchar maxlength=20 value=#arguments.lastName# ) /cfquery cfoutput#theResult.identityCol#/cfoutput When I run this, identityCol is not there. I am indeed using an autonumbering identity column in the database. Yes, I know I can just add a select to this query to get at it, but I really want this to work, dammit! ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:345080 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cf9 query not returning identityCol
Weird. Not working for me with cf9 and sql sever 2005. Identity Specification = Yes Is Identity = Yes Identity Increment 1 Identity Seed 1 On Sat, Jun 4, 2011 at 8:23 PM, Paul Kukiel pkuk...@gmail.com wrote: It does work and thats the way to do it. Are you 100% it's an auto inc field? http://cookbooks.adobe.com/post_Generated_key___Get_the_ID_of_the_inserted_record-16593.html Paul. On Jun 5, 2011, at 9:35 AM, Robert Rhodes wrote: That's for MYSQL. I am using SQL Server 2005. The docs say identityCol But I will give it a try.. Nope., didn't work. Thanks, though. :) On Sat, Jun 4, 2011 at 6:56 PM, Jason Durham jqdur...@gmail.com wrote: It's theResult.generatedKey On Jun 4, 2011 4:46 PM, Robert Rhodes rrhode...@gmail.com wrote: I am frustrated. :( In the cf9 docs, it says that cfquery is supposed to return, in the result variable, the identity column value of the inserted record. However it is not working from me. Would one of you kind souls point out my mistake? cfquery datasource=someDsn name=insertRecord result=theResult INSERT INTO someTable ( firstName, lastName ) VALUES ( cfqueryparam cfsqltype=cf_sql_varchar maxlength=20 value=#arguments.firstName#, cfqueryparam cfsqltype=cf_sql_varchar maxlength=20 value=#arguments.lastName# ) /cfquery cfoutput#theResult.identityCol#/cfoutput When I run this, identityCol is not there. I am indeed using an autonumbering identity column in the database. Yes, I know I can just add a select to this query to get at it, but I really want this to work, dammit! ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:345081 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: cf9 query not returning identityCol
http://cookbooks.adobe.com/post_Getting_the_ID_of_the_Last_Record_Inserted_I nto_a-16522.html I'm curious as to the best way of doing this in CF7. Currently I do another query to find the last inserted ID, but this seems a long winded way of doing things. Jenny Gavin-Wear Fast Track eCommerce http://www.ftol-ecommerce.com/ -Original Message- From: Robert Rhodes [mailto:rrhode...@gmail.com] Sent: 05 June 2011 02:11 To: cf-talk Subject: Re: cf9 query not returning identityCol Weird. Not working for me with cf9 and sql sever 2005. Identity Specification = Yes Is Identity = Yes Identity Increment 1 Identity Seed 1 On Sat, Jun 4, 2011 at 8:23 PM, Paul Kukiel pkuk...@gmail.com wrote: It does work and thats the way to do it. Are you 100% it's an auto inc field? http://cookbooks.adobe.com/post_Generated_key___Get_the_ID_of_the_ inserted_record-16593.html Paul. On Jun 5, 2011, at 9:35 AM, Robert Rhodes wrote: That's for MYSQL. I am using SQL Server 2005. The docs say identityCol But I will give it a try.. Nope., didn't work. Thanks, though. :) On Sat, Jun 4, 2011 at 6:56 PM, Jason Durham jqdur...@gmail.com wrote: It's theResult.generatedKey On Jun 4, 2011 4:46 PM, Robert Rhodes rrhode...@gmail.com wrote: I am frustrated. :( In the cf9 docs, it says that cfquery is supposed to return, in the result variable, the identity column value of the inserted record. However it is not working from me. Would one of you kind souls point out my mistake? cfquery datasource=someDsn name=insertRecord result=theResult INSERT INTO someTable ( firstName, lastName ) VALUES ( cfqueryparam cfsqltype=cf_sql_varchar maxlength=20 value=#arguments.firstName#, cfqueryparam cfsqltype=cf_sql_varchar maxlength=20 value=#arguments.lastName# ) /cfquery cfoutput#theResult.identityCol#/cfoutput When I run this, identityCol is not there. I am indeed using an autonumbering identity column in the database. Yes, I know I can just add a select to this query to get at it, but I really want this to work, dammit! ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:345082 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cf9 query not returning identityCol
This is the way I have been doing it. I may have to keep doing it this way if I can't get result.identityCol to work the way it is supposed to: cfquery datasource=someDsn name=insertRecord INSERT INTO someTable ( firstName, lastName ) VALUES ( cfqueryparam cfsqltype=cf_sql_varchar maxlength=20 value=#arguments.firstName#, cfqueryparam cfsqltype=cf_sql_varchar maxlength=20 value=#arguments.lastName# ) SELECT Scope_Identity() AS ID /cfquery cfoutput#tinsertRecord.ID#/cfoutput It's a little more efficient than doing it with two separate querys. RR On Sat, Jun 4, 2011 at 10:12 PM, Jenny Gavin-Wear jenn...@fasttrackonline.co.uk wrote: http://cookbooks.adobe.com/post_Getting_the_ID_of_the_Last_Record_Inserted_I nto_a-16522.html I'm curious as to the best way of doing this in CF7. Currently I do another query to find the last inserted ID, but this seems a long winded way of doing things. Jenny Gavin-Wear Fast Track eCommerce http://www.ftol-ecommerce.com/ -Original Message- From: Robert Rhodes [mailto:rrhode...@gmail.com] Sent: 05 June 2011 02:11 To: cf-talk Subject: Re: cf9 query not returning identityCol Weird. Not working for me with cf9 and sql sever 2005. Identity Specification = Yes Is Identity = Yes Identity Increment 1 Identity Seed 1 On Sat, Jun 4, 2011 at 8:23 PM, Paul Kukiel pkuk...@gmail.com wrote: It does work and thats the way to do it. Are you 100% it's an auto inc field? http://cookbooks.adobe.com/post_Generated_key___Get_the_ID_of_the_ inserted_record-16593.html Paul. On Jun 5, 2011, at 9:35 AM, Robert Rhodes wrote: That's for MYSQL. I am using SQL Server 2005. The docs say identityCol But I will give it a try.. Nope., didn't work. Thanks, though. :) On Sat, Jun 4, 2011 at 6:56 PM, Jason Durham jqdur...@gmail.com wrote: It's theResult.generatedKey On Jun 4, 2011 4:46 PM, Robert Rhodes rrhode...@gmail.com wrote: I am frustrated. :( In the cf9 docs, it says that cfquery is supposed to return, in the result variable, the identity column value of the inserted record. However it is not working from me. Would one of you kind souls point out my mistake? cfquery datasource=someDsn name=insertRecord result=theResult INSERT INTO someTable ( firstName, lastName ) VALUES ( cfqueryparam cfsqltype=cf_sql_varchar maxlength=20 value=#arguments.firstName#, cfqueryparam cfsqltype=cf_sql_varchar maxlength=20 value=#arguments.lastName# ) /cfquery cfoutput#theResult.identityCol#/cfoutput When I run this, identityCol is not there. I am indeed using an autonumbering identity column in the database. Yes, I know I can just add a select to this query to get at it, but I really want this to work, dammit! ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:345083 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: cf9 query not returning identityCol
Thanks Robert :) -Original Message- From: Robert Rhodes [mailto:rrhode...@gmail.com] Sent: 05 June 2011 03:55 To: cf-talk Subject: Re: cf9 query not returning identityCol This is the way I have been doing it. I may have to keep doing it this way if I can't get result.identityCol to work the way it is supposed to: cfquery datasource=someDsn name=insertRecord INSERT INTO someTable ( firstName, lastName ) VALUES ( cfqueryparam cfsqltype=cf_sql_varchar maxlength=20 value=#arguments.firstName#, cfqueryparam cfsqltype=cf_sql_varchar maxlength=20 value=#arguments.lastName# ) SELECT Scope_Identity() AS ID /cfquery cfoutput#tinsertRecord.ID#/cfoutput It's a little more efficient than doing it with two separate querys. RR On Sat, Jun 4, 2011 at 10:12 PM, Jenny Gavin-Wear jenn...@fasttrackonline.co.uk wrote: http://cookbooks.adobe.com/post_Getting_the_ID_of_the_Last_Record_ Inserted_I nto_a-16522.html I'm curious as to the best way of doing this in CF7. Currently I do another query to find the last inserted ID, but this seems a long winded way of doing things. Jenny Gavin-Wear Fast Track eCommerce http://www.ftol-ecommerce.com/ -Original Message- From: Robert Rhodes [mailto:rrhode...@gmail.com] Sent: 05 June 2011 02:11 To: cf-talk Subject: Re: cf9 query not returning identityCol Weird. Not working for me with cf9 and sql sever 2005. Identity Specification = Yes Is Identity = Yes Identity Increment 1 Identity Seed 1 On Sat, Jun 4, 2011 at 8:23 PM, Paul Kukiel pkuk...@gmail.com wrote: It does work and thats the way to do it. Are you 100% it's an auto inc field? http://cookbooks.adobe.com/post_Generated_key___Get_the_ID_of_the_ inserted_record-16593.html Paul. On Jun 5, 2011, at 9:35 AM, Robert Rhodes wrote: That's for MYSQL. I am using SQL Server 2005. The docs say identityCol But I will give it a try.. Nope., didn't work. Thanks, though. :) On Sat, Jun 4, 2011 at 6:56 PM, Jason Durham jqdur...@gmail.com wrote: It's theResult.generatedKey On Jun 4, 2011 4:46 PM, Robert Rhodes rrhode...@gmail.com wrote: I am frustrated. :( In the cf9 docs, it says that cfquery is supposed to return, in the result variable, the identity column value of the inserted record. However it is not working from me. Would one of you kind souls point out my mistake? cfquery datasource=someDsn name=insertRecord result=theResult INSERT INTO someTable ( firstName, lastName ) VALUES ( cfqueryparam cfsqltype=cf_sql_varchar maxlength=20 value=#arguments.firstName#, cfqueryparam cfsqltype=cf_sql_varchar maxlength=20 value=#arguments.lastName# ) /cfquery cfoutput#theResult.identityCol#/cfoutput When I run this, identityCol is not there. I am indeed using an autonumbering identity column in the database. Yes, I know I can just add a select to this query to get at it, but I really want this to work, dammit! ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:345086 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm