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
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).
Need more information ... what type of data is in the checkbox values?
how is the table set up? how does the checkbox data relate to the other
data in the form and in the table(s)?
~|
Want to reach the ColdFusion community
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
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
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.
.
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
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
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
[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
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
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
Hi there.
I played around with the concept of what you're doing, and it is sound. I built
this simple template to prove it:
cfquery name=getbits datasource=mydatasource
select * from bittest
/cfquery
cfdump var=#getbits#
cfset column_string = bool1=1,bool2=0,bool3=1
cfquery
Hi,
I'm trying to construct a dynamic sql server update statement on a table with a
large number of fields, most of them type bit. First I evaluate all the
yes/no fields and convert them to 1/0. Then I build a string of column
(field) names and values, e.g, {name of field1}=0, {name of
define bombs and do a cfoutput on #column_string# to see exactly
what SQL you're generating.
On 6/28/07, david cowen [EMAIL PROTECTED] wrote:
Hi,
I'm trying to construct a dynamic sql server update statement on a table with
a large number of fields, most of them type bit. First I evaluate
.
..:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.
Bobby Hartsfield
http://acoderslife.com
-Original Message-
From: david cowen [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 28, 2007 6:44 PM
To: CF-Talk
Subject: how can I dynamically build field names string in a sql update
statement?
Hi,
I'm trying
field2=1, set field3=0 portion.
-Original Message-
From: Bobby Hartsfield [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 28, 2007 6:20 PM
To: CF-Talk
Subject: RE: how can I dynamically build field names string in a sql update
statement?
Well, without trying the code out or looking real
If I remove the quote marks, then the program fails on line 0 of this
routine; i.e., this code does not execute. With the quote marks, the
routine gets executed but fails and there is a sql statement in the
resulting error message. The sql statement has quote marks around the set
field1=0, set
Message-
From: David S. Cowen [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 28, 2007 9:03 PM
To: CF-Talk
Subject: RE: how can I dynamically build field names string in a sql update
statement?
If I remove the quote marks, then the program fails on line 0 of this
routine; i.e., this code does
With Oracle you use the pipe ||..
update foo
set fooname = fooname || 'Customer'
On 1/20/06, Ken [EMAIL PROTECTED] wrote:
Hi. This may be a little elementary, but here it is:
I want to update a database field so that all values in it are appended with
a given string. Something like this:
What database are you using?
--Ferg
Ken wrote:
Hi. This may be a little elementary, but here it is:
I want to update a database field so that all values in it are appended with
a given string. Something like this:
UPDATE db_names
SET db_names.lastName = db_names.lastName + 'Customer'
I know
In MSSQL:
update authors
set city = city+'s'
where au_lname = 'Smith'
-Original Message-
From: Ken [mailto:[EMAIL PROTECTED]
Sent: Friday, January 20, 2006 9:49 AM
To: CF-Talk
Subject: SQL Update
Hi. This may be a little elementary, but here it is:
I want to update
MS SQL Server 2000... Sorry i should have mentioned before.
On 1/20/06, Ken Ferguson [EMAIL PROTECTED] wrote:
What database are you using?
--Ferg
Ken wrote:
Hi. This may be a little elementary, but here it is:
I want to update a database field so that all values in it are appended
Ken wrote:
Hi. This may be a little elementary, but here it is:
I want to update a database field so that all values in it are appended with
a given string. Something like this:
UPDATE db_names
SET db_names.lastName = db_names.lastName + 'Customer'
I know the above query is wrong. Can
In that case, I believe it should work as you posted it.
--Ferg
Ken wrote:
MS SQL Server 2000... Sorry i should have mentioned before.
On 1/20/06, Ken Ferguson [EMAIL PROTECTED] wrote:
What database are you using?
--Ferg
Ken wrote:
Hi. This may be a little elementary, but here it
I often come across this same thing, but I am using MySQL. Does anyone
know how to append a string to a field in MySQL?
Thanks,
Aaron
On 1/20/06, Ken Ferguson [EMAIL PROTECTED] wrote:
In that case, I believe it should work as you posted it.
--Ferg
Ken wrote:
MS SQL Server 2000... Sorry i
Aaron,
I Googled MySQL concatenation operator and eventually came to the
CONCAT function, pasted below. I think searching for
DB_SOFTWARE concatenation operator should help others find their
db-specific answers as well.
HTH,
Matthieu
CONCAT(str1,str2,...)
Returns the string that results
Thanks!!!
On 1/20/06, Cornillon, Matthieu (Consultant)
[EMAIL PROTECTED] wrote:
Aaron,
I Googled MySQL concatenation operator and eventually came to the
CONCAT function, pasted below. I think searching for
DB_SOFTWARE concatenation operator should help others find their
db-specific answers
From the error message returned ('Client_ID ='), it looks like #FORM.Client_ID# is empty.
Okay, I have managed to pull my head out of my @$$ for part of the day
and now I'm stuck on this, for a bit now - seems like days.
I have a form that displays current info just fine but I want to be
able to
Thank you Paul and Nate.
Dan
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
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.
Dan
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
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
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
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#
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
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
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
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:
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
Errr...that's the main purpose of a primary key. Generally a unique value
for each record. Without it, you run into...well, the problem you're
describing.
-Original Message-
From: Ed Gordon [mailto:NetDr;callptc.com]
Sent: Thursday, November 07, 2002 1:26 PM
To: CF-Talk
Subject: SQL
How does one CFQUERY UPDATE a particular record on an SQL database? That
is the general question...
It may sound simple, but if you were to read a record using SELECT, if
more
than one record matched the criteria in a WHERE clause, how could you
UPDATE
just one of them?
I have been using
Well you would have to use all elements in the table which would
make that record unique.
Most all tables I use have an ID number assigned using
the IDENTITY seed.
Thus I can easily update the record based on the ID field.
Similar to the UUID but alot smaller.
[EMAIL PROTECTED] 11/07/02
). Many would argue that even in a case like this, you should
use a numeric primary key.
Matthew Walker
http://www.matthewwalker.net.nz/
-Original Message-
From: Ed Gordon [mailto:NetDr;callptc.com]
Sent: Friday, 8 November 2002 7:26 a.m.
To: CF-Talk
Subject: SQL UPDATE
Use the primary key for the table... If a table is designed in such a
way that there is no way to construct a query to pull any single
record, then it's not a database, it's a spreadsheet ;)
--
jon
mailto:jonhall;ozline.net
Thursday, November 7, 2002, 1:26:03 PM, you wrote:
EG How does one
Subject: SQL UPDATE - identifying a record?
How does one CFQUERY UPDATE a particular record on an SQL database? That
is the general question...
It may sound simple, but if you were to read a record using SELECT, if more
than one record matched the criteria in a WHERE clause, how could you UPDATE
: SQL Update Increment Value
Is there a way to increment an int field in sql via the update statement
without doing a select query in cf, then an update? Thanks for filling
the hole.
Regards,
Eric J. Hoffman
Director of Internet Development
DataStream Connexion, LLC
(formerly Small Dog Design
Message-
From: Eric J Hoffman [mailto:[EMAIL PROTECTED]]
Sent: Monday, April 01, 2002 11:30 AM
To: CF-Talk
Subject: SQL Update Increment Value
Is there a way to increment an int field in sql via the update statement
without doing a select query in cf, then an update? Thanks for filling
the hole
How about
Update myTable
Set myIntField = myIntField + 1
Where id = something
- Original Message -
From: Eric J Hoffman [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Monday, April 01, 2002 11:29 AM
Subject: SQL Update Increment Value
Is there a way to increment an int field
Thanks Joeseph and Brian, Im really close nowwhats happening is the
SubQuery is returning more than 1 row which wont work. If I put a
MAX(Date1) it works but I get incorrect results. I get the MAX date for ALL
the REFNO instead of just the single set. Any ideas how to get around that?
I
This is the syntax that worked, apparently you cannot set (SQL7)
an alias within the UPDATE clause
UPDATE mytable
SET Date1 =
(SELECT max(T2.Date1)
FROM mytable T2
WHERE T2.RefNo = mytable.RefNo AND T2.Date1 IS NOT NULL)
WHERE Date1 IS NULL
Thanks to all...
.
-Original Message-
From: Adrian Cesana [mailto:[EMAIL PROTECTED]]
Sent: 26 April 2001 17:34
To: CF-Talk
Subject: RE: SQL UPDATE
Thanks Joeseph and Brian, Im really close nowwhats happening is the
SubQuery is returning more than 1 row which wont work. If I put a
MAX(Date1) it works
doh!, actually I tried that initially but receive a Syntax error, so I
removed them thinking they were not needed. Im running SQL7 and testing the
code in ISQL. I have the syntax exactly like shown below but receive an
err
Not sure what to try next.
-Original Message-
Adrian
Try qualifying DATE1 with the table, like m.DATE1 or t.DATE1
Ben
-Original Message-
From: Adrian Cesana [mailto:[EMAIL PROTECTED]]
Sent: Thursday, April 26, 2001 10:25 AM
To: CF-Talk
Subject: RE: SQL UPDATE
doh!, actually I tried that initially but receive a Syntax error, so I
are not the
intended recipient, you are not authorised to disclose, copy, distribute, or
retain this message. Please notify us on +44 (0)207 387 8890.
-Original Message-
From: Jones, Becky [mailto:[EMAIL PROTECTED]]
Sent: 25 April 2001 18:06
To: CF-Talk
Subject: sql update
i want to update
-
From: Adrian Cesana [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, April 25, 2001 6:24 PM
To: CF-Talk
Subject: SQL UPDATE
I have kind of a strange update I need to run and cant figure
out how to
write the thing properly, I think Im making it more difficult
than it is,
maybe not.
Here
:38 PM
To: CF-Talk
Subject: RE: SQL UPDATE
UPDATE mytable
SET DATE1 = (SELECT DATE1
FROM mytable
WHERE REFNO = 123
ANDID = 1)
WHERE DATE1 IS NULL
That should do it for you. As long as the subquery returns only one row,
this will work
up
with a 'different' solution.
Todd Ashworth
- Original Message -
From: "Jon Hall" [EMAIL PROTECTED]
To: "CF-Talk" [EMAIL PROTECTED]
Sent: Thursday, April 19, 2001 9:48 PM
Subject: SQL update not executing?
OK, no response on the question earlier, but ma
;Todd Ashworth" [EMAIL PROTECTED]
To: "CF-Talk" [EMAIL PROTECTED]
Sent: Thursday, April 19, 2001 11:03 PM
Subject: Re: SQL update not executing?
This has nothing to do with your current question, but as to your earlier
question, our DBA says it can't be done on the application le
with that and see if it helps Phillip.
Jeremy Allen
elliptIQ Inc.
-Original Message-
From: Philip Arnold - ASP [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, February 20, 2001 9:33 AM
To: CF-Talk
Subject: SQL Update - Now I'm royally confused
OK, after that SP question, and some experimentation
I have not tested this but try
UPDATE t1
SET table1.Title=table2.Title,
table1.Body=table2.Body,
table1.Section=table2.Section
FROM table1 t1
INNER JOIN table2 t2 ON(t1.ID = t2.ID)
I think the way you are doing it will not work
because of your placement of the join clause.
Hint look at the SELECT INTO Statement
-Original Message-
From: Philip Arnold - ASP [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, February 20, 2001 8:16 AM
To: CF-Talk
Subject: RE: SQL Update - Now I'm royally confused
I have not tested this but try
UPDATE t1
SET table1.Title=table2
Hint look at the SELECT INTO Statement
Normally I wouldn't have this problem, but I think the 80+ hour weeks are
catching me up
Philip Arnold
Director
Certified ColdFusion Developer
ASP Multimedia Limited
T: +44 (0)20 8680 1133
"Websites for the real world"
it would be to run a
second query, after the update, like:
select count(*)
from table
where name = "#name#"
Bob
-Original Message-
From: Mak Wing Lok [mailto:[EMAIL PROTECTED]]
Sent: Friday, January 12, 2001 10:52 PM
To: CF-Talk
Subject: SQL update, how to count afftected
I have an update form that when the update is completed, the contents of
the form is emailed to the respective recipient and then I javascript
alert appears telling the user that the update was successful. The
problem is that while the update is working fine neither the CFMAIL or
javascript alert
: James Taavon [mailto:[EMAIL PROTECTED]]
Sent: 03 January 2001 16:13
To: CF-Talk
Subject: CFMAIL/Javascript not executing after SQL Update
I have an update form that when the update is completed, the contents of
the form is emailed to the respective recipient and then I javascript
alert appears
not executing after SQL Update
I have an update form that when the update is completed, the contents of
the form is emailed to the respective recipient and then I javascript
alert appears telling the user that the update was successful. The
problem is that while the update is working fine neither
January 2001 17:17
To: CF-Talk
Subject: Re: CFMAIL/Javascript not executing after SQL Update
This is not my first attempt with CFMAIL. It has worked before just fine
the way i have it. That is why I was stumped when it did not work as
always.
Martin Sutton wrote:
I usually like to specify
em manager.
**
-Original Message-
From: James Taavon [mailto:[EMAIL PROTECTED]]
Sent: 03 January 2001 16:13
To: CF-Talk
Subject: CFMAIL/Javascript not executing after SQL Update
I have an update form that when the update is completed, the contents of
the form is emailed to the
-
From: "Martin Sutton" [EMAIL PROTECTED]
To: "CF-Talk" [EMAIL PROTECTED]
Sent: Wednesday, January 03, 2001 11:19 AM
Subject: RE: CFMAIL/Javascript not executing after SQL Update
I usually like to specify a mail server within the CFMAIL tag.
You have CFOUTPUT tags enclos
t;#Your message here#");
self.location='staff.cfm';
/script
/cfoutput
jon
- Original Message -
From: "Martin Sutton" [EMAIL PROTECTED]
To: "CF-Talk" [EMAIL PROTECTED]
Sent: Wednesday, January 03, 2001 11:19 AM
Subject: RE: CFMAIL/Javascript not execu
or I receive is:
ODBC Error Code = 22005 (Error in assignment)
[Microsoft][ODBC Microsoft Access Driver] Data type
mismatch in criteria
expression.
SQL = "UPDATE tbldistrib SET distrib_name='Calico
Corners III',
distrib_city='Charlston', distrib_state='SOUTH
CAROLINA',
distrib_phon
notify us on +44 (0)207 387 8890.
-Original Message-
From: Michael Wilson [mailto:[EMAIL PROTECTED]]
Sent: 23 October 2000 17:45
To: CF-Talk
Subject: SQL UPDATE question (was Update Query/Output question from a
newbie)
Hi again to everyone,
I would like to thank Stephen and Bret
, July 11, 2000 11:21 AM
To: [EMAIL PROTECTED]
Subject: SQL UPDATE
I am trying to update records where a field is NULL and another field from
that table matches to another field in a different table. Im trying to use
somthing like this but it does not seem to work:
UPDATE table1,table2
SET table1
73 matches
Mail list logo
