Re: Query-of-Query Issue

2007-02-12 Thread Mary Jo Sminkey
>Does the original query passed to the CFC come from a database itself, or
>are you creating it some other way?

Ah, that was the answer Dave! I checked again and it does go through a function 
that rewrites the query, and that seems to be what is causing the problem, 
although it doesn't show up until I hit that QoQ line. Apparently the field 
type is getting lost along the way.

MJS
 

~|
Upgrade to Adobe ColdFusion MX7 
Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs 
http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:269606
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4


Re: Query-of-Query Issue

2007-02-12 Thread Mary Jo Sminkey
>The main problem with your situation is that there shouldn't be a list in a
>database field.  There should be a separate table that creates the
>one-to-many or many-to-many relationship.  By doing that, your problems
>would be solved.

No, that does not help much at all. Without going into details about the 
application, there's good reasons it's in a list here.

MJS



~|
Upgrade to Adobe ColdFusion MX7 
Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs 
http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:269605
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4


Re: Query-of-Query Issue

2007-02-12 Thread Mary Jo Sminkey
>Does the original query passed to the CFC come from a database itself, or
>are you creating it some other way?

It's a database query, although it does get manipulated along the way. This 
particular field however should be the same as what is pulled from the 
database. 

MJS

~|
Upgrade to Adobe ColdFusion MX7 
Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs 
http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:269603
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4


Re: Query-of-Query Issue

2007-02-12 Thread Matt Quackenbush
To quote someone else:

"Every time you store a comma-delimited list in a database field God kills a
kitty."

The main problem with your situation is that there shouldn't be a list in a
database field.  There should be a separate table that creates the
one-to-many or many-to-many relationship.  By doing that, your problems
would be solved.

Hope that helps.


Matt


On 2/12/07, Mary Jo Sminkey <[EMAIL PROTECTED]> wrote:
>
> I've run into a strange query-of-query issue and wondering if anyone else
> has seen this or knows of a work-around. Basically, I'm passing a query into
> a CFC and then running this QoQ:
>
> 
>SELECT DISTINCT Item_ID, Related_Items FROM arguments.theQuery
> 
>
> Related_Items is a text field with a list of IDs. The problem I am having
> is that when this runs, if Related_Items just has a single ID (or none), it
> works fine. But if it has several items in the list, the QoQ doesn't return
> the list, it apparently is converting it to a date field.
>
> Any ideas?
>


~|
Upgrade to Adobe ColdFusion MX7 
Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs 
http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:269600
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4


RE: Query-of-Query Issue

2007-02-12 Thread Dave Watts
> I've run into a strange query-of-query issue and wondering if 
> anyone else has seen this or knows of a work-around. 
> Basically, I'm passing a query into a CFC and then running this QoQ: 
> 
> 
>SELECT DISTINCT Item_ID, Related_Items FROM 
> arguments.theQuery 
> 
> Related_Items is a text field with a list of IDs. The problem 
> I am having is that when this runs, if Related_Items just has 
> a single ID (or none), it works fine. But if it has several 
> items in the list, the QoQ doesn't return the list, it 
> apparently is converting it to a date field. 

Does the original query passed to the CFC come from a database itself, or
are you creating it some other way?

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!


~|
Upgrade to Adobe ColdFusion MX7 
Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs 
http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:269599
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4


Query-of-Query Issue

2007-02-12 Thread Mary Jo Sminkey
I've run into a strange query-of-query issue and wondering if anyone else has 
seen this or knows of a work-around. Basically, I'm passing a query into a CFC 
and then running this QoQ: 


   SELECT DISTINCT Item_ID, Related_Items FROM arguments.theQuery


Related_Items is a text field with a list of IDs. The problem I am having is 
that when this runs, if Related_Items just has a single ID (or none), it works 
fine. But if it has several items in the list, the QoQ doesn't return the list, 
it apparently is converting it to a date field. 

Any ideas? 


~|
Upgrade to Adobe ColdFusion MX7 
Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs 
http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:269598
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4


Re: odd query issue returning 0 results in CFMC 7.2

2007-01-30 Thread Charlie Griefer
d'oH! :)

On 1/30/07, Steve Dworman <[EMAIL PROTECTED]> wrote:
> problem solved.  forgot to commit new database changes.
>
> > well...i took a look at the query in the debugging info.  then i took
> > that query, which has the coldfusion variables parsed already, and ran
> > it on the command line.  so yeah, i know i'm using the same query.
> >
> >
> > > how are you sure it's the same query?  are there variables involved
> >
> > > in
> > > the CFMX code?  are you sure those variables are evaluating to what
> > > you expect?
> > >
> > > On 1/30/07, Steve Dworman <[EMAIL PROTECTED]> wrote:
> > > > i have a query that returns results when using sqlplus.  however,
> >
> > > when i run the same query in cfmx there are 0 records returned.  has
> >
> > > anyone experienced this issue? is this a bug?
> > > >
> > > > tia
> > > >
> > > >
>
> 

~|
Upgrade to Adobe ColdFusion MX7 
Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs 
http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:268098
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4


Re: odd query issue returning 0 results in CFMC 7.2

2007-01-30 Thread Steve Dworman
problem solved.  forgot to commit new database changes.

> well...i took a look at the query in the debugging info.  then i took 
> that query, which has the coldfusion variables parsed already, and ran 
> it on the command line.  so yeah, i know i'm using the same query.
> 
> 
> > how are you sure it's the same query?  are there variables involved 
> 
> > in
> > the CFMX code?  are you sure those variables are evaluating to what
> > you expect?
> > 
> > On 1/30/07, Steve Dworman <[EMAIL PROTECTED]> wrote:
> > > i have a query that returns results when using sqlplus.  however, 
> 
> > when i run the same query in cfmx there are 0 records returned.  has 
> 
> > anyone experienced this issue? is this a bug?
> > >
> > > tia
> > >
> > > 

~|
Upgrade to Adobe ColdFusion MX7 
Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs 
http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:268097
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4


Re: odd query issue returning 0 results in CFMC 7.2

2007-01-30 Thread Steve Dworman
well...i took a look at the query in the debugging info.  then i took that 
query, which has the coldfusion variables parsed already, and ran it on the 
command line.  so yeah, i know i'm using the same query.


> how are you sure it's the same query?  are there variables involved 
> in
> the CFMX code?  are you sure those variables are evaluating to what
> you expect?
> 
> On 1/30/07, Steve Dworman <[EMAIL PROTECTED]> wrote:
> > i have a query that returns results when using sqlplus.  however, 
> when i run the same query in cfmx there are 0 records returned.  has 
> anyone experienced this issue? is this a bug?
> >
> > tia
> >
> > 

~|
Upgrade to Adobe ColdFusion MX7 
Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs 
http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:268096
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4


Re: odd query issue returning 0 results in CFMC 7.2

2007-01-30 Thread Charlie Griefer
how are you sure it's the same query?  are there variables involved in
the CFMX code?  are you sure those variables are evaluating to what
you expect?

On 1/30/07, Steve Dworman <[EMAIL PROTECTED]> wrote:
> i have a query that returns results when using sqlplus.  however, when i run 
> the same query in cfmx there are 0 records returned.  has anyone experienced 
> this issue? is this a bug?
>
> tia
>
> 

~|
Upgrade to Adobe ColdFusion MX7 
Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs 
http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:268095
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4


odd query issue returning 0 results in CFMC 7.2

2007-01-30 Thread Steve Dworman
i have a query that returns results when using sqlplus.  however, when i run 
the same query in cfmx there are 0 records returned.  has anyone experienced 
this issue? is this a bug?

tia

~|
Upgrade to Adobe ColdFusion MX7 
Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs 
http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:268094
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4


RE: Odd Query issue - SOLVED

2006-07-26 Thread Scott Mulholland
Thanks for all help all, turns out a trigger on the table was referencing
the job_id field in a different table that was recently changed.

All the queries work now.


-Original Message-
From: Brian Rinaldi [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, July 26, 2006 1:34 PM
To: CF-Talk
Subject: Re: Odd Query issue

This may seem silly but did you pop this into query analyzer (assuming this
is MS SQL...I didn't see you mention what database it is). I have found that
when you get odd errors like this back from a query sometimes they are
misreporting the error...if you try it in query analyzer you may get a
clearer error message. My guess is the problem lies in the word satus and
not in job_id...so try bracketing that keyword (which btw using Query
Analyzer will highlight potential keyword conflicts for you too). Hope that
helps.

- Brian Rinaldi
blog - http://www.remotesynthesis.com/blog
CF Open Source List - http://www.remotesynthesis.com/cfopensourcelist
Boston CFUG - http://www.bostoncfug.org




~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four 
times a year.
http://www.fusionauthority.com/quarterly

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:247811
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4


Re: Odd Query issue

2006-07-26 Thread Robertson-Ravo, Neil (RX)
Looks like you have an update trigger on the update. 







"This e-mail is from Reed Exhibitions (Oriel House, 26 The Quadrant,
Richmond, Surrey, TW9 1DL, United Kingdom), a division of Reed Business,
Registered in England, Number 678540.  It contains information which is
confidential and may also be privileged.  It is for the exclusive use of the
intended recipient(s).  If you are not the intended recipient(s) please note
that any form of distribution, copying or use of this communication or the
information in it is strictly prohibited and may be unlawful.  If you have
received this communication in error please return it to the sender or call
our switchboard on +44 (0) 20 89107910.  The opinions expressed within this
communication are not necessarily those expressed by Reed Exhibitions." 
Visit our website at http://www.reedexpo.com

-Original Message-
From: Scott Mulholland <[EMAIL PROTECTED]>
To: CF-Talk 
Sent: Wed Jul 26 20:29:47 2006
Subject: RE: Odd Query issue

Yeah, its ms-sql, and yes, does seem silly that I hadn't done that yet ;)

Query analyzer gave me the same result for the update but with a better
error message, "Invalid column job_id, Procedure jobs_UTrig".  Looks like
it's an issue with one of my relationships/constraints.

Thanks,
Scott

-Original Message-
From: Brian Rinaldi [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, July 26, 2006 1:34 PM
To: CF-Talk
Subject: Re: Odd Query issue

This may seem silly but did you pop this into query analyzer (assuming this
is MS SQL...I didn't see you mention what database it is). I have found that
when you get odd errors like this back from a query sometimes they are
misreporting the error...if you try it in query analyzer you may get a
clearer error message. My guess is the problem lies in the word satus and
not in job_id...so try bracketing that keyword (which btw using Query
Analyzer will highlight potential keyword conflicts for you too). Hope that
helps.

- Brian Rinaldi
blog - http://www.remotesynthesis.com/blog
CF Open Source List - http://www.remotesynthesis.com/cfopensourcelist
Boston CFUG - http://www.bostoncfug.org






~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four 
times a year.
http://www.fusionauthority.com/quarterly

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:247810
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4


RE: Odd Query issue

2006-07-26 Thread Scott Mulholland
Yeah, its ms-sql, and yes, does seem silly that I hadn't done that yet ;)

Query analyzer gave me the same result for the update but with a better
error message, "Invalid column job_id, Procedure jobs_UTrig".  Looks like
it's an issue with one of my relationships/constraints.

Thanks,
Scott

-Original Message-
From: Brian Rinaldi [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, July 26, 2006 1:34 PM
To: CF-Talk
Subject: Re: Odd Query issue

This may seem silly but did you pop this into query analyzer (assuming this
is MS SQL...I didn't see you mention what database it is). I have found that
when you get odd errors like this back from a query sometimes they are
misreporting the error...if you try it in query analyzer you may get a
clearer error message. My guess is the problem lies in the word satus and
not in job_id...so try bracketing that keyword (which btw using Query
Analyzer will highlight potential keyword conflicts for you too). Hope that
helps.

- Brian Rinaldi
blog - http://www.remotesynthesis.com/blog
CF Open Source List - http://www.remotesynthesis.com/cfopensourcelist
Boston CFUG - http://www.bostoncfug.org




~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four 
times a year.
http://www.fusionauthority.com/quarterly

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:247808
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4


Re: Odd Query issue

2006-07-26 Thread Brian Rinaldi
This may seem silly but did you pop this into query analyzer (assuming this
is MS SQL...I didn't see you mention what database it is). I have found that
when you get odd errors like this back from a query sometimes they are
misreporting the error...if you try it in query analyzer you may get a
clearer error message. My guess is the problem lies in the word satus and
not in job_id...so try bracketing that keyword (which btw using Query
Analyzer will highlight potential keyword conflicts for you too). Hope that
helps.

- Brian Rinaldi
blog - http://www.remotesynthesis.com/blog
CF Open Source List - http://www.remotesynthesis.com/cfopensourcelist
Boston CFUG - http://www.bostoncfug.org


~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four 
times a year.
http://www.fusionauthority.com/quarterly

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:247786
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4


Re: Odd Query issue

2006-07-26 Thread Robertson-Ravo, Neil (RX)
Does the update work in Query Analyser? Which DB is it again? What drivers
you using (and what ColdFusion)








"This e-mail is from Reed Exhibitions (Oriel House, 26 The Quadrant,
Richmond, Surrey, TW9 1DL, United Kingdom), a division of Reed Business,
Registered in England, Number 678540.  It contains information which is
confidential and may also be privileged.  It is for the exclusive use of the
intended recipient(s).  If you are not the intended recipient(s) please note
that any form of distribution, copying or use of this communication or the
information in it is strictly prohibited and may be unlawful.  If you have
received this communication in error please return it to the sender or call
our switchboard on +44 (0) 20 89107910.  The opinions expressed within this
communication are not necessarily those expressed by Reed Exhibitions." 
Visit our website at http://www.reedexpo.com

-Original Message-
From: Scott Mulholland <[EMAIL PROTECTED]>
To: CF-Talk 
Sent: Wed Jul 26 17:42:27 2006
Subject: RE: Odd Query issue

I just wrote these two tests and ran them, same result...the select works,
the update gives "Invalid column name 'job_id'."


Select * from jobs where job_id = 1



Update jobs set status = 0 where job_id = 1



Scott

-Original Message-
From: Ben Nadel [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, July 25, 2006 3:02 PM
To: CF-Talk
Subject: RE: Odd Query issue

Can you actually post the CFQuery codes so we can see if maybe there is
something you are not seeing.

.
Ben Nadel 
www.bennadel.com

-Original Message-
From: Scott Mulholland [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, July 25, 2006 1:23 PM
To: CF-Talk
Subject: Odd Query issue

I have a query today that stopped working with an invalid column error.  The
database did not change.  Select statements work on it but not updates.

 

Select * from jobs where job_id = 1 - works fine

 

Update jobs set status = 0 where job_id = 1 - results in Invalid column name
'job_id'.

 

Any ideas what would cause this to just start happening?

 

Thanks,
Scott









~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four 
times a year.
http://www.fusionauthority.com/quarterly

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:247784
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4


Re: Odd Query issue

2006-07-26 Thread Brian Rinaldi
This may seem silly but did you pop this into query analyzer (assuming this
is MS SQL...I didn't see you mention what database it is). I have found that
when you get odd errors like this back from a query sometimes they are
misreporting the error...if you try it in query analyzer you may get a
clearer error message. My guess is the problem lies in the word satus and
not in job_id...so try bracketing that keyword (which btw using Query
Analyzer will highlight potential keyword conflicts for you too). Hope that
helps.

- Brian Rinaldi
blog - http://www.remotesynthesis.com/blog
CF Open Source List - http://www.remotesynthesis.com/cfopensourcelist
Boston CFUG - http://www.bostoncfug.org

On 7/26/06, Scott Mulholland <[EMAIL PROTECTED]> wrote:
>
> I tried with a qryparam yesterday, no change.  Selecting job_id from jobs
> works as well.
>
> If I run select * from information_schema.columns where table_name =
> 'jobs'
> the column comes back as well.
>
> This is one of those weird "it worked fine for yrs up until yesterday and
> nothing in the code or table changed" things.  I'm thinking something is
> corrupt in the database.
>
> -Original Message-
> From: Robertson-Ravo, Neil (RX)
> [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, July 26, 2006 12:32 PM
> To: CF-Talk
> Subject: Re: Odd Query issue
>
> What happens if you put it into a cfqueryparam?
>
> What happens if you select job_id from jobs?
>
>
>
>
>
>
>
> "This e-mail is from Reed Exhibitions (Oriel House, 26 The Quadrant,
> Richmond, Surrey, TW9 1DL, United Kingdom), a division of Reed Business,
> Registered in England, Number 678540.  It contains information which is
> confidential and may also be privileged.  It is for the exclusive use of
> the
> intended recipient(s).  If you are not the intended recipient(s) please
> note
> that any form of distribution, copying or use of this communication or the
> information in it is strictly prohibited and may be unlawful.  If you have
> received this communication in error please return it to the sender or
> call
> our switchboard on +44 (0) 20 89107910.  The opinions expressed within
> this
> communication are not necessarily those expressed by Reed Exhibitions."
> Visit our website at http://www.reedexpo.com
>
> -Original Message-
> From: Scott Mulholland <[EMAIL PROTECTED]>
> To: CF-Talk 
> Sent: Wed Jul 26 17:42:27 2006
> Subject: RE: Odd Query issue
>
> I just wrote these two tests and ran them, same result...the select works,
> the update gives "Invalid column name 'job_id'."
>
> 
> Select * from jobs where job_id = 1
> 
>
> 
> Update jobs set status = 0 where job_id = 1
> 
>
>
> Scott
>
> -Original Message-
> From: Ben Nadel [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, July 25, 2006 3:02 PM
> To: CF-Talk
> Subject: RE: Odd Query issue
>
> Can you actually post the CFQuery codes so we can see if maybe there is
> something you are not seeing.
>
> ..
> Ben Nadel
> www.bennadel.com
>
> -Original Message-
> From: Scott Mulholland [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, July 25, 2006 1:23 PM
> To: CF-Talk
> Subject: Odd Query issue
>
> I have a query today that stopped working with an invalid column
> error.  The
> database did not change.  Select statements work on it but not updates.
>
>
>
> Select * from jobs where job_id = 1 - works fine
>
>
>
> Update jobs set status = 0 where job_id = 1 - results in Invalid column
> name
> 'job_id'.
>
>
>
> Any ideas what would cause this to just start happening?
>
>
>
> Thanks,
> Scott
>
>
>
>
>
>
>
>
>
>
>
> 

~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four 
times a year.
http://www.fusionauthority.com/quarterly

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:247783
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4


RE: Odd Query issue

2006-07-26 Thread Ben Nadel
What happens when you try to do a delete statement that you know will fail
(so you don't delete data):

DELETE FROM 
jobs
WHERE
job_id != job_id


This should never be true, but I am curious as to if it throws the same
error. 


...
Ben Nadel 
www.bennadel.com

-Original Message-
From: Scott Mulholland [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, July 26, 2006 1:16 PM
To: CF-Talk
Subject: RE: Odd Query issue

I tried with a qryparam yesterday, no change.  Selecting job_id from jobs
works as well.

If I run select * from information_schema.columns where table_name = 'jobs'
the column comes back as well.

This is one of those weird "it worked fine for yrs up until yesterday and
nothing in the code or table changed" things.  I'm thinking something is
corrupt in the database.

-Original Message-
From: Robertson-Ravo, Neil (RX)
[mailto:[EMAIL PROTECTED]
Sent: Wednesday, July 26, 2006 12:32 PM
To: CF-Talk
Subject: Re: Odd Query issue

What happens if you put it into a cfqueryparam?

What happens if you select job_id from jobs?







"This e-mail is from Reed Exhibitions (Oriel House, 26 The Quadrant,
Richmond, Surrey, TW9 1DL, United Kingdom), a division of Reed Business,
Registered in England, Number 678540.  It contains information which is
confidential and may also be privileged.  It is for the exclusive use of the
intended recipient(s).  If you are not the intended recipient(s) please note
that any form of distribution, copying or use of this communication or the
information in it is strictly prohibited and may be unlawful.  If you have
received this communication in error please return it to the sender or call
our switchboard on +44 (0) 20 89107910.  The opinions expressed within this
communication are not necessarily those expressed by Reed Exhibitions." 
Visit our website at http://www.reedexpo.com

-Original Message-
From: Scott Mulholland <[EMAIL PROTECTED]>
To: CF-Talk 
Sent: Wed Jul 26 17:42:27 2006
Subject: RE: Odd Query issue

I just wrote these two tests and ran them, same result...the select works,
the update gives "Invalid column name 'job_id'."

 Select * from
jobs where job_id = 1 

 Update jobs set
status = 0 where job_id = 1 


Scott

-Original Message-
From: Ben Nadel [mailto:[EMAIL PROTECTED]
Sent: Tuesday, July 25, 2006 3:02 PM
To: CF-Talk
Subject: RE: Odd Query issue

Can you actually post the CFQuery codes so we can see if maybe there is
something you are not seeing.

...
Ben Nadel
www.bennadel.com

-Original Message-
From: Scott Mulholland [mailto:[EMAIL PROTECTED]
Sent: Tuesday, July 25, 2006 1:23 PM
To: CF-Talk
Subject: Odd Query issue

I have a query today that stopped working with an invalid column error.  The
database did not change.  Select statements work on it but not updates.

 

Select * from jobs where job_id = 1 - works fine

 

Update jobs set status = 0 where job_id = 1 - results in Invalid column name
'job_id'.

 

Any ideas what would cause this to just start happening?

 

Thanks,
Scott













~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four 
times a year.
http://www.fusionauthority.com/quarterly

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:247779
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4


RE: Odd Query issue

2006-07-26 Thread Scott Mulholland
I tried with a qryparam yesterday, no change.  Selecting job_id from jobs
works as well.

If I run select * from information_schema.columns where table_name = 'jobs'
the column comes back as well.

This is one of those weird "it worked fine for yrs up until yesterday and
nothing in the code or table changed" things.  I'm thinking something is
corrupt in the database.

-Original Message-
From: Robertson-Ravo, Neil (RX)
[mailto:[EMAIL PROTECTED] 
Sent: Wednesday, July 26, 2006 12:32 PM
To: CF-Talk
Subject: Re: Odd Query issue

What happens if you put it into a cfqueryparam?

What happens if you select job_id from jobs?







"This e-mail is from Reed Exhibitions (Oriel House, 26 The Quadrant,
Richmond, Surrey, TW9 1DL, United Kingdom), a division of Reed Business,
Registered in England, Number 678540.  It contains information which is
confidential and may also be privileged.  It is for the exclusive use of the
intended recipient(s).  If you are not the intended recipient(s) please note
that any form of distribution, copying or use of this communication or the
information in it is strictly prohibited and may be unlawful.  If you have
received this communication in error please return it to the sender or call
our switchboard on +44 (0) 20 89107910.  The opinions expressed within this
communication are not necessarily those expressed by Reed Exhibitions." 
Visit our website at http://www.reedexpo.com

-Original Message-
From: Scott Mulholland <[EMAIL PROTECTED]>
To: CF-Talk 
Sent: Wed Jul 26 17:42:27 2006
Subject: RE: Odd Query issue

I just wrote these two tests and ran them, same result...the select works,
the update gives "Invalid column name 'job_id'."


Select * from jobs where job_id = 1



Update jobs set status = 0 where job_id = 1



Scott

-Original Message-
From: Ben Nadel [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, July 25, 2006 3:02 PM
To: CF-Talk
Subject: RE: Odd Query issue

Can you actually post the CFQuery codes so we can see if maybe there is
something you are not seeing.

..
Ben Nadel 
www.bennadel.com

-Original Message-
From: Scott Mulholland [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, July 25, 2006 1:23 PM
To: CF-Talk
Subject: Odd Query issue

I have a query today that stopped working with an invalid column error.  The
database did not change.  Select statements work on it but not updates.

 

Select * from jobs where job_id = 1 - works fine

 

Update jobs set status = 0 where job_id = 1 - results in Invalid column name
'job_id'.

 

Any ideas what would cause this to just start happening?

 

Thanks,
Scott











~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four 
times a year.
http://www.fusionauthority.com/quarterly

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:247776
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4


Re: Odd Query issue

2006-07-26 Thread Kris Jones
Have you tried surrounding the "job_id" with brackets such that:

Update jobs set status=0 where [job_id]=1

Perhaps, and I'm only stabbing in the dark here, job_id becomes a
reserved word when submitting certain types of statements to your DBMS
(where they might become jobs)?

Try it and see.

Cheers,
Kris

> I just wrote these two tests and ran them, same result...the select works,
> the update gives "Invalid column name 'job_id'."
>
> 
> Select * from jobs where job_id = 1
> 
>
> 
> Update jobs set status = 0 where job_id = 1
> 

~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four 
times a year.
http://www.fusionauthority.com/quarterly

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:247771
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4


Re: Odd Query issue

2006-07-26 Thread Robertson-Ravo, Neil (RX)
What happens if you put it into a cfqueryparam?

What happens if you select job_id from jobs?







"This e-mail is from Reed Exhibitions (Oriel House, 26 The Quadrant,
Richmond, Surrey, TW9 1DL, United Kingdom), a division of Reed Business,
Registered in England, Number 678540.  It contains information which is
confidential and may also be privileged.  It is for the exclusive use of the
intended recipient(s).  If you are not the intended recipient(s) please note
that any form of distribution, copying or use of this communication or the
information in it is strictly prohibited and may be unlawful.  If you have
received this communication in error please return it to the sender or call
our switchboard on +44 (0) 20 89107910.  The opinions expressed within this
communication are not necessarily those expressed by Reed Exhibitions." 
Visit our website at http://www.reedexpo.com

-Original Message-
From: Scott Mulholland <[EMAIL PROTECTED]>
To: CF-Talk 
Sent: Wed Jul 26 17:42:27 2006
Subject: RE: Odd Query issue

I just wrote these two tests and ran them, same result...the select works,
the update gives "Invalid column name 'job_id'."


Select * from jobs where job_id = 1



Update jobs set status = 0 where job_id = 1



Scott

-Original Message-
From: Ben Nadel [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, July 25, 2006 3:02 PM
To: CF-Talk
Subject: RE: Odd Query issue

Can you actually post the CFQuery codes so we can see if maybe there is
something you are not seeing.

.
Ben Nadel 
www.bennadel.com

-Original Message-
From: Scott Mulholland [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, July 25, 2006 1:23 PM
To: CF-Talk
Subject: Odd Query issue

I have a query today that stopped working with an invalid column error.  The
database did not change.  Select statements work on it but not updates.

 

Select * from jobs where job_id = 1 - works fine

 

Update jobs set status = 0 where job_id = 1 - results in Invalid column name
'job_id'.

 

Any ideas what would cause this to just start happening?

 

Thanks,
Scott









~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four 
times a year.
http://www.fusionauthority.com/quarterly

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:247770
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4


RE: Odd Query issue

2006-07-26 Thread Scott Mulholland
I just wrote these two tests and ran them, same result...the select works,
the update gives "Invalid column name 'job_id'."


Select * from jobs where job_id = 1



Update jobs set status = 0 where job_id = 1



Scott

-Original Message-
From: Ben Nadel [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, July 25, 2006 3:02 PM
To: CF-Talk
Subject: RE: Odd Query issue

Can you actually post the CFQuery codes so we can see if maybe there is
something you are not seeing.


Ben Nadel 
www.bennadel.com

-Original Message-
From: Scott Mulholland [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, July 25, 2006 1:23 PM
To: CF-Talk
Subject: Odd Query issue

I have a query today that stopped working with an invalid column error.  The
database did not change.  Select statements work on it but not updates.

 

Select * from jobs where job_id = 1 - works fine

 

Update jobs set status = 0 where job_id = 1 - results in Invalid column name
'job_id'.

 

Any ideas what would cause this to just start happening?

 

Thanks,
Scott







~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four 
times a year.
http://www.fusionauthority.com/quarterly

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:247768
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4


RE: Query issue between MX 6 and MX 7

2006-07-25 Thread Trevor Orr
Yup, that is what I mean, the columns getting an empty string.

 

-Original Message-
From: Barney Boisvert [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, July 25, 2006 10:35 AM
To: CF-Talk
Subject: Re: Query issue between MX 6 and MX 7

I assume what the "radically different result" you describe is that on
CF 7, column C gets reverted to the empty string (from '3') when the
second query is injected into column BB?  Or is there something else?

cheers,
barneyb

On 7/25/06, Trevor Orr <[EMAIL PROTECTED]> wrote:
> I ran across an issue with queries in MX 6 and MX7, try this code in 
> MX
> 6 and MX 7 and you get radically different results.  Anyone know of a 
> reason for this or is it a bug.  I sent this to Adobe but got no 
> response so maybe someone here can shed some light.
>
>
> Here is the code:
>
> 
> 
>  
>  
>  
>  
> 
> 
>
> 
> 
>  
>  
>  
>  
> 
>
> 
>
>
>
>
>
> Trevor Orr

--
Barney Boisvert
[EMAIL PROTECTED]
360.319.6145
http://www.barneyb.com/

Got Gmail? I have 100 invites.



~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four 
times a year.
http://www.fusionauthority.com/quarterly

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:247695
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4


RE: Odd Query issue

2006-07-25 Thread Ben Nadel
Can you actually post the CFQuery codes so we can see if maybe there is
something you are not seeing.

...
Ben Nadel 
www.bennadel.com

-Original Message-
From: Scott Mulholland [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, July 25, 2006 1:23 PM
To: CF-Talk
Subject: Odd Query issue

I have a query today that stopped working with an invalid column error.  The
database did not change.  Select statements work on it but not updates.

 

Select * from jobs where job_id = 1 - works fine

 

Update jobs set status = 0 where job_id = 1 - results in Invalid column name
'job_id'.

 

Any ideas what would cause this to just start happening?

 

Thanks,
Scott





~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four 
times a year.
http://www.fusionauthority.com/quarterly

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:247660
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4


RE: Odd Query issue

2006-07-25 Thread Scott Mulholland
Yeah, I checked that as well.  Update is still allowed.  I also queried the
system tables by column name and the table does come back for column =
'job_id'.  Very strange.

-Original Message-
From: Joseph Lamoree [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, July 25, 2006 2:01 PM
To: CF-Talk
Subject: Re: Odd Query issue

Maybe someone changed your datasource configuration.

--
Joseph Lamoree

On 25 Jul 2006, at 10:22, Scott Mulholland wrote:

> I have a query today that stopped working with an invalid column  
> error.  The
> database did not change.  Select statements work on it but not  
> updates.




~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four 
times a year.
http://www.fusionauthority.com/quarterly

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:247641
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4


Re: Odd Query issue

2006-07-25 Thread Joseph Lamoree
Maybe someone changed your datasource configuration.

--
Joseph Lamoree

On 25 Jul 2006, at 10:22, Scott Mulholland wrote:

> I have a query today that stopped working with an invalid column  
> error.  The
> database did not change.  Select statements work on it but not  
> updates.


~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four 
times a year.
http://www.fusionauthority.com/quarterly

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:247639
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4


Re: Query issue between MX 6 and MX 7

2006-07-25 Thread Barney Boisvert
I assume what the "radically different result" you describe is that on
CF 7, column C gets reverted to the empty string (from '3') when the
second query is injected into column BB?  Or is there something else?

cheers,
barneyb

On 7/25/06, Trevor Orr <[EMAIL PROTECTED]> wrote:
> I ran across an issue with queries in MX 6 and MX7, try this code in MX
> 6 and MX 7 and you get radically different results.  Anyone know of a
> reason for this or is it a bug.  I sent this to Adobe but got no
> response so maybe someone here can shed some light.
>
>
> Here is the code:
>
> 
> 
>  
>  
>  
>  
> 
> 
>
> 
> 
>  
>  
>  
>  
> 
>
> 
>  
> 
> 
>
>
>
>
> Trevor Orr

-- 
Barney Boisvert
[EMAIL PROTECTED]
360.319.6145
http://www.barneyb.com/

Got Gmail? I have 100 invites.

~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four 
times a year.
http://www.fusionauthority.com/quarterly

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:247636
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4


Query issue between MX 6 and MX 7

2006-07-25 Thread Trevor Orr
I ran across an issue with queries in MX 6 and MX7, try this code in MX
6 and MX 7 and you get radically different results.  Anyone know of a
reason for this or is it a bug.  I sent this to Adobe but got no
response so maybe someone here can shed some light.
 
 
Here is the code:
 


 
 
 
 


 


 
 
 
 

 

 


 
 
 
 
Trevor Orr
Summit Projects
[EMAIL PROTECTED]
541.387.8883 x213
 
 


~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four 
times a year.
http://www.fusionauthority.com/quarterly

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:247634
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4


Odd Query issue

2006-07-25 Thread Scott Mulholland
I have a query today that stopped working with an invalid column error.  The
database did not change.  Select statements work on it but not updates.

 

Select * from jobs where job_id = 1 - works fine

 

Update jobs set status = 0 where job_id = 1 - results in Invalid column name
'job_id'.

 

Any ideas what would cause this to just start happening?

 

Thanks,
Scott



~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four 
times a year.
http://www.fusionauthority.com/quarterly

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:247633
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4


RE: Wierd query of query issue

2004-03-11 Thread Paul Vernon
I've just got my hands on a new W2k3 server and installed the MX6.1 trial.
The problem as described in the original message no longer exists Must
be a CF5 issue only... ho hum.

 
Paul

  _  

From: Paul Vernon 
Sent: Wednesday, 10 March 2004 11:50
To: CF-Talk
Subject: Wierd query of query issue

I've just discovered that one of my query of query functions is truncating a
particular field in a query and I was wondering if anyone had come across
this behaviour before

I'm running CF5 on W2k SP4.

    
SELECT *
FROM IMAPServer
    

    
    #notaffectedcolumn#
    #MyColumn#    
    

    
SELECT *
FROM IMAPServerQuery
ORDER BY internaldate DESC
    

    
    #notaffectedcolumn#
    #MyColumn# 
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]




Wierd query of query issue

2004-03-10 Thread Paul Vernon
I've just discovered that one of my query of query functions is truncating a
particular field in a query and I was wondering if anyone had come across
this behaviour before

 
I'm running CF5 on W2k SP4.

 
    
SELECT *
FROM IMAPServer
    

 
    
    #notaffectedcolumn#
    #MyColumn#    
    

 
    
SELECT *
FROM IMAPServerQuery
ORDER BY internaldate DESC
    

 
    
    #notaffectedcolumn#
    #MyColumn# 
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]




Re: Query Issue

2002-05-20 Thread Nick McClure

SELECT *
FROM Table
WHERE #form.ID# IN (AccessList)

At 03:42 PM 5/20/2002 -0700, you wrote:
>Howdy List!
>
>Can someone please 'gimmie a thread' on this? (ie: Brain thread, as in cpu
>thread)
>
>I have an existing database with a field named 'AccessList'. This field is a
>comma delimited list of IDs (numeric only). These IDs are for users who are
>to be granted access to a function. For example one entry would be
>4,15,26,44,101,9.
>
>I am trying to devise a query that will check the field for a single userID.
>Say 4 for example... note that 44 also exists in the list.
>
>My fumbling attempts to devise a way using LIKE and IN are not working.
>
>
>
>
>
>
__
Get the mailserver that powers this list at http://www.coolfusion.com
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: Query Issue

2002-05-20 Thread Ewok

ahhh one more for the road...






  select list
  from table





select ANYTHING TO GET A RECORDCOUNT
from table
where userID = #ID# AND
  userid IN (#list#)





SUCCESS!



 DENIED!




dead thread...
- Original Message -
From: "Ewok" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Monday, May 20, 2002 7:06 PM
Subject: Re: Query Issue


> Im probably way off what you mean
>
> select list
> from table
>
> 
>
> 
> SUCCESS
> 
> DENIED
> 
>
>
>
>
> - Original Message -
> From: "Tony Gruen" <[EMAIL PROTECTED]>
> To: "CF-Talk" <[EMAIL PROTECTED]>
> Sent: Monday, May 20, 2002 6:42 PM
> Subject: Query Issue
>
>
> > Howdy List!
> >
> > Can someone please 'gimmie a thread' on this? (ie: Brain thread, as in
cpu
> > thread)
> >
> > I have an existing database with a field named 'AccessList'. This field
is
> a
> > comma delimited list of IDs (numeric only). These IDs are for users who
> are
> > to be granted access to a function. For example one entry would be
> > 4,15,26,44,101,9.
> >
> > I am trying to devise a query that will check the field for a single
> userID.
> > Say 4 for example... note that 44 also exists in the list.
> >
> > My fumbling attempts to devise a way using LIKE and IN are not working.
> >
> >
> >
> >
> >
> >
> 
__
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
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: Query Issue

2002-05-20 Thread Tony Gruen

Thanks everyone. This helps. We have inherited an access DB that was
originally in FileMaker Pro3 and migrated into access 97 by some goombaa. We
proposed rebuilding the structure but no sale. It is on an intranet and does
not carry any budget dollars for the client.

Tony

-Original Message-
From: Ewok [mailto:[EMAIL PROTECTED]]
Sent: Monday, May 20, 2002 4:07 PM
To: CF-Talk
Subject: Re: Query Issue


Im probably way off what you mean

select list
from table




SUCCESS

DENIED





- Original Message -
From: "Tony Gruen" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Monday, May 20, 2002 6:42 PM
Subject: Query Issue


> Howdy List!
>
> Can someone please 'gimmie a thread' on this? (ie: Brain thread, as in cpu
> thread)
>
> I have an existing database with a field named 'AccessList'. This field is
a
> comma delimited list of IDs (numeric only). These IDs are for users who
are
> to be granted access to a function. For example one entry would be
> 4,15,26,44,101,9.
>
> I am trying to devise a query that will check the field for a single
userID.
> Say 4 for example... note that 44 also exists in the list.
>
> My fumbling attempts to devise a way using LIKE and IN are not working.
>
>
>
>
>
>

__
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: Query Issue

2002-05-20 Thread Ewok

Im probably way off what you mean

select list
from table




SUCCESS

DENIED





- Original Message -
From: "Tony Gruen" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Monday, May 20, 2002 6:42 PM
Subject: Query Issue


> Howdy List!
>
> Can someone please 'gimmie a thread' on this? (ie: Brain thread, as in cpu
> thread)
>
> I have an existing database with a field named 'AccessList'. This field is
a
> comma delimited list of IDs (numeric only). These IDs are for users who
are
> to be granted access to a function. For example one entry would be
> 4,15,26,44,101,9.
>
> I am trying to devise a query that will check the field for a single
userID.
> Say 4 for example... note that 44 also exists in the list.
>
> My fumbling attempts to devise a way using LIKE and IN are not working.
>
>
>
>
>
> 
__
Get the mailserver that powers this list at http://www.coolfusion.com
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: Query Issue

2002-05-20 Thread Jim Curran

select user_ID from usertbl where user_ID in (select accesslist from area
where )

- j

-Original Message-
From: Tony Gruen [mailto:[EMAIL PROTECTED]]
Sent: Monday, May 20, 2002 6:43 PM
To: CF-Talk
Subject: Query Issue


Howdy List!

Can someone please 'gimmie a thread' on this? (ie: Brain thread, as in cpu
thread)

I have an existing database with a field named 'AccessList'. This field is a
comma delimited list of IDs (numeric only). These IDs are for users who are
to be granted access to a function. For example one entry would be
4,15,26,44,101,9.

I am trying to devise a query that will check the field for a single userID.
Say 4 for example... note that 44 also exists in the list.

My fumbling attempts to devise a way using LIKE and IN are not working.






__
Get the mailserver that powers this list at http://www.coolfusion.com
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: Query Issue

2002-05-20 Thread Rob Baxter

Any chance you can change that db schema, or is it too late? I would think
it might be a big headaches to maintain such a scheme. It's also likely to
be much slower than some other approaches.

If you're stuck with the current setup, just try something like this:

select blah
from Functions
where 4 IN (select UserIDList from Functions where FunctionId = 1)



-Original Message-
From: Tony Gruen [mailto:[EMAIL PROTECTED]]
Sent: Monday, May 20, 2002 6:43 PM
To: CF-Talk
Subject: Query Issue


Howdy List!

Can someone please 'gimmie a thread' on this? (ie: Brain thread, as in cpu
thread)

I have an existing database with a field named 'AccessList'. This field is a
comma delimited list of IDs (numeric only). These IDs are for users who are
to be granted access to a function. For example one entry would be
4,15,26,44,101,9.

I am trying to devise a query that will check the field for a single userID.
Say 4 for example... note that 44 also exists in the list.

My fumbling attempts to devise a way using LIKE and IN are not working.






__
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
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: Query Issue

2002-05-20 Thread Bryan Love

If you must do it this way then ensure that you always put a comma at the
beginning and at the end of the list of numbers before inserting into the
DB.  That you end up with this: ,4,15,26,44,101,9, . Then use LIKE %,4,% to
get your results.

I'd like to say that there are better ways of doing this...

[table] users (FK)> [table] users_roles <(FK) [table] roles

+---+
Bryan Love
  Macromedia Certified Professional
  Internet Application Developer
  Database Analyst
Telecommunication Systems
[EMAIL PROTECTED]
+---+

"...'If there must be trouble, let it be in my day, that my child may have
peace'..."
- Thomas Paine, The American Crisis



-Original Message-
From: chad [mailto:[EMAIL PROTECTED]]
Sent: Monday, May 20, 2002 3:52 PM
To: CF-Talk
Subject: Re: Query Issue


Why do it in the query?  Grab your list and loop over it until you find a
true.

You should probably make another table and related that field out instead 
of making a comma delimited list in the db... but maybe you don't have 
control over the db.


At 03:42 PM 5/20/2002 -0700, you wrote:
>Howdy List!
>
>Can someone please 'gimmie a thread' on this? (ie: Brain thread, as in cpu
>thread)
>
>I have an existing database with a field named 'AccessList'. This field is
a
>comma delimited list of IDs (numeric only). These IDs are for users who are
>to be granted access to a function. For example one entry would be
>4,15,26,44,101,9.
>
>I am trying to devise a query that will check the field for a single
userID.
>Say 4 for example... note that 44 also exists in the list.
>
>My fumbling attempts to devise a way using LIKE and IN are not working.
>
>
>
>
>
>

__
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
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: Query Issue

2002-05-20 Thread chad

Why do it in the query?  Grab your list and loop over it until you find a true.

You should probably make another table and related that field out instead 
of making a comma delimited list in the db... but maybe you don't have 
control over the db.


At 03:42 PM 5/20/2002 -0700, you wrote:
>Howdy List!
>
>Can someone please 'gimmie a thread' on this? (ie: Brain thread, as in cpu
>thread)
>
>I have an existing database with a field named 'AccessList'. This field is a
>comma delimited list of IDs (numeric only). These IDs are for users who are
>to be granted access to a function. For example one entry would be
>4,15,26,44,101,9.
>
>I am trying to devise a query that will check the field for a single userID.
>Say 4 for example... note that 44 also exists in the list.
>
>My fumbling attempts to devise a way using LIKE and IN are not working.
>
>
>
>
>
>
__
This list and all House of Fusion resources hosted by CFHosting.com. The place for 
dependable ColdFusion Hosting.
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



Query Issue

2002-05-20 Thread Tony Gruen

Howdy List!

Can someone please 'gimmie a thread' on this? (ie: Brain thread, as in cpu
thread)

I have an existing database with a field named 'AccessList'. This field is a
comma delimited list of IDs (numeric only). These IDs are for users who are
to be granted access to a function. For example one entry would be
4,15,26,44,101,9.

I am trying to devise a query that will check the field for a single userID.
Say 4 for example... note that 44 also exists in the list.

My fumbling attempts to devise a way using LIKE and IN are not working.





__
Get the mailserver that powers this list at http://www.coolfusion.com
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: Query Issue.

2002-05-06 Thread Kreig Zimmerman

Well obviously you wouldn't be using a column that you were trying to update.
  - Original Message - 
  From: Jared Stark 
  To: CF-Talk 
  Sent: Friday, May 03, 2002 6:25 PM
  Subject: RE: Query Issue.


  Except then if you try to update that same column later in the same
  query SQL Server will get upset with you.

  -Original Message-
  From: Kreig Zimmerman [mailto:[EMAIL PROTECTED]] 
  Sent: Friday, May 03, 2002 4:23 PM
  To: CF-Talk
  Subject: Re: Query Issue.

  what i usually do is "columnname = columnname" which updates the content
  of the column, with the contents of the column--no problems there, eh?
- Original Message - 
From: Jared Stark 
To: CF-Talk 
Sent: Friday, May 03, 2002 5:52 PM
    Subject: RE: Query Issue.


I tried the 0 = 0 trick and SQL Server gives me an error.  Does that
work for you?




  
__
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
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: Query Issue.

2002-05-03 Thread Jared Stark

Except then if you try to update that same column later in the same
query SQL Server will get upset with you.

-Original Message-
From: Kreig Zimmerman [mailto:[EMAIL PROTECTED]] 
Sent: Friday, May 03, 2002 4:23 PM
To: CF-Talk
Subject: Re: Query Issue.

what i usually do is "columnname = columnname" which updates the content
of the column, with the contents of the column--no problems there, eh?
  - Original Message - 
  From: Jared Stark 
  To: CF-Talk 
  Sent: Friday, May 03, 2002 5:52 PM
  Subject: RE: Query Issue.


  I tried the 0 = 0 trick and SQL Server gives me an error.  Does that
  work for you?




__
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
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: Query Issue.

2002-05-03 Thread Kreig Zimmerman

what i usually do is "columnname = columnname" which updates the content of the 
column, with the contents of the column--no problems there, eh?
  - Original Message - 
  From: Jared Stark 
  To: CF-Talk 
  Sent: Friday, May 03, 2002 5:52 PM
  Subject: RE: Query Issue.


  I tried the 0 = 0 trick and SQL Server gives me an error.  Does that
  work for you?



__
This list and all House of Fusion resources hosted by CFHosting.com. The place for 
dependable ColdFusion Hosting.
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: Query Issue.

2002-05-03 Thread Jared Stark

That's the trick.  I should have seen that!!  Thanks

Jared

-Original Message-
From: Bryan Love [mailto:[EMAIL PROTECTED]] 
Sent: Friday, May 03, 2002 4:16 PM
To: CF-Talk
Subject: RE: Query Issue.

It's been a while since I've tried it... I know it works with WHERE
clauses... to be honest I don't remember if it worked for me or not with
updates... 

I see your problem...

do this instead:

UPDATE table
SET col1 = '#col1#'col1,
  col2 = '#col2#'col2,
  col3 = '#col3#'col3,
  col4 = '#col4#'col4
WHERE id = 12345


+---+
Bryan Love
  Macromedia Certified Professional
  Internet Application Developer
  Database Analyst
Telecommunication Systems
[EMAIL PROTECTED]
+---+

"...'If there must be trouble, let it be in my day, that my child may
have
peace'..."
- Thomas Paine, The American Crisis



-Original Message-
From: Jared Stark [mailto:[EMAIL PROTECTED]]
Sent: Friday, May 03, 2002 2:52 PM
To: CF-Talk
Subject: RE: Query Issue.


I tried the 0 = 0 trick and SQL Server gives me an error.  Does that
work for you?

-Original Message-
From: Bryan Love [mailto:[EMAIL PROTECTED]] 
Sent: Friday, May 03, 2002 3:22 PM
To: CF-Talk
Subject: RE: Query Issue.

escaping the inner apostrphe should work, but since it isn't, why build
the
update in a string?

Just put the IF statements in the query itself.

UPDATE table
SET 0=0
  ,col1 = '#col1#'
  ,col2 = '#col2#'
  ,col3 = '#col3#'
  ,col4 = '#col4#'
WHERE id = 12345

+---+
Bryan Love
  Macromedia Certified Professional
  Internet Application Developer
  Database Analyst
Telecommunication Systems
[EMAIL PROTECTED]
+---+

"...'If there must be trouble, let it be in my day, that my child may
have
peace'..."
- Thomas Paine, The American Crisis



-Original Message-
From: Jared Stark [mailto:[EMAIL PROTECTED]]
Sent: Friday, May 03, 2002 1:59 PM
To: CF-Talk
Subject: Query Issue.


I seem to be stuck between a rock and a hard spot here.  I am building
the 'guts' of an UPDATE query inside a variable, and then using the
variable inside a query like so:
 

 

UPDATE table
SET
#PreserveSingleQuotes(qryText)#
WHERE id = 12345

 
This works fine unless inside qryText I get something like:
 

 
Now it barfs on the ' because I am using PreserveSingleQuotes and that
inner ' doesn't get escaped, which I have to do otherwise it escapes the
quotes around the value.  So I tried escaping the apostrophe like:
 

 
but it still doesn't like that, and tells me there is an error at that
apostrophe.
 
Am I missing something here?  I do realize it is Friday and I should
probably be at the movies instead, but if someone could be so gracious
as to point out what I'm missing I'd be grateful.
 
~ Jared





__
Get the mailserver that powers this list at http://www.coolfusion.com
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: Query Issue.

2002-05-03 Thread Bryan Love

It's been a while since I've tried it... I know it works with WHERE
clauses... to be honest I don't remember if it worked for me or not with
updates... 

I see your problem...

do this instead:

UPDATE table
SET col1 = '#col1#'col1,
  col2 = '#col2#'col2,
  col3 = '#col3#'col3,
  col4 = '#col4#'col4
WHERE id = 12345


+---+
Bryan Love
  Macromedia Certified Professional
  Internet Application Developer
  Database Analyst
Telecommunication Systems
[EMAIL PROTECTED]
+---+

"...'If there must be trouble, let it be in my day, that my child may have
peace'..."
- Thomas Paine, The American Crisis



-Original Message-
From: Jared Stark [mailto:[EMAIL PROTECTED]]
Sent: Friday, May 03, 2002 2:52 PM
To: CF-Talk
Subject: RE: Query Issue.


I tried the 0 = 0 trick and SQL Server gives me an error.  Does that
work for you?

-Original Message-
From: Bryan Love [mailto:[EMAIL PROTECTED]] 
Sent: Friday, May 03, 2002 3:22 PM
To: CF-Talk
Subject: RE: Query Issue.

escaping the inner apostrphe should work, but since it isn't, why build
the
update in a string?

Just put the IF statements in the query itself.

UPDATE table
SET 0=0
  ,col1 = '#col1#'
  ,col2 = '#col2#'
  ,col3 = '#col3#'
  ,col4 = '#col4#'
WHERE id = 12345

+---+
Bryan Love
  Macromedia Certified Professional
  Internet Application Developer
  Database Analyst
Telecommunication Systems
[EMAIL PROTECTED]
+---+

"...'If there must be trouble, let it be in my day, that my child may
have
peace'..."
- Thomas Paine, The American Crisis



-Original Message-
From: Jared Stark [mailto:[EMAIL PROTECTED]]
Sent: Friday, May 03, 2002 1:59 PM
To: CF-Talk
Subject: Query Issue.


I seem to be stuck between a rock and a hard spot here.  I am building
the 'guts' of an UPDATE query inside a variable, and then using the
variable inside a query like so:
 

 

UPDATE table
SET
#PreserveSingleQuotes(qryText)#
WHERE id = 12345

 
This works fine unless inside qryText I get something like:
 

 
Now it barfs on the ' because I am using PreserveSingleQuotes and that
inner ' doesn't get escaped, which I have to do otherwise it escapes the
quotes around the value.  So I tried escaping the apostrophe like:
 

 
but it still doesn't like that, and tells me there is an error at that
apostrophe.
 
Am I missing something here?  I do realize it is Friday and I should
probably be at the movies instead, but if someone could be so gracious
as to point out what I'm missing I'd be grateful.
 
~ Jared




__
Get the mailserver that powers this list at http://www.coolfusion.com
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: Query Issue.

2002-05-03 Thread Jared Stark

I tried the 0 = 0 trick and SQL Server gives me an error.  Does that
work for you?

-Original Message-
From: Bryan Love [mailto:[EMAIL PROTECTED]] 
Sent: Friday, May 03, 2002 3:22 PM
To: CF-Talk
Subject: RE: Query Issue.

escaping the inner apostrphe should work, but since it isn't, why build
the
update in a string?

Just put the IF statements in the query itself.

UPDATE table
SET 0=0
  ,col1 = '#col1#'
  ,col2 = '#col2#'
  ,col3 = '#col3#'
  ,col4 = '#col4#'
WHERE id = 12345

+---+
Bryan Love
  Macromedia Certified Professional
  Internet Application Developer
  Database Analyst
Telecommunication Systems
[EMAIL PROTECTED]
+---+

"...'If there must be trouble, let it be in my day, that my child may
have
peace'..."
- Thomas Paine, The American Crisis



-Original Message-
From: Jared Stark [mailto:[EMAIL PROTECTED]]
Sent: Friday, May 03, 2002 1:59 PM
To: CF-Talk
Subject: Query Issue.


I seem to be stuck between a rock and a hard spot here.  I am building
the 'guts' of an UPDATE query inside a variable, and then using the
variable inside a query like so:
 

 

UPDATE table
SET
#PreserveSingleQuotes(qryText)#
WHERE id = 12345

 
This works fine unless inside qryText I get something like:
 

 
Now it barfs on the ' because I am using PreserveSingleQuotes and that
inner ' doesn't get escaped, which I have to do otherwise it escapes the
quotes around the value.  So I tried escaping the apostrophe like:
 

 
but it still doesn't like that, and tells me there is an error at that
apostrophe.
 
Am I missing something here?  I do realize it is Friday and I should
probably be at the movies instead, but if someone could be so gracious
as to point out what I'm missing I'd be grateful.
 
~ Jared



__
Get the mailserver that powers this list at http://www.coolfusion.com
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: Query Issue.

2002-05-03 Thread Bryan Love

escaping the inner apostrphe should work, but since it isn't, why build the
update in a string?

Just put the IF statements in the query itself.

UPDATE table
SET 0=0
  ,col1 = '#col1#'
  ,col2 = '#col2#'
  ,col3 = '#col3#'
  ,col4 = '#col4#'
WHERE id = 12345

+---+
Bryan Love
  Macromedia Certified Professional
  Internet Application Developer
  Database Analyst
Telecommunication Systems
[EMAIL PROTECTED]
+---+

"...'If there must be trouble, let it be in my day, that my child may have
peace'..."
- Thomas Paine, The American Crisis



-Original Message-
From: Jared Stark [mailto:[EMAIL PROTECTED]]
Sent: Friday, May 03, 2002 1:59 PM
To: CF-Talk
Subject: Query Issue.


I seem to be stuck between a rock and a hard spot here.  I am building
the 'guts' of an UPDATE query inside a variable, and then using the
variable inside a query like so:
 

 

UPDATE table
SET
#PreserveSingleQuotes(qryText)#
WHERE id = 12345

 
This works fine unless inside qryText I get something like:
 

 
Now it barfs on the ' because I am using PreserveSingleQuotes and that
inner ' doesn't get escaped, which I have to do otherwise it escapes the
quotes around the value.  So I tried escaping the apostrophe like:
 

 
but it still doesn't like that, and tells me there is an error at that
apostrophe.
 
Am I missing something here?  I do realize it is Friday and I should
probably be at the movies instead, but if someone could be so gracious
as to point out what I'm missing I'd be grateful.
 
~ Jared


__
This list and all House of Fusion resources hosted by CFHosting.com. The place for 
dependable ColdFusion Hosting.
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



Query Issue.

2002-05-03 Thread Jared Stark

I seem to be stuck between a rock and a hard spot here.  I am building
the 'guts' of an UPDATE query inside a variable, and then using the
variable inside a query like so:
 

 

UPDATE table
SET
#PreserveSingleQuotes(qryText)#
WHERE id = 12345

 
This works fine unless inside qryText I get something like:
 

 
Now it barfs on the ' because I am using PreserveSingleQuotes and that
inner ' doesn't get escaped, which I have to do otherwise it escapes the
quotes around the value.  So I tried escaping the apostrophe like:
 

 
but it still doesn't like that, and tells me there is an error at that
apostrophe.
 
Am I missing something here?  I do realize it is Friday and I should
probably be at the movies instead, but if someone could be so gracious
as to point out what I'm missing I'd be grateful.
 
~ Jared

__
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
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: Dynamic Insert Query Issue

2002-04-02 Thread Frank Mamone

How about if you make the unused fields on your form invisible and give them
a value which you can detect on your processing page and set them to null. A
little more work on the form generation side.

Another way to do it is to use a set if Isdefined("Form.Adresses_X")  on the
processing page and set the ones that are not defined to "Null".

- Original Message -
From: "Brunt, Michael" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Tuesday, April 02, 2002 6:13 PM
Subject: Dynamic Insert Query Issue


> This is difficult to explain but I will try my best.  I have an Insert
Query
> which will be passed a list of values from a form.  This form is
dynamically
> generated from a previous query result set so by the time the form is
> generated we know the number of records returned used for building the
form.
> Once the user has entered information in the form and submitted it  the
> insert query has to behave like this: -
>
> 
> INSERT INTO #request.TBCR#TN0987
> ADDR_ID_001,
>ADDR_ID_002,
>ADDR_ID_003,
>ADDR_ID_004,
>ADDR_ID_005,
>ADDR_ID_006,
>ADDR_ID_007,
>ADDR_ID_008 (this carries on up to 100)
> VALUES
>#form.addressID# (once again we have 100 fields in the db and for
> example in the case where there are 5 values passed to this query from the
> form we have make the remaining 95 values be null and this will vary
> dependant on the number of form values past.  So again if there are 20
> form.addressID's passed we would have to dynamically set the remaining 80
> values in the Insert to null.
>
> Hope I explained this well, can anyone help?
>
> Mike Brunt
> Sempra Energy
> 213.244.5226
>
> "A logician trying to explain logic to a programmer is like a cat trying
to
> explain to a fish what it's like to get wet."
>
>
> 
__
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



Dynamic Insert Query Issue

2002-04-02 Thread Brunt, Michael

This is difficult to explain but I will try my best.  I have an Insert Query
which will be passed a list of values from a form.  This form is dynamically
generated from a previous query result set so by the time the form is
generated we know the number of records returned used for building the form.
Once the user has entered information in the form and submitted it  the
insert query has to behave like this: -


INSERT INTO #request.TBCR#TN0987
ADDR_ID_001,
   ADDR_ID_002,
   ADDR_ID_003,
   ADDR_ID_004,
   ADDR_ID_005,
   ADDR_ID_006,
   ADDR_ID_007,
   ADDR_ID_008 (this carries on up to 100)
VALUES
   #form.addressID# (once again we have 100 fields in the db and for
example in the case where there are 5 values passed to this query from the
form we have make the remaining 95 values be null and this will vary
dependant on the number of form values past.  So again if there are 20
form.addressID's passed we would have to dynamically set the remaining 80
values in the Insert to null.

Hope I explained this well, can anyone help?

Mike Brunt
Sempra Energy
213.244.5226

"A logician trying to explain logic to a programmer is like a cat trying to
explain to a fish what it's like to get wet." 


__
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
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