Re: nested SQL update statement to replace CF script
If _all_ children _aways_ get published (like your pseudocode indicates), why not just flag the parent category? If you want to do it all in SQL, try... http://msdn.microsoft.com/en-us/library/ms186243.aspx Jason Durham On Fri, Jan 28, 2011 at 1:16 AM, wabba must...@wabba.net wrote: Hi all, read a lot, post rarely. Anyway, try to keep this simple as possible - I have a database of items stored within nested categories using a pivot table to establish parent/childs for the categories (the can virtually nest indefinitely, one child cat can be under multiple parents, etc). There are roughly 30,000 items within 3,000 categories. There are a handful of top level categories. One of the top level categories gets published to an external site regularly, while the others don't. I use a bit flag on the items to determine which items get published and which don't, so that only the items we want published get transferred. Currently I use a nested CF custom tag to set the publish flags - I feed it a top level categoryID, it sets Publish=1 on the items in that category, then calls itself in a loop with all of the CategoryID where the ParentCatID=CategoryID. Something like this: (cf_pushlishtree custom tag): cfquery Update item SET Publish=1 WHERE CategoryID=CurrentCat /cfquery cfquery name=getsubcats Select CategoryID FROM CatPivot WHERE ParentCatID=CurrentCat /cfquery cfloop query=getsubcats cf_publishtree CurrentCat=#CurrentCat# /cfloop ...It works fine but is slow and will timeout before it finishes, and I can't restart it where it leaves off. I would think this could be done right in MS SQL by having a nested function or stored procedure that can do the update and then loop itself from the select, but I can't figure out how - I don't think an UPDATE is allowed in a function, and a stored procedure can't be called from a select statement. I considered adding a TopCategoryID to the item or category table as it would make publishing very simple, but we can't as categories can exist anywhere in the category structure, often under multiple parents simultaneously. ~| 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:341624 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: No SQL Update!
I've been trying to figure this one out. The query gets hit, but the database isn't being updated from the values in the form. The columns seem valid, and the table permissions are fine. What does your debug output show? Are you setting the form variable used in the WHERE clause? Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:318806 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: No SQL Update!
Run: SELECT * FROM dbo.Vehicles WHERE (StockNo='#form.carid#') and see if that returns a row. On Tue, Feb 3, 2009 at 6:34 PM, Rick Sanders r...@webenergy.ca wrote: I've been trying to figure this one out. The query gets hit, but the database isn't being updated from the values in the form. The columns seem valid, and the table permissions are fine. I'm using SQL Server 2005. Here's the query: UPDATE dbo.Vehicles SET StockNo='#form.stockno#',CarMake=#form.make#,CarModel='#form.model#',ModelTr im='#form.trim#',CarYear=#form.caryear#,Category=#form.category#,Mileage='#f orm.mileage#',unit='km',VIN='#form.vin#',EngineSize='#form.engine#',Transmis sion='#form.trans#',Drivetrain='#form.drivetrain#',Price=#form.price#,Warran ty='#form.warranty#',Description='#form.description#',Inspected='#form.mm# ', Financing='#form.financing#',Leasing='#form.leasing#',Active='#form.active#' ,Featured='#form.featured#',Pic1='#pic1#',Pic2='#pic2#',Pic3='#pic3#',Pic4=' #pic4#',thumb1='#pic1t#',thumb2='#pic2t#',thumb3='#pic3t#',thumb4='#pic4t#', Sold='#form.sold#',SoldPrice=#form.soldamt#,VehicleCost=#form.cost#,MVIcost= #form.mvicost#,Reconditioning='#form.reconditioning#',ReconditioningCost=#fo rm.reconcost#,Parts='#form.parts#',PartsCost=#form.partscost#,Labour='#form. labour#',LabourCost=#form.labourcost# WHERE (StockNo='#form.carid#') Thanks for any help! Rick Sanders Webenergy ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:318807 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: No SQL Update!
Here are the values being passed: UPDATE dbo.Vehicles SET StockNo='012',CarMake=31,CarModel='ION ',ModelTrim=' ',CarYear=2004,Category=1,Mileage='135,000 ',unit='km',VIN=' ',EngineSize='4cyl',Transmission='Automatic',Drivetrain='Front Wheel Drive',Price=4300.00,Warranty='Yes',Description=' 2004 Saturn Ion, 4 Cylinder 5 speed standard, that is fully loaded and is in excellent condition. This car has Air conditioning, Power locks, windows, Keyless entry, and more. This car has 135,00,000kms that has just been MVI''d, new front brakes, 4 brand new tires with snows on the front.. This car works and looks like new, a must drive, this car looks like new inside, non smoker',Inspected='12/09 ',Financing='No',Leasing='No',Active='Yes',Featured='Yes',Pic1='012_1.jpg',P ic2='012_2.jpg',Pic3='012_3.jpg',Pic4='012_4.jpg',thumb1='012thumb_1.jpg',th umb2='012thumb_2.jpg',thumb3='012thumb_3.jpg',thumb4='012thumb_4.jpg',Sold=' Yes',SoldPrice=0,VehicleCost=0,MVIcost=0,Reconditioning='N/A',Reconditioning Cost=0,Parts='N/A',PartsCost=0,Labour='N/A',LabourCost=0 WHERE (StockNo='3') No errors from CF or the database! SELECT statements work no problem either. I can pull up anything from the DB. This is a weird one! -Original Message- From: Dave Watts [mailto:dwa...@figleaf.com] Sent: February-03-09 8:39 PM To: cf-talk Subject: Re: No SQL Update! I've been trying to figure this one out. The query gets hit, but the database isn't being updated from the values in the form. The columns seem valid, and the table permissions are fine. What does your debug output show? Are you setting the form variable used in the WHERE clause? Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:318808 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: No SQL Update!
What do you get when you run: SELECT * FROM dbo.Vehicles WHERE (StockNo='3') ~Brad Original Message Subject: RE: No SQL Update! From: Rick Sanders c...@webenergy.ca Date: Tue, February 03, 2009 6:46 pm To: cf-talk cf-talk@houseoffusion.com ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:318809 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: No SQL Update!
StockNo is a char/varchar datatype? On Tue, Feb 3, 2009 at 4:55 PM, b...@bradwood.com wrote: What do you get when you run: SELECT * FROM dbo.Vehicles WHERE (StockNo='3') ~Brad Original Message Subject: RE: No SQL Update! From: Rick Sanders c...@webenergy.ca Date: Tue, February 03, 2009 6:46 pm To: cf-talk cf-talk@houseoffusion.com ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:318810 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: No SQL Update!
Problem fixed! My dumb error! I was trying to update by vehicle ID using the stock number! Vehicle ID's and stock numbers are different, so no wonder why it didn't update. Time to step away from the puter. Thanks for listening! -Original Message- From: Charlie Griefer [mailto:charlie.grie...@gmail.com] Sent: February-03-09 9:02 PM To: cf-talk Subject: Re: No SQL Update! StockNo is a char/varchar datatype? On Tue, Feb 3, 2009 at 4:55 PM, b...@bradwood.com wrote: What do you get when you run: SELECT * FROM dbo.Vehicles WHERE (StockNo='3') ~Brad Original Message Subject: RE: No SQL Update! From: Rick Sanders c...@webenergy.ca Date: Tue, February 03, 2009 6:46 pm To: cf-talk cf-talk@houseoffusion.com ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:318811 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: No SQL Update!
yeah, that's one of those if i only had a dollar for every time... things :) On Tue, Feb 3, 2009 at 5:14 PM, Rick Sanders c...@webenergy.ca wrote: Problem fixed! My dumb error! I was trying to update by vehicle ID using the stock number! Vehicle ID's and stock numbers are different, so no wonder why it didn't update. Time to step away from the puter. Thanks for listening! -Original Message- From: Charlie Griefer [mailto:charlie.grie...@gmail.com] Sent: February-03-09 9:02 PM To: cf-talk Subject: Re: No SQL Update! StockNo is a char/varchar datatype? On Tue, Feb 3, 2009 at 4:55 PM, b...@bradwood.com wrote: What do you get when you run: SELECT * FROM dbo.Vehicles WHERE (StockNo='3') ~Brad Original Message Subject: RE: No SQL Update! From: Rick Sanders c...@webenergy.ca Date: Tue, February 03, 2009 6:46 pm To: cf-talk cf-talk@houseoffusion.com ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:318813 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: No SQL Update!
Even a nickel and I'd be happy! -Original Message- From: Charlie Griefer [mailto:charlie.grie...@gmail.com] Sent: February-03-09 9:17 PM To: cf-talk Subject: Re: No SQL Update! yeah, that's one of those if i only had a dollar for every time... things :) On Tue, Feb 3, 2009 at 5:14 PM, Rick Sanders c...@webenergy.ca wrote: Problem fixed! My dumb error! I was trying to update by vehicle ID using the stock number! Vehicle ID's and stock numbers are different, so no wonder why it didn't update. Time to step away from the puter. Thanks for listening! -Original Message- From: Charlie Griefer [mailto:charlie.grie...@gmail.com] Sent: February-03-09 9:02 PM To: cf-talk Subject: Re: No SQL Update! StockNo is a char/varchar datatype? On Tue, Feb 3, 2009 at 4:55 PM, b...@bradwood.com wrote: What do you get when you run: SELECT * FROM dbo.Vehicles WHERE (StockNo='3') ~Brad Original Message Subject: RE: No SQL Update! From: Rick Sanders c...@webenergy.ca Date: Tue, February 03, 2009 6:46 pm To: cf-talk cf-talk@houseoffusion.com ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:318817 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Detecting SQL Update success
Thank you Paul and Nate. Dan [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Detecting SQL Update success
Is there a way to detect if a SQL update statement in CFQUERY resulted in a record being updated? I guess I would want to know the number of rows updated. depends on your db, for sql server examine the @@ROWCOUNT var immediately after your update. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Detecting SQL Update success
Exactly, To be accurate its best to use a storedproc, but if you don't want to use it like this cfquery name=updatequery datasource=whateverdsn update table set column = x where column = y /cfquery cfquery name=getrowcount datasource= whateverdsn select @@rowcount as RecordsUpdated /cfquery just be sure to wrap in a transaction. The only bad thing about it is if the table you are updating has a trigger that does something then you are going to get the value from the trigger's execution.@@rowcount returns the number of rows affected by the very last executed statement. Hope it helps, Nate Nelson -Original Message- From: Paul Hastings [mailto:[EMAIL PROTECTED] Sent: Monday, January 12, 2004 4:58 PM To: CF-Talk Subject: Re: Detecting SQL Update success Is there a way to detect if a SQL update statement in CFQUERY resulted in a record being updated? I guess I would want to know the number of rows updated. depends on your db, for sql server examine the @@ROWCOUNT var immediately after your update. _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
re: MS SQL UPDATE syntax help
Hi, Could someone tell me where I'm going wrong with the below cfquery? cfquery name=qUpdateProfile datasource=prefs UPDATE tblProfiles ( strSelect, strShowMe ) VALUES ( cfqueryparam value=#No_Access_List# cfsqltype=CF_SQL_LONGVARCHAR, cfqueryparam value=#form.dir_access# cfsqltype=CF_SQL_LONGVARCHAR ) WHERE strUsername = '#auth#' /cfquery ~| Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. http://www.cfhosting.com
re: MS SQL UPDATE syntax help
Hi, Could someone tell me where I'm going wrong with the below cfquery? cfquery name=qUpdateProfile datasource=prefs UPDATE tblProfiles ( strSelect, strShowMe ) VALUES ( cfqueryparam value=#No_Access_List# cfsqltype=CF_SQL_LONGVARCHAR, cfqueryparam value=#form.dir_access# cfsqltype=CF_SQL_LONGVARCHAR ) WHERE strUsername = '#auth#' /cfquery For MS SQL, the format for an update statement is, in general, UPDATE tablename SET col1=value1, col2=value2... WHERE keycolumn=value3 Stephen ~| Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
RE: MS SQL UPDATE syntax help
update tblProfiles set strSelect = cfqueryparam value=#No_Access_List# cfsqltype=CF_SQL_LONGVARCHAR, strShowMe = cfqueryparam value=#form.dir_access# cfsqltype=CF_SQL_LONGVARCHAR where strUsername = '#auth#' :) hth tw tony weeg uncertified advanced cold fusion developer tony at navtrak dot net www.navtrak.net office 410.548.2337 fax 410.860.2337 -Original Message- From: Bushy [mailto:[EMAIL PROTECTED] Sent: Thursday, August 14, 2003 12:52 PM To: CF-Talk Subject: re: MS SQL UPDATE syntax help Hi, Could someone tell me where I'm going wrong with the below cfquery? cfquery name=qUpdateProfile datasource=prefs UPDATE tblProfiles ( strSelect, strShowMe ) VALUES ( cfqueryparam value=#No_Access_List# cfsqltype=CF_SQL_LONGVARCHAR, cfqueryparam value=#form.dir_access# cfsqltype=CF_SQL_LONGVARCHAR ) WHERE strUsername = '#auth#' /cfquery ~| Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Get the mailserver that powers this list at http://www.coolfusion.com
Re: MS SQL UPDATE syntax help
heh looks like an INSERT query to me ;) try UPDATE tblProfiles Set blah = blah, blah2 = blah2 where blah = blah cheers - Original Message - From: Bushy [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Thursday, August 14, 2003 9:51 AM Subject: re: MS SQL UPDATE syntax help Hi, Could someone tell me where I'm going wrong with the below cfquery? cfquery name=qUpdateProfile datasource=prefs UPDATE tblProfiles ( strSelect, strShowMe ) VALUES ( cfqueryparam value=#No_Access_List# cfsqltype=CF_SQL_LONGVARCHAR, cfqueryparam value=#form.dir_access# cfsqltype=CF_SQL_LONGVARCHAR ) WHERE strUsername = '#auth#' /cfquery ~| Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. http://www.cfhosting.com
RE: dynamic sql update
If there is a variables called #active_week# and you have it in a cfquery you don't need a tag around it. Ade -Original Message- From: Wurst, Keith D. [mailto:[EMAIL PROTECTED] Sent: 01 August 2003 16:41 To: CF-Talk Subject: dynamic sql update i know i need a tag around #active_week#_winner but i cant remember what it is. can someone remind me? thanks. update tbl_pic set #active_week#_pic = #active_week#_win ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
