Re: Update Query syntax
WHERE itemno IN (#preserveSingleQuotes(itemnos)#)
or even better:
WHERE itemno IN (cfqueryparam value=#itemno# cfsqltype=cf_sql_char
list=true /)
On Thu, Oct 23, 2008 at 12:21 PM, Ben Conner [EMAIL PROTECTED] wrote:
Hi,
I have a simple update query that is throwing an error:
cfquery name=NewRec datasource=#Application.DSN#
update icitem
set lastchanged = '1/1/2008'
where itemno in (#itemnos#)
/cfquery
The itemnos variable has the value:
'1A703239', '1A703240', '1A703241', '1A703242', '1A703243'
If I paste that value in between the parens, it works. I've also tried
using double quotation marks. The error I'm getting is:
Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Line 3: Incorrect syntax near
'1'.
The error occurred in D:\sites\qcliving.com\htdocs\Admin\kludge.cfm: line
23
21 :update icitem
22 :set lastchanged = '1/1/2008'
23 :where itemno in (#itemnos#)
24 :/cfquery
SQLupdate icitem set lastchanged = '1/1/2008' where itemno in
(''1A703239'', ''1A703240'', ''1A703241'', ''1A703242'', ''1A703243'')
DATASOURCEqcliving
VENDORERRORCODE 170
SQLSTATE HY000
What am I missing here?
Thanks!
--Ben
~|
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:314316
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Update Query syntax
Thanks much, Charlie! --Ben WHERE itemno IN (#preserveSingleQuotes(itemnos)#) or even better: WHERE itemno IN (cfqueryparam value=#itemno# cfsqltype=cf_sql_char list=true /) ~| 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:314317 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Update query record count?
I don't believe so.If you are using a db that supports multiple SQL comands in onf cfquery tag, you could: 1.do a select instead of the update first 2.set an int(say, @myRowcount to @@rowcount 3.run your update 4.return @myRowcount AS RecordsUpdated This is off the top of my head and there might be a better way, but that gets you what you need. -Original Message- From: [EMAIL PROTECTED] To: CF-Talk Sent: 2/13/04 9:05 AM Subject: Update query record count? Is there a way to see how many (if any) records an update query has updated after it runs? Recordcount doesn't work.. Is there a way without doing another query? Thanks, Chris _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Update query record count?
I had in working in a 2 part w/ select, but was hoping to cut down to 1 query since it's being hit on every page to lower the DB load if possible. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Update query record count?
as long as you are using a db that supports multiple sql statements per cfquery tag, you really aren't killing your db that bad.move it to a stored proc and you are really g2g. I had an app using a self-referncing table that had to do ton of recursive loops with recordsets looping and doing a couple-3 queries for each record in the initial record set.MS access was taking 4000+ ms for page render; ms sql with a single cfquery and a ton of sql statements and cursors and the like in a single cfquery tag was sub-100ms. I think the biggest hit is cf making the odbc connection, especially if you have good indexes and a cached query plan(stored proc or cfqueryparam).Can another cf guru on the list verify? -Original Message- From: [EMAIL PROTECTED] To: CF-Talk Sent: 2/13/04 9:19 AM Subject: Re: Update query record count? I had in working in a 2 part w/ select, but was hoping to cut down to 1 query since it's being hit on every page to lower the DB load if possible. _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Update query record count?
If using MS SQL, @@ROWCOUNT will return exactly that what your after! DECLARE @myVar INT INSERT SET @myVar = @@ROWCOUNT OR INSERT SELECT @@ROWCOUNT AS myVar Taco Fleur Bloghttp://www.tacofleur.com/index/blog/ http://www.tacofleur.com/index/blog/ Methodology http://www.tacofleur.com/index/methodology/ Tell me and I will forget Show me and I will remember Teach me and I will learn -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Saturday, 14 February 2004 1:05 AM To: CF-Talk Subject: Update query record count? Is there a way to see how many (if any) records an update query has updated after it runs? Recordcount doesn't work.. Is there a way without doing another query? Thanks, Chris _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: update query
I would do a cfquery of all columns in b. then loop through that, for each iteration, update a with the value of some b query column value. make sense? 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: Phillip B [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 09, 2003 12:21 PM To: CF-Talk Subject: OT: update query I have two tables. tableA id name region tableB id name region I need to update tableA's region colum with tableB's region colum. The region colum has changed for some and not for others. With that said, does this look right? UPDATE tableA SET region = (SELECT region FROM tableB WHERE tableB.id = tableA.id) Its been a while since I have done this and I cant remeber the best way to do it. :| Phillip B. www.LoungeRoyale.com www.FillWorks.com ~| 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
Re: update query
That is what I would normally do but it has to be part of a DTS package and will be ran everyday :( Phillip B. www.LoungeRoyale.com www.FillWorks.com - Original Message - From: Tony Weeg [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Wednesday, July 09, 2003 11:25 AM Subject: RE: update query I would do a cfquery of all columns in b. then loop through that, for each iteration, update a with the value of some b query column value. make sense? 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: Phillip B [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 09, 2003 12:21 PM To: CF-Talk Subject: OT: update query I have two tables. tableA id name region tableB id name region I need to update tableA's region colum with tableB's region colum. The region colum has changed for some and not for others. With that said, does this look right? UPDATE tableA SET region = (SELECT region FROM tableB WHERE tableB.id = tableA.id) Its been a while since I have done this and I cant remeber the best way to do it. :| Phillip B. www.LoungeRoyale.com www.FillWorks.com ~| 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
RE: update query
Hi If the link is ID then your query looks ok the following will do it. UPDATE tableA SET region = TableB.region FROM tableB, TableA WHERE tableB.id = tableA.id -Original Message- From: Phillip B [mailto:[EMAIL PROTECTED] Sent: 09 July 2003 17:40 To: CF-Talk Subject: Re: update query That is what I would normally do but it has to be part of a DTS package and will be ran everyday :( Phillip B. www.LoungeRoyale.com www.FillWorks.com - Original Message - From: Tony Weeg [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Wednesday, July 09, 2003 11:25 AM Subject: RE: update query I would do a cfquery of all columns in b. then loop through that, for each iteration, update a with the value of some b query column value. make sense? 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: Phillip B [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 09, 2003 12:21 PM To: CF-Talk Subject: OT: update query I have two tables. tableA id name region tableB id name region I need to update tableA's region colum with tableB's region colum. The region colum has changed for some and not for others. With that said, does this look right? UPDATE tableA SET region = (SELECT region FROM tableB WHERE tableB.id = tableA.id) Its been a while since I have done this and I cant remeber the best way to do it. :| Phillip B. www.LoungeRoyale.com www.FillWorks.com ~| 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 This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. http://www.cfhosting.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: update query
so you have to do it all in sql server logic? well, then, SQL Experts mount up 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: Phillip B [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 09, 2003 12:40 PM To: CF-Talk Subject: Re: update query That is what I would normally do but it has to be part of a DTS package and will be ran everyday :( Phillip B. www.LoungeRoyale.com www.FillWorks.com - Original Message - From: Tony Weeg [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Wednesday, July 09, 2003 11:25 AM Subject: RE: update query I would do a cfquery of all columns in b. then loop through that, for each iteration, update a with the value of some b query column value. make sense? 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: Phillip B [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 09, 2003 12:21 PM To: CF-Talk Subject: OT: update query I have two tables. tableA id name region tableB id name region I need to update tableA's region colum with tableB's region colum. The region colum has changed for some and not for others. With that said, does this look right? UPDATE tableA SET region = (SELECT region FROM tableB WHERE tableB.id = tableA.id) Its been a while since I have done this and I cant remeber the best way to do it. :| Phillip B. www.LoungeRoyale.com www.FillWorks.com ~| 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 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 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: update query
I will give it a try. Thanks a lot Phillip B. www.LoungeRoyale.com www.FillWorks.com - Original Message - From: Andy Ewings [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Wednesday, July 09, 2003 11:55 AM Subject: RE: update query Hi If the link is ID then your query looks ok the following will do it. UPDATE tableA SET region = TableB.region FROM tableB, TableA WHERE tableB.id = tableA.id -Original Message- From: Phillip B [mailto:[EMAIL PROTECTED] Sent: 09 July 2003 17:40 To: CF-Talk Subject: Re: update query That is what I would normally do but it has to be part of a DTS package and will be ran everyday :( Phillip B. www.LoungeRoyale.com www.FillWorks.com - Original Message - From: Tony Weeg [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Wednesday, July 09, 2003 11:25 AM Subject: RE: update query I would do a cfquery of all columns in b. then loop through that, for each iteration, update a with the value of some b query column value. make sense? 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: Phillip B [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 09, 2003 12:21 PM To: CF-Talk Subject: OT: update query I have two tables. tableA id name region tableB id name region I need to update tableA's region colum with tableB's region colum. The region colum has changed for some and not for others. With that said, does this look right? UPDATE tableA SET region = (SELECT region FROM tableB WHERE tableB.id = tableA.id) Its been a while since I have done this and I cant remeber the best way to do it. :| Phillip B. www.LoungeRoyale.com www.FillWorks.com ~| 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 This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. http://www.cfhosting.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Update query loop with checkboxes
Janine, I didn't see a sql query in your code-example. The problem you are having appears to be from the difference in behavior between Checkboxes and input/text boxes. If you have multiple text-boxes on a page and one of them is blank, that text box still passes its value (an empty string) to coldfusion. However, a checkbox works very differently. If you check the checkbox, it passes the value to coldfusion that you define in your HTML. However, if the checkbox is NOT checked, NO VALUE IS PASSED TO COLDFUSION, NOT EVEN AN EMPTY STRING. This is kind of stupid to me, but you just have work around it. In the case of multiple textboxes, you can name all the textboxes the same name and all their values will get passed as a big comma-delimited list, even if one item is missing. However, if you are using check boxes, this won't work. Instead you will need to name each of the checkboxes something different, then use a cfaparam name=Checkbox_1 value=No/false/etc... to give them default values on the receiving page. So your checkboxes could be something like this: input type=CheckBox_1 value=yes input type=CheckBox_2 value=yes input type=CheckBox_3 value=yes and on the receiving page: cfaparam name=Checkbox_1 value=No cfaparam name=Checkbox_2 value=No cfaparam name=Checkbox_3 value=No If you do not want the hardcode every checkbox and every param, you can use a query from a database to create them and then process them in a loop using the Evaluate() function. Contact me off list for some example code. Fregas [EMAIL PROTECTED] - Original Message - From: Janine Jakim [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Thursday, February 13, 2003 2:37 PM Subject: Update query loop with checkboxes I know checkboxes have been discussed many times- I searched the archives of hof and cf support and haven't quite found this issue with them so please forgive me if it's been discussed. i have a display page that shows about 80 checkboxes- set in a basic table format. It looks like so English Reading Math Science History Visual Aides X X X Large Print Test X X Math Aids x Etc I need these checkboxes to update in the database. Problem is using a loop causes errors with the checkboxes. So what I can do with an input box (ie: y/n) using #ListGetAt(Attributes.English, x)# Throws an error when there's a blank checkbox. All of my fields have the cfparam So my code works as long as I'm not using a checkbox! (And of course the users want checkboxes and they do look better with so much on the page). Thanks for any help. j Below is my query- the error I get is when an empty checkbox is encountered An error occurred while evaluating the expression: ListFindNoCase(#ListGetAt(Attributes.SOLWriting,x)#) Error near line 25, column 6. ~| 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 Get the mailserver that powers this list at http://www.coolfusion.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Update Query
Obviously a logic error but I cannot be sure. I am a proponent of KISS. On Mon, 22 Apr 2002, Janine Jakim wrote: I have a page that runs an update query. It seems that sometimes the grades update incorrectly or will overwrite the wrong information. This is the query I use- does anyone see anything wrong/could cause this? TIA CFPARAM NAME=SubjectMark DEFAULT= CFSET Courseset=CourseID CFSET Courseset=#Courseset#-1 CFTRANSACTION CFLOOP INDEX=X FROM=1 TO=#ListLen(SubjectMark)# CFQUERY NAME=EditMarks DATASOURCE=#Request.dsn# UPDATE T_StudentMarks SET SubjectMark='#ListGetAt(attributes.SubjectMark,X)#' WHERE StudentID='#StudentID#' and Quarter=#Quarter# And CourseID=#Evaluate(X + Courseset)# /CFQUERY /CFLOOP /CFTRANSACTION __ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Update Query/Output question from a newbie...
Hi Michael, I'll just start at the top, ok? What is distrib_state? I get the impression that it is the actual name of the state. If so, I would suggest that you use state_id instead - I know the names of the states are not going to change but it will save you space and is good database design. Now, modify your query and select to include the state_id... CFQUERY NAME="GetState" DATASOURCE="dbcms" SELECT state_name, state_id FROM tblstates /CFQUERY SELECT NAME="distrib_state" CFOUTPUT QUERY="GetState" OPTION value="#GetState.state_id#"#GetState.state_name#/OPTION /CFOUTPUT /SELECT This means that when you action the form you get the state_id as the value of distrib_state rather then state_name. In the update form where you want to display the state for the distributor you need to add a bit more code to the select so that it 'selects' the state you want: SELECT NAME="distrib_state" CFOUTPUT QUERY="GetState" OPTION value="#GetState.state_id#" cfif GetState.state_id eq Distributor.state_idSELECTED/cfif #GetState.state_name# /OPTION /CFOUTPUT /SELECT I'm not sure why you are using CFOUTPUT QUERY="Distributor" when you know you are only going to get a single record. It does save typing 'distributor' in front of the variable names but I would suggest that this is a false economy. Using full 'dot' notation (as recommended by Bill) will make your maintenance task much easier in another four years time and is (I believe) marginally faster for CF to lookup. If you decide to continue to use CFOUTPUT QUERY="Distributor" then you will need to change the CFOUTPUT QUERY="GetState" in the select to be CFLOOP QUERY="GetState". OK? And don't forget the /cfoutput to /cfloop... I hope that answers your question... Regards Brett Payne-Rhodes B) Eaglehawk Computing Perth, Western Australia Michael Wilson wrote: Hi all and thanks for taking time to look this over. I will try my best to keep it as brief as possible, although I need to give you some background on my problem. Thanks in advance for any help you can provide. I have a client I did a site for about 4 years ago and it have never really been changed much. The site owner asked me to update the look and functionality of the site to make it easier to use. He also requested that I create a system that would allow him to update the Distributors section on his own, so I went with ColdFusion, because I want to learn more about it. I am working on the code for the Distributors first. The original page is http://www.mfay.com/Locations.htm. Don't laugh, it's really Old School... I stared with my Database in MS Access, dbcms.mdb. I have 2 tables, tbldistrib and tblstates. tbldistrib has the following columns: distrib_id (auto number), distrib_name, distrib_city, distrib_state and distrib_phone tblstates has the following columns: state_id (auto number), state_name and state_abb (incase he wants to use abbreviation later) Next I started on the Add New Distributor area of his CMS. I created the insert form using Text type inputs for name, city and phone. I used a select box for the sate and got the select options from tblstates/state_names in the Database. the code I used for the select box: Query: CFQUERY NAME="GetState" DATASOURCE="dbcms" SELECT state_name FROM tblstates /CFQUERY Output: SELECT NAME="distrib_state" CFOUTPUT QUERY="GetState" OPTION #GetState.state_name# /OPTION /CFOUTPUT /SELECT All went according to plan and it works fine. I then created the display page and messed around with the ordering and formatting until I have it real close to the original style of listing the distributors. You can find it at: http://xiondev2.dynip.com/cms/list_distrib.cfm Then I started on the Update Distributor section... I can make this work by using the following Query: CFQUERY DATASOURCE="dbcms" NAME="Distributor" SELECT distrib_name, distrib_city, distrib_state, distrib_phone FROM tbldistrib WHERE distrib_id = #distrib_id# /CFQUERY Output: CFOUTPUT QUERY="Distributor" FORM ACTION="distrib_update.cfm" METHOD="post" INPUT TYPE="hidden" NAME=distrib_id" Value="#distrib_id#" p Distributor Name: INPUT TYPE="text" NAME=distrib_name" SIZE="35" MAXLENGTH="50" Value="#Trim(distrib_name)#" ..and so forth for each form field. What I would like to do is have the "State Name" portion of the form return as a select box as in the insert form, displaying the current distrib_state as the default option while also populating the rest of the select box (options) with the data (state names) from the tblsates/state_names in the database. This way my client can change the Distributor State if he needs to in the same manner as when he adds a new distributor. I tried running 2 Queries and 2 outputs calling a distrib_id in the url (...cfm?distrib_id=30) Query: CFQUERY DATASOURCE="dbcms"
RE: Update Query/Output question from a newbie...
Hi Mike, Invalid tag nesting configuration A query driven CFOUTPUT tag is nested inside a CFOUTPUT tag that also has a QUERY= attribute. This is not allowed. Nesting these tags implies that you want to use grouped processing. However, only the top-level tag can specify the query that drives the processing. I've had a look at the code for the update form and you've got a couple of problems. Firstly, you're missing a bunch of quote marks from the name attribute on your input tags. Secondly, you're missing the 's from the end of your submit and reset buttons Thirdly, you have a nested CFOUTPUT, just as the error says ;o) Basically, you have the right idea, but CF doesn't allow nested CFOUTPUT tags, unless you're using the GROUP attribute in the outer CFOUTPUT. What you need to do is use a CFLOOP for your state select box, like so : Distributor State: SELECT name="distrib_state" CFLOOP query="GetState" cfif GetState.State_ID IS distrib_state cfset selected_var = "selected" cfelse cfset select_var = "" /cfif OPTION VALUE="#GetState.state_id#" #selected_var##GetState.state_name#/OPTION /CFLOOP /SELECTBR The CFOUTPUT for the Distributor query will make sure that the CF variables in the above bit of code get displayed correctly and the CFIF is to make sure that the distributors state is initially correctly selected from the complete list of states. Well, hope that helps. Regards Stephen Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED]
Re: Update Query/Output question from a newbie...
Without going to in depth into what you are trying to do(I get lost after about 5 sentences - mind goes elsewhere ;)) - you may try to use dot notation for your queries, and see if that helps. So in other words, try: cfoutput #query1.var_name# #query2.other_var_name# /cfoutput Dot notation is a very useful thing to learn, even if it doesn't help you in this case, it is extremely handy. -Bill /intraget - Original Message - From: Michael Wilson [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Saturday, October 21, 2000 11:37 PM Subject: Update Query/Output question from a newbie... Hi all and thanks for taking time to look this over. I will try my best to keep it as brief as possible, although I need to give you some background on my problem. Thanks in advance for any help you can provide. I have a client I did a site for about 4 years ago and it have never really been changed much. The site owner asked me to update the look and functionality of the site to make it easier to use. He also requested that I create a system that would allow him to update the Distributors section on his own, so I went with ColdFusion, because I want to learn more about it. I am working on the code for the Distributors first. The original page is http://www.mfay.com/Locations.htm. Don't laugh, it's really Old School... I stared with my Database in MS Access, dbcms.mdb. I have 2 tables, tbldistrib and tblstates. tbldistrib has the following columns: distrib_id (auto number), distrib_name, distrib_city, distrib_state and distrib_phone tblstates has the following columns: state_id (auto number), state_name and state_abb (incase he wants to use abbreviation later) Next I started on the Add New Distributor area of his CMS. I created the insert form using Text type inputs for name, city and phone. I used a select box for the sate and got the select options from tblstates/state_names in the Database. the code I used for the select box: Query: CFQUERY NAME="GetState" DATASOURCE="dbcms" SELECT state_name FROM tblstates /CFQUERY Output: SELECT NAME="distrib_state" CFOUTPUT QUERY="GetState" OPTION #GetState.state_name# /OPTION /CFOUTPUT /SELECT All went according to plan and it works fine. I then created the display page and messed around with the ordering and formatting until I have it real close to the original style of listing the distributors. You can find it at: http://xiondev2.dynip.com/cms/list_distrib.cfm Then I started on the Update Distributor section... I can make this work by using the following Query: CFQUERY DATASOURCE="dbcms" NAME="Distributor" SELECT distrib_name, distrib_city, distrib_state, distrib_phone FROM tbldistrib WHERE distrib_id = #distrib_id# /CFQUERY Output: CFOUTPUT QUERY="Distributor" FORM ACTION="distrib_update.cfm" METHOD="post" INPUT TYPE="hidden" NAME=distrib_id" Value="#distrib_id#" p Distributor Name: INPUT TYPE="text" NAME=distrib_name" SIZE="35" MAXLENGTH="50" Value="#Trim(distrib_name)#" ..and so forth for each form field. What I would like to do is have the "State Name" portion of the form return as a select box as in the insert form, displaying the current distrib_state as the default option while also populating the rest of the select box (options) with the data (state names) from the tblsates/state_names in the database. This way my client can change the Distributor State if he needs to in the same manner as when he adds a new distributor. I tried running 2 Queries and 2 outputs calling a distrib_id in the url (...cfm?distrib_id=30) Query: CFQUERY DATASOURCE="dbcms" NAME="GetState" SELECT state_name FROM tblstates /CFQUERY Output: SELECT NAME="distrib_state" CFOUTPUT QUERY="GetState" OPTION #GetState.state_name# /OPTION OPTION #Trim(distrib_state)# OPTION /CFOUTPUT /SELECT and received errors: Invalid tag nesting configuration A query driven CFOUTPUT tag is nested inside a CFOUTPUT tag that also has a QUERY= attribute. This is not allowed. Nesting these tags implies that you want to use grouped processing. However, only the top-level tag can specify the query that drives the processing. I moved things around several times and got several different results and errors, lol but nothing like what I was thinking about. Any ideas on how I can accomplish what I am looking for? Should I go back to the insert form and have him manually enter the state information and forget about the select box and the tblstates idea? The actual goal was to learn _how_ to do this rather than it being a "must have" in the system, but I am out of ideas. Any help is much appreciated and I am sorry for being long on words, but I wanted to make sure my question was clear enough to elicit clear answers that I can understand. I have placed all the CF code in .txt files at: http://xiondev2.dynip.com/cms/cftext/insertform.txt
Re: Update Query
Miriam: If they can change everything, then you really have nothing to tie the fields together, so the db doesn't know which to change. In these instances, a unique identifier should be used. Hope this helps. Joel "Miriam Hirschman" [EMAIL PROTECTED] wrote in message 01bfcbe4$51bd3680$6baa70d8@mhirschman">news:01bfcbe4$51bd3680$6baa70d8@mhirschman... How can I specify which row in my table I want to update if the user has the ability to change every field. Is there any way besides making an autoNumber field? I have an Access DB. Thanks, ---miriam -- Archives: http://www.eGroups.com/list/cf-talk To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=stsbody=sts/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. -- Archives: http://www.eGroups.com/list/cf-talk To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
RE: Update Query
Something like this should work: UPDATE TableName SET Field1 = #FORM.Field1#, Field2 = #FORM.Field2# WHERE Field1 = #Current value of Field1# The WHERE clause applies to the existing value. AFTER the update, the value will reflect the change the user made. To get the current value of Field1, you can either pass a variable from a query on the form page, or some other method. Jesse D. Roberts Business Process Analyst Procurement Quality Assurance Boeing - Huntington Beach, CA -Original Message- From: Miriam Hirschman [SMTP:[EMAIL PROTECTED]] Sent: Thursday, June 01, 2000 9:13 AM To: CF Forum Subject: Update Query How can I specify which row in my table I want to update if the user has the ability to change every field. Is there any way besides making an autoNumber field? I have an Access DB. Thanks, ---miriam -- Archives: http://www.eGroups.com/list/cf-talk To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. -- Archives: http://www.eGroups.com/list/cf-talk To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
Re: Update Query
If I understand correctly, you could save the original values for the primary key (fields uniquely identifying the record) in variables and then use those #variables# in your cfquery "WHERE" statement. --Serge - Original Message - From: Miriam Hirschman [EMAIL PROTECTED] To: CF Forum [EMAIL PROTECTED] Sent: Thursday, June 01, 2000 11:13 AM Subject: Update Query How can I specify which row in my table I want to update if the user has the ability to change every field. Is there any way besides making an autoNumber field? I have an Access DB. Thanks, ---miriam -- Archives: http://www.eGroups.com/list/cf-talk To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=stsbody=sts/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. -- Archives: http://www.eGroups.com/list/cf-talk To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
Re: Update Query
You need a primary key field that the user shouldn't be able to modify. Autonumber is the easiest way to do it in Access. That's the only way to uniquely identify a single record in your table. Otherwise, your data integrity would be compromised. --- Miriam Hirschman [EMAIL PROTECTED] wrote: How can I specify which row in my table I want to update if the user has the ability to change every field. Is there any way besides making an autoNumber field? I have an Access DB. Thanks, ---miriam -- Archives: http://www.eGroups.com/list/cf-talk To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. __ Do You Yahoo!? Send instant messages get email alerts with Yahoo! Messenger. http://im.yahoo.com/ -- Archives: http://www.eGroups.com/list/cf-talk To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
