Re: A little JS help!!

[Dina Hess]

Doug,

I see that Jon has already offered a solution, but here's another for you to
consider...JavaScript can be a lot of fun. :)

cfset qty = 1
cfset itemPrice = 2

!doctype html public -//w3c//dtd html 4.01 transitional//en
http://www.w3.org/tr/html4/loose.dtd;
html

head
 titleLine Item Calculation/title
 script type=text/javascript
 !--
  function calculateTotal() {
   var num = document.getElementById(qty).value * document.getElementById
(price).value;
   document.getElementById(total).value = num.toFixed(2);
  }
 //--
 /script
/head

body onload=calculateTotal()
cfoutput
table
tr
   td colspan=4 style=font-weight:bold;Your Offer/TD/tr
tr
   tdQuantity: input type=text name=qty id=qty value=#qty#
style=width:40px; onkeyup=calculateTotal()/td
   tdUnit Price: input type=text name=price id=price
value=#decimalFormat(itemPrice)# style=width:50px;
onkeyup=calculateTotal()/td
   tdTotal Price: input type=text name=total id=total value=
style=width:60px;/td/tr
/table
/cfoutput
/body

/html
Enjoy...
Dina


~|
Create robust enterprise, web RIAs.
Upgrade  integrate Adobe Coldfusion MX7 with Flex 2
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:266509
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: Sql aggregate woes

[Dina Hess]

select distinct
i.itemnumber
sum(i.qty) as invqty
from [your inventory table name here] i
group by i.itemnumber
having sum(i.qty)  0


On 12/3/06, Terry Troxel [EMAIL PROTECTED] wrote:

 I am having a senior moment and could use some help with a
 sql select query I am attempting to use to populate a
 listbox with only those items that have inventory.

 SELECT DISTINCT inventory.ItemNumber, SUM(inventory.qty) as
 invqty where invqty  0

 And I get the error I am not using 'ItenNumber' as part of
 an aggregate function.

 I have fixed this in the past, but am drawing a blank.

 Terry


 

~|
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:262657
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: Sql aggregate woes

[Dina Hess]

:) know the feeling, jim...i completely overlooked the distinct, left out a
comma between the fields, and left out the table name even though it was
right there...lol. all of this snow up here must be affecting my brain
cells...all 2 of them. oh, well, at least terry got some valuable feedback
on the use of group by and having.



On 12/3/06, Jim Wright [EMAIL PROTECTED] wrote:

 Jim Wright wrote:
  SELECT inventory.ItemNumber, SUM(inventory.qty) as
  invqty
  FROM inventory
  GROUP BY inventory.ItemNumber
  HAVING SUM(inventory.qty)  0
 

 oops...told you to take the DISTINCT out, and then left it in...see above.




~|
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:262662
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: database design revisted

[Dina Hess]

Probably not that simple...

Say User A requests the auction page 5 minutes before the auction expires.
He sits there looking at the page for 10 minutes before he decides what he
wants to do. By the time, he enters his bid, the auction has officially
closed. So when he submits his bid, he gets a gotcha message telling him
the auction has closed. Not what User A wants to hear!

So I think a JavaScript timer would be in order so users could see a
countdown to the auction close time. Having said that, I don't recall that
I've ever actually implemented that type of functionality on any of my web
applications...but you should be able to do a Google search on Javascript
timer and turn up something. You'll probably want to make use of MX 7's
ToScript() function to set a Javascript variable to the auction's expiration
timestamp.

Dina
(BTW, sorry for the delayed response)




On 10/8/06, Justin Holzer [EMAIL PROTECTED] wrote:

 Doug,

 There's no reason that you should need to add any kind of logic to
 expire
 an auction. You especially would not need to do anything in your
 Application.cfm/Application.cfc. When a user views an auction, just check
 the auction end date/time against the current date/time, and if the end
 date
 has already passed, set some kind of a flag:

 ie. cfset isAuctionFinished = dateCompare( now(), auction_end_date ) EQ 1
 /

 That's it. All you have to do at that point is just check in your code if
 the auction is finished, and take the necessary action based on that. I am
 leaving out tons of implementation specific details, and of course you'ld
 want to validate your data, but the general idea is that you just check if
 the auction is finished whenever a user views it. You're having to
 retrieve
 that auction data anyways, so what's the big deal in adding one extra
 check
 to see if it's finished? I don't see any reason to run a scheduled job
 when
 it's not a problem to do the check in real time.

 As for the preferred payment, why not give the seller the opportunity to
 do
 both things you talked about? In their profile, let them choose a
 preferred
 method, but at the same time, give them the option to use a different
 method
 on a per-auction basis.

 - Justin

 On 10/8/06, Doug Brown [EMAIL PROTECTED] wrote:
 
  Dina,
 
 
  Thanks for the reply. As for the first question, [auction_end] I
  understand
  how to set-up the table as far as that goes, but am not sure if I should
  simply run the code for checking the expire date/time in the
  application.cfm
  or another way. What would you do?
 
 
 
 
 
  - Original Message -
  From: Dina Hess [EMAIL PROTECTED]
  To: CF-Talk cf-talk@houseoffusion.com
  Sent: Sunday, October 08, 2006 9:34 AM
  Subject: Re: database design revisted
 
 
   [auction expire]
  
   auction_start_date
   days_listed
   auction_end_date
  
   Maybe I'm missing something here, but it appears that when an auction
 is
   initially configured, you can set the auction_end_date by
   adding auction_start_date and days_listed. From there, all you need to
  do
  is
   compare auction_end_date to the current date.
  
   [payment_method]
  
   I would set up master tables for payments options and shipping options
  for
   use with separate preference tables for payment and shipping:
  
   (assume SQL Server database...untested)
  
   -- master table for payment options
   create table payment_options (
  pmt_option_id int identity not null primary key,
  pmt_option_desc varchar (50)
   );
  
   -- master table for shipping options
   create table shipping_options (
  shp_option_id int identity not null primary key,
  shp_option_desc varchar (50)
   );
  
   create table seller_pmt_preferences (
  user_id int not null
references user (user_id),
  pmt_option_id  int not null
references payment_options (pmt_option_id),
  constraint pk_seller_pmt_pref
primary key clustered (user_id, pmt_option_id)
   );
  
   create table seller_shp_preferences (
  user_id int not null
 references user (user_id),
  shp_option_id int not null
 references shipping_options (shp_option_id),
   constraint pk_seller_shp_pref
primary key clustered (user_id, shp_option_id)
   );
  
   Then use the following query syntax to pull the seller options for
  display:
  
   !--- get seller payment options ---
   select p.pmt_option_id, p.pmt_option_desc
   from payment_options p inner join seller_pmt_preferences s
   on s.pmt_option_id = p.pmt_option_id
   where s.user_id = cfqueryparam cfsqltype=cf_sql_integer
   value=#form.user_id#
  
   Dina
  
  
  
 
 

 

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

Re: database design revisted

[Dina Hess]

 [auction expire]

auction_start_date
days_listed
auction_end_date

Maybe I'm missing something here, but it appears that when an auction is
initially configured, you can set the auction_end_date by
adding auction_start_date and days_listed. From there, all you need to do is
compare auction_end_date to the current date.

[payment_method]

I would set up master tables for payments options and shipping options for
use with separate preference tables for payment and shipping:

(assume SQL Server database...untested)

-- master table for payment options
create table payment_options (
   pmt_option_id int identity not null primary key,
   pmt_option_desc varchar (50)
);

-- master table for shipping options
create table shipping_options (
   shp_option_id int identity not null primary key,
   shp_option_desc varchar (50)
);

create table seller_pmt_preferences (
   user_id int not null
 references user (user_id),
   pmt_option_id  int not null
 references payment_options (pmt_option_id),
   constraint pk_seller_pmt_pref
 primary key clustered (user_id, pmt_option_id)
);

create table seller_shp_preferences (
   user_id int not null
  references user (user_id),
   shp_option_id int not null
  references shipping_options (shp_option_id),
constraint pk_seller_shp_pref
 primary key clustered (user_id, shp_option_id)
);

Then use the following query syntax to pull the seller options for display:

!--- get seller payment options ---
select p.pmt_option_id, p.pmt_option_desc
from payment_options p inner join seller_pmt_preferences s
on s.pmt_option_id = p.pmt_option_id
where s.user_id = cfqueryparam cfsqltype=cf_sql_integer
value=#form.user_id#

Dina


~|
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:255921
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: database design revisted

[Dina Hess]

What version of ColdFusion are you using, Doug?

On 10/8/06, Doug Brown [EMAIL PROTECTED] wrote:

 Dina,


 Thanks for the reply. As for the first question, [auction_end] I
 understand
 how to set-up the table as far as that goes, but am not sure if I should
 simply run the code for checking the expire date/time in the
 application.cfm
 or another way. What would you do?





 - Original Message -
 From: Dina Hess [EMAIL PROTECTED]
 To: CF-Talk cf-talk@houseoffusion.com
 Sent: Sunday, October 08, 2006 9:34 AM
 Subject: Re: database design revisted


  [auction expire]
 
  auction_start_date
  days_listed
  auction_end_date
 
  Maybe I'm missing something here, but it appears that when an auction is
  initially configured, you can set the auction_end_date by
  adding auction_start_date and days_listed. From there, all you need to
 do
 is
  compare auction_end_date to the current date.
 
  [payment_method]
 
  I would set up master tables for payments options and shipping options
 for
  use with separate preference tables for payment and shipping:
 
  (assume SQL Server database...untested)
 
  -- master table for payment options
  create table payment_options (
 pmt_option_id int identity not null primary key,
 pmt_option_desc varchar (50)
  );
 
  -- master table for shipping options
  create table shipping_options (
 shp_option_id int identity not null primary key,
 shp_option_desc varchar (50)
  );
 
  create table seller_pmt_preferences (
 user_id int not null
   references user (user_id),
 pmt_option_id  int not null
   references payment_options (pmt_option_id),
 constraint pk_seller_pmt_pref
   primary key clustered (user_id, pmt_option_id)
  );
 
  create table seller_shp_preferences (
 user_id int not null
references user (user_id),
 shp_option_id int not null
references shipping_options (shp_option_id),
  constraint pk_seller_shp_pref
   primary key clustered (user_id, shp_option_id)
  );
 
  Then use the following query syntax to pull the seller options for
 display:
 
  !--- get seller payment options ---
  select p.pmt_option_id, p.pmt_option_desc
  from payment_options p inner join seller_pmt_preferences s
  on s.pmt_option_id = p.pmt_option_id
  where s.user_id = cfqueryparam cfsqltype=cf_sql_integer
  value=#form.user_id#
 
  Dina
 
 
 

 

~|
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:255923
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: Java help

[Dina Hess]

Bobby,

I think this is what you want:


import java.math.*;
import java.text.*;
import java.util.Locale;

public class BigDecimalFormatTest {

 public static void main (String[] args) {
  double [] nums = {71.715,71.725,71.735,71.745,71.755,71.765,71.775,71.785,
71.795};
  NumberFormat form;
  form = NumberFormat.getCurrencyInstance(Locale.US);
  for (int i = 0; i  nums.length; ++i) {
   BigDecimal theNumber = new BigDecimal (Double.toString(nums[i]));
   theNumber = theNumber.setScale(2, RoundingMode.HALF_UP);
   System.out.println(form.format(theNumber));
  }

 }
}

The key here is the double conversion to BigDecimal and setting the rounding
mode to half_up. The reason you weren't getting what you thought you should
is because DecimalFormat uses the half_even rounding mode.

Dina



On 10/7/06, Bobby Hartsfield [EMAIL PROTECTED] wrote:

 Thanks for the suggestion. I saw that link before but dismissed it for
 some
 reason. I guess I assumed it was doing something else. In any event, I
 just
 tried it and it DID come up with different output but still not what it
 should be. I may very well be using it wrong though. It's probably obvious
 that I'm no Java programmer. I'm basically just taking what I know about
 programming in general and the syntax of JavaScript and doing the best I
 can
 here.

 This is what I have from your suggestion and what it displays.

 import java.text.*;
 import java.math.BigDecimal;

 public class dollarFormattingTest
 {
 public static void main(String[] args)
 {
 DecimalFormat dollarFormat = new DecimalFormat($0.00);
 System.out.println(dollarFormat.format(new java.math.BigDecimal(71.815)));
 System.out.println(dollarFormat.format(new java.math.BigDecimal(71.825)));
 System.out.println(dollarFormat.format(new java.math.BigDecimal(71.835)));
 System.out.println(dollarFormat.format(new java.math.BigDecimal(71.845)));
 System.out.println(dollarFormat.format(new java.math.BigDecimal(71.855)));
 System.out.println(dollarFormat.format(new java.math.BigDecimal(71.865)));
 System.out.println(dollarFormat.format(new java.math.BigDecimal(71.875)));
 System.out.println(dollarFormat.format(new java.math.BigDecimal(71.885)));
 System.out.println(dollarFormat.format(new java.math.BigDecimal(71.895)));
 }
 }


 This is the output now...

 $71.81
 $71.83
 $71.83
 $71.84
 $71.86
 $71.86
 $71.88
 $71.89
 $71.89

 That's either extremely weird... or I'm completely missing something
 obvious. :-/


 -Original Message-
 From: Denny Valliant [mailto:[EMAIL PROTECTED]
 Sent: Saturday, October 07, 2006 1:25 AM
 To: CF-Talk
 Subject: Re: Java help

 Could this be it?

 http://forum.java.sun.com/thread.jspa?threadID=736383messageID=4230346


 On 10/6/06, Bobby Hartsfield [EMAIL PROTECTED] wrote:
  I should also mention that I tried this as well...
 .

 --
 No virus found in this outgoing message.
 Checked by AVG Free Edition.
 Version: 7.1.407 / Virus Database: 268.13.0/465 - Release Date: 10/6/2006




 

~|
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:255891
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 Question

[Dina Hess]

http://mysite.verizon.net/Graeme_Birchall/id1.html

Download and go to page 302.

Dina


On 9/1/06, loathe [EMAIL PROTECTED] wrote:

 I have an organization table.  There is an org_id and a parent_org_id.

 I am trying to get all organizations from a certain point down.  The
 environment is DB/2.

 So far I'm doing:

 Select
org.org_id,
org.parent_org_id
 From
organizations org
 Left join
Organizations org2
 On
Org.org_id = org2.parentID

 Where org.org_id = #session.filter.org_id#


 I know I have seen something similar to this before.  It's no problem
 going
 down one level but there seems to be a need to do some sort of recursion
 or
 something in order to go lower than that.

 I'm beating myself over the head with this one, on a Friday afternoon, any
 pointers would be much appreciated.



 

~|
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:251963
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: insert a list into database

[Dina Hess]

Sorry, Isaac. You saw what I missed...that John's tbl_list was the name of
a single column, not a variable representing a list of columns. And in light
of that error on my part, I apologize for my flip response.


On 4/14/06, Dina Hess [EMAIL PROTECTED] wrote:

 Hmmm...too bad you wrote all that before you tried it. :)


 On 4/14/06, S. Isaac Dealey [EMAIL PROTECTED] wrote:
 
  I don't think that will work with an insert statement. The standard
  for SQL insists on one value per column for inserts and updates. It's
  only IN and NOT IN constructs in where clauses where comma separated
  lists are allowed. In an insert statement with only one column in the
  table, a comma in the values list should produce a too many values
  or number of values does not match number of columns error message.
 
   John,
 
   You also don't need the loop. Just add list=yes inside
   your cfqueryparam.
 
   Dina
 
 
   On 4/14/06, john holmes [EMAIL PROTECTED] wrote:
  
   That surely was it, thank you.
  
   Jerry Johnson  [EMAIL PROTECTED] wrote: In an insert
   statement, you don't
   need the tml_list = in the values set.
  
   INSERT into tbl
   (field_list)
   VALUES
   (valuelist)
  
   so in your example
   INSERT INTOList_prac
   (tbl_list)
   VALUES
   ()
  
  
   On 4/14/06, john holmes  wrote:
Hi,
I have an list that I need to insert into a mysql
database and I am not
   quite sure how to do it. Right now I take the list
   variable
   #thiswillbemylist# and if I run a cfloop list to insert
   it but it just
   gets a null value in database.
   
The part of query to insert is this:
   
   
   
INSERT INTOList_prac (tbl_list)
VALUES (tbl_list = )
   
   
   
   
Can someone help?
Thanks
John
   
   
   
-
Talk is cheap. Use Yahoo! Messenger to make PC-to-Phone
calls.  Great
   rates starting at 1�/min.
   
   
  
  
  
  
 
   ~~
  
 
  

~|
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:237830
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations  Support: http://www.houseoffusion.com/tiny.cfm/54

Re: insert a list into database

[Dina Hess]

Hmmm...too bad you wrote all that before you tried it. :)

On 4/14/06, S. Isaac Dealey [EMAIL PROTECTED] wrote:

 I don't think that will work with an insert statement. The standard
 for SQL insists on one value per column for inserts and updates. It's
 only IN and NOT IN constructs in where clauses where comma separated
 lists are allowed. In an insert statement with only one column in the
 table, a comma in the values list should produce a too many values
 or number of values does not match number of columns error message.

  John,

  You also don't need the loop. Just add list=yes inside
  your cfqueryparam.

  Dina


  On 4/14/06, john holmes [EMAIL PROTECTED] wrote:
 
  That surely was it, thank you.
 
  Jerry Johnson [EMAIL PROTECTED] wrote: In an insert
  statement, you don't
  need the tml_list = in the values set.
 
  INSERT into tbl
  (field_list)
  VALUES
  (valuelist)
 
  so in your example
  INSERT INTOList_prac
  (tbl_list)
  VALUES
  ()
 
 
  On 4/14/06, john holmes  wrote:
   Hi,
   I have an list that I need to insert into a mysql
   database and I am not
  quite sure how to do it. Right now I take the list
  variable
  #thiswillbemylist# and if I run a cfloop list to insert
  it but it just
  gets a null value in database.
  
   The part of query to insert is this:
  
  
  
   INSERT INTOList_prac (tbl_list)
   VALUES (tbl_list = )
  
  
  
  
   Can someone help?
   Thanks
   John
  
  
  
   -
   Talk is cheap. Use Yahoo! Messenger to make PC-to-Phone
   calls.  Great
  rates starting at 1�/min.
  
  
 
 
 
 

  ~~
 

 

~|
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:237839
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations  Support: http://www.houseoffusion.com/tiny.cfm/54

Re: SQL syntax

[Dina Hess]

SQL Server doesn't support an exponent operator (^), so you'll need to
use either the POWER or EXP function instead. Also, some of your
parentheses seemed to be off, so try this:

2 * asin(sqrt( power(sin((lat1-lat2)/2),2) + cos(lat1) * cos(lat2) *
power(sin((lon1-lon2)/2),2) ) )

In the future, you may want to try the HoF SQL mailing list for a
faster response.

Dina



On 12/2/05, Ken [EMAIL PROTECTED] wrote:
 Hi. I want to use this in one of my stored procedures. Can anyone correct
 the syntax here for me please. The formula i want to use is:

 2*asin(sqrt((sin((lat1-lat2)/2))^2 +
 cos(lat1)*cos(lat2)*(sin((lon1-lon2)/2))^2))

 Thanks,
 K


 

~|
Logware (www.logware.us): a new and convenient web-based time tracking 
application. Start tracking and documenting hours spent on a project or with a 
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:226049
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations  Support: http://www.houseoffusion.com/tiny.cfm/54

Re: OT - Complex SQL

[Dina Hess]

Jillian,
 If you're using DB2 UDB, you can use the date() function to pass in your
date:
 where table_1.expiry_date = date('2005-12-31') ...
 Dina

 On 10/28/05, Dawson, Michael [EMAIL PROTECTED] wrote:

 Along these lines, you might want to look into DB2's data dictionary.
 On our AS400, you can find the data dictionary in the qsys2 libraries.
 Look for a table named syscolumns. That table will give you the
 details on any column in the database.

 I created a simple DB2 datadictionary view a few years ago and it comes
 in quite handy.

 M!ke

 -Original Message-
 From: Jillian Koskie [mailto:[EMAIL PROTECTED]
 Sent: Friday, October 28, 2005 5:41 PM
 To: CF-Talk
 Subject: RE: OT - Complex SQL

 You're so right... Anybody happen to now offhand (I'm off to search
 after I send this message) what the date format is for DB2?

 I'm just running this at the command line, so no need for any CF
 stuffs... But unfortuantely also can't take advantage of CF stuffs.

 -Original Message-
 From: Dawson, Michael [mailto:[EMAIL PROTECTED]
 Sent: Friday, October 28, 2005 4:16 PM
 To: CF-Talk
 Subject: RE: OT - Complex SQL

 You need single quotes around each of your values in the IN clause.

 snip
 AND table_1.table_id IN ('value1','value2','value3') /snip

 The date formats may be wrong, too. Check the actual data in the
 database and/or use createODBCDateTime() and definitely use
 CFQUERYPARAM. It will make your job a bit eaiser.

 Either way, I would remove the dates from your WHERE claus, fix the IN
 clause, then run the query. See if you get any results with very
 limited filtering, first.

 Then, go from there and add more filtering as needed.

 M!ke

 -Original Message-
 From: Jillian Koskie [mailto:[EMAIL PROTECTED]
 Sent: Friday, October 28, 2005 5:12 PM
 To: CF-Talk
 Subject: OT - Complex SQL

 Okay, I think it's because it is Friday at 4... can somebody help me
 out?

 SELECT table_1.expiry_date,
 table_3.expiry_date
 FROM table_3
 JOIN table_2 ON table_2.table2_key = table_3.tablel2_key
 JOIN table_1 ON table_1.table1_key = table_2.table1_key
 WHERE table_1.expiry_date = 'Dec 31, 2005'
 AND table_3.expiry_date = 'Dec 31, 2005'
 AND table_1.table_id IN ('value1,value2,value3')

 This isn't returning results, and it should... where is my syntax
 incorrect?

 --
 Jillian





 

~|
Logware (www.logware.us): a new and convenient web-based time tracking 
application. Start tracking and documenting hours spent on a project or with a 
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:222645
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations  Support: http://www.houseoffusion.com/tiny.cfm/54

Re: CF7 lots of new Ambiguous column name issues

[Dina Hess]

Well, I'd like to see the parsed query to be certain that your problem
is fully addressed but, from what you've provided, I'd say Claude has
honed in on your issue. You need to scope the formobjectitemid and
archive columns with either the version or companynews table. This is
best practice for any SQL statement that references more than one
table.

If you'd like to provide the parsed query, you can do so in one of two
ways: 1) copy the parsed query from your debug info or 2) comment out
the CFQUERY tags and replace them with CFOUTPUT tags.

~|
Discover CFTicket - The leading ColdFusion Help Desk and Trouble 
Ticket application

http://www.houseoffusion.com/banners/view.cfm?bannerid=48

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:214511
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations  Support: http://www.houseoffusion.com/tiny.cfm/54

Re: DatePart and QoQ ?

[Dina Hess]

Macromedia has provided a QoQ BNF resource, which includes a handful
of  aggregate functions (but that's about it)
at(http://livedocs.macromedia.com/coldfusion/6/Developing_ColdFusion_MX_Applications_with_CFML/using_recordsets5.htm

~|
Logware (www.logware.us): a new and convenient web-based time tracking 
application. Start tracking and documenting hours spent on a project or with a 
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:214513
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations  Support: http://www.houseoffusion.com/tiny.cfm/54

Re: Code function

[Dina Hess]

Port 8500 is in reference to ColdFusion's built-in web server, which
was unavailable in CF 5.0 and prior versions. I'm guessing that is
your problem.

~|
Logware (www.logware.us): a new and convenient web-based time tracking 
application. Start tracking and documenting hours spent on a project or with a 
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:212272
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations  Support: http://www.houseoffusion.com/tiny.cfm/54

Re: Query Help

[Dina Hess]

Eric,

You can find total payments made against orders in a specific date range that 
contain a specific part number by doing something like this:

select 
sum(p.pmt) as order_pmts, 
i.part_number,
o.order_number,
o.order_date,
o.order_total
from orders o 
inner join order_items i on o.order_id = i.order_id
inner join payments on o.order_id = p.order_id
where o.order_date 
between cfqueryparam cfsqltype=cf_sql_timestamp value=#start_date#
 and cfqueryparam cfsqltype=cf_sql_timestamp value=#end_date#
and i.part_number = 
cfqueryparam cfsqltype=cf_sql_varchar value=#part_number#
group by
o.order_date, i.part_number, o.order_total, o.order_number
order by
o.order_date

If you need to find payments for orders with multiple part numbers, you can 
rewrite the part number filter to:

and i.part_number in (cfqueryparam cfsqltype=cf_sql_varchar list=yes 
value=#part_number#)

This is not tested and is not database-specific since you didn't mention what 
DBMS you are using, but it should get you started in the right direction. 

Dina

  
- Original Message - 
From: Eric Hoffman [EMAIL PROTECTED]
To: CF-Talk cf-talk@houseoffusion.com
Sent: Monday, January 24, 2005 10:43 AM
Subject: RE: Query Help


 I know its goofybut lets say the 500 lb gorilla wants to eat...and
 he likes crappy reports.
 
 Is there a logic pattern I should follow...or do I run the query to find
 total $ in order items that contain the inventory itemand then check
 the payments table for any payments, and then just say, well, if there
 was a payment...count it?   I would love to do in a single SQL query...
 
 Know what I mean?
 
 -Original Message-
 From: Dina Hess [mailto:[EMAIL PROTECTED] 
 Sent: Monday, January 24, 2005 10:12 AM
 To: CF-Talk
 Subject: Re: Query Help
 
 Eric,
 
 I don't think the problem has anything to do with your knowledge of SQL.
 It's not typical for a company to allocate order payments to specific
 order line items; rather, payments are typically applied to the entire
 order. Therefore, how could you possibly determine which line item a
 payment should be applied to...unless I'm missing something.
 
 Dina
 
 
 - Original Message -
 From: Eric Hoffman [EMAIL PROTECTED]
 To: CF-Talk cf-talk@houseoffusion.com
 Sent: Monday, January 24, 2005 7:27 AM
 Subject: Query Help
 
 
  I have to make a report based upon this scenario...seems simple, yet I
  can't get the numbers rightany pointers?  (I have ordered a newer
  Advanced SQL book in the meantime!!)
  
  Need to get orders within date range containing a part number and get
  total dollars received in payments for this.
  
  So I query the orders tablewhich is related to the order_items
  table, where each line item of the order is stored, but does include a
  line_total field which did qty * rate charged.  On the other hand, the
  payments table is related to the orders table by order id, and shows
  amount paid...but obviously not breaking out what of the payment
 applies
  to what. 
  
  So how do I query to get the applicable payment amount correct by that
  item id?  Right now my numbers are inflated because it gets the whole
  order payment amount.  Recievables higher than sales is great in
  fantasyland.  Mucho frustrating no doubt.
  
  Thanks guys.
  
  Eric
  
  
 
 
 
 

~|
Discover CFTicket - The leading ColdFusion Help Desk and Trouble 
Ticket application

http://www.houseoffusion.com/banners/view.cfm?bannerid=48

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:191691
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations  Support: http://www.houseoffusion.com/tiny.cfm/54

Re: Query Help

[Dina Hess]

Eric,

I don't think the problem has anything to do with your knowledge of SQL. It's 
not typical for a company to allocate order payments to specific order line 
items; rather, payments are typically applied to the entire order. Therefore, 
how could you possibly determine which line item a payment should be applied 
to...unless I'm missing something.

Dina


- Original Message - 
From: Eric Hoffman [EMAIL PROTECTED]
To: CF-Talk cf-talk@houseoffusion.com
Sent: Monday, January 24, 2005 7:27 AM
Subject: Query Help


 I have to make a report based upon this scenario...seems simple, yet I
 can't get the numbers rightany pointers?  (I have ordered a newer
 Advanced SQL book in the meantime!!)
 
 Need to get orders within date range containing a part number and get
 total dollars received in payments for this.
 
 So I query the orders tablewhich is related to the order_items
 table, where each line item of the order is stored, but does include a
 line_total field which did qty * rate charged.  On the other hand, the
 payments table is related to the orders table by order id, and shows
 amount paid...but obviously not breaking out what of the payment applies
 to what. 
 
 So how do I query to get the applicable payment amount correct by that
 item id?  Right now my numbers are inflated because it gets the whole
 order payment amount.  Recievables higher than sales is great in
 fantasyland.  Mucho frustrating no doubt.
 
 Thanks guys.
 
 Eric
 
 

~|
Logware: a new and convenient web-based time tracking application. Start 
tracking and documenting hours spent on a project or with a client with Logware 
today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:191537
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations  Support: http://www.houseoffusion.com/tiny.cfm/54

cfmx cfquery dbtype=odbc

[Dina Hess]

The application I'm currently working on was originally written in CF 4.5 so 
I'm frequently seeing dbtype=odbc in CFQUERY tags. Does anyone know if that 
setting could adversely affect performance now that the DBTYPE attribute is to 
be used to designate a QofQ?

Thanks much,
Dina

~|
Protect your mail server with built in anti-virus protection. It's not only 
good for you, it's good for everybody.
http://www.houseoffusion.com/banners/view.cfm?bannerid=39

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:189698
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations  Support: http://www.houseoffusion.com/tiny.cfm/54

Re: cfmx cfquery dbtype=

[Dina Hess]

Thanks, Dave. I was surprised to see that DBTYPE=odbc was working in this 
application as well, but it is. At this point, I'm just trying to determine if 
a DBTYPE=odbc designation in CFMX would adversely affect performance for a 
JDBC datasource. 

~|
Discover CFTicket - The leading ColdFusion Help Desk and Trouble 
Ticket application

http://www.houseoffusion.com/banners/view.cfm?bannerid=48

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:189703
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations  Support: http://www.houseoffusion.com/tiny.cfm/54

Cluster Verity Maintenance

[Dina Hess]

I have a few intranet applications hosted in a clustered server 
environment -- IIS with CF running on top of JRun. The applications use 
Verity collections that need to be maintained periodically.

What's the best approach to doing this in a clustered environment so
that all nodes are updated simultaneously?

I've tried CFSCHEDULE, but the virtual server pointing to the nodes only reflects the 
scheduled tasks from one of the nodes. I've also tried 
doing this programmatically but that didn't work either.

Any suggestions from those of you more accustomed to working in a 
clustered server environment would sure be appreciated.

Thanks much,
~Dina


~|
Purchase from House of Fusion, a Macromedia Authorized Affiliate and support the CF 
community.
http://www.houseoffusion.com/banners/view.cfm?bannerid=37

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:182611
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations  Support: http://www.houseoffusion.com/tiny.cfm/54

Re: Cluster Verity Maintenance

[Dina Hess]

Thanks, Dave. I'll look into that. I had never used Verity prior to this job and 
simply followed the previous developer's lead...but then, he wasn't running apps on a 
cluster. Your help is much appreciated.!

~Dina

- Original Message - 
From: Dave Watts [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Tuesday, October 26, 2004 8:47 AM
Subject: RE: Cluster Verity Maintenance


  I have a few intranet applications hosted in a clustered 
  server environment -- IIS with CF running on top of JRun. The 
  applications use Verity collections that need to be 
  maintained periodically.
  
  What's the best approach to doing this in a clustered 
  environment so that all nodes are updated simultaneously?
  
  I've tried CFSCHEDULE, but the virtual server pointing to the 
  nodes only reflects the scheduled tasks from one of the 
  nodes. I've also tried doing this programmatically but that 
  didn't work either.
  
  Any suggestions from those of you more accustomed to working 
  in a clustered server environment would sure be appreciated.
 
 Have you considered running Verity on a single server using K2? You'd only
 need one set of collections in this case, and you could limit indexing tasks
 to that one server.
 
 Dave Watts, CTO, Fig Leaf Software
 http://www.figleaf.com/
 phone: 202-797-5496
 fax: 202-797-5444
 
 
 

~|
Purchase from House of Fusion, a Macromedia Authorized Affiliate and support the CF 
community.
http://www.houseoffusion.com/banners/view.cfm?bannerid=34

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:182617
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations  Support: http://www.houseoffusion.com/tiny.cfm/54

Re: Cluster Verity Maintenance

[Dina Hess]

Have you considered running Verity on a single server using K2? 

Dave, if I need a separate server for the Verity collection, doesn't that undermine my 
reasons for using a clustered server environment in the first place? I mean, if that 
Verity server failed, I have no failover redundancy. I haven't read the K2 
documentation yet; am I unclear about the server configuration you're recommending? 

~|
Sams Teach Yourself Regular Expressions in 10 Minutes  by Ben Forta 
http://www.houseoffusion.com/banners/view.cfm?bannerid=40

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:182634
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations  Support: http://www.houseoffusion.com/tiny.cfm/54

Re: Cluster Verity Maintenance

[Dina Hess]

If the server running K2 failed, you wouldn't have any
redundancy. 

Thanks again, Dave. 

As it turns out, I think the problem is resolved. When I CFSCHEDULE'd overnight tasks 
on each of the nodes yesterday, I was sure it wouldn't work because the CF Admin on 
the virtual server only recognized the tasks on one of the nodes. But the log I 
created to track task execution says otherwise. So I tested the collections this 
morning on each node by using the IP/directory rather than the domain name and 
entering a unique search criteria from one of yesterday's entries. Worked great, so I 
guess CFSCHEDULE is the ticket for keeping the collections in sync afterall.

~|
Purchase from House of Fusion, a Macromedia Authorized Affiliate and support the CF 
community.
http://www.houseoffusion.com/banners/view.cfm?bannerid=35

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:182641
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations  Support: http://www.houseoffusion.com/tiny.cfm/54

Re: Execute Query string w CFQUERYPARAM

[Dina Hess]

try htmleditformat(); and it's gt; not @gt;
- Original Message - 
From: Dan O'Keefe
To: CF-Talk
Sent: Monday, October 18, 2004 7:33 AM
Subject: Execute Query string w CFQUERYPARAM

I have a variable that contains the following:

Insert into tblProducts (product,c_productDesc,active)
Values
cfqueryparam cfsqltype='CF_SQL_INTEGER' value='new Product name',
cfqueryparam cfsqltype='CF_SQL_VARCHAR' value='new Product desc',
cfqueryparam cfsqltype='CF_SQL_BIT' value='1'

I want to execute this string inside a cfquery tag. 1st problem is,
when I am building the string and looping over an array, if I try to
use  and  in the string, my string ends up being empty. If I replace
that with @lt; and @gt;, the string is built OK, as displayed abouve,
but get an ODBC error on the @.

I have played around with evaluating and DE, but cannot get past this
point. Is this possible?


Dan O'Keefe
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]
 [Donations and Support]

Re: cflogin ?

[Dina Hess]

Further clarification:

Roles are case sensitive

lcfif IsUserInRole(Sales,Manager) means the user must be in both roles

lcfif IsUserInRole(Sales) or IsUserInRole(Manager) means the user must
be in one role or the other

cffunction name=somemethod roles=Sales,Manager means this method can
only be executed by a user who has been authenticated and authorized by
belonging to any of the named roles

~Dina

- Original Message - 
From: Patricia Lee
To: CF-Talk
Sent: Sunday, October 03, 2004 7:17 AM
Subject: Re: cflogin ?

cfif NOT IsUserInRole('admin') AND NOT IsUserInRole('user')
 ^^^
-Patti

 i cant ever find anything that tells how to check for more than 1
 acceptable roles

 so, say there are 2 roles (admin, user) and both can access the same
pages
 would it be?

 !--- check to see if user is logged in ---
 cfif NOT IsUserInRole('admin') OR NOT IsUserInRole('user')
 cflocation url="" addtoken=no
 cfabort
 /cfif


 i cant find any docs that give any info on it
 tia




 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]
 [Donations and Support]

Re: CFStoredProc vs CFQuery

[Dina Hess]

I'm late to this thread but thought it worth mentioning that ColdFusion MX
Bible cites a method for caching a single result set stored procedure, as
follows:

cfquery name=cached_12hours datasource=#dsn#
cachedwithin=#createtimespan(0,12,0,0)#
{ CALL sp_getAuthors('Churvis') }
/cfquery

~Dina
- Original Message - 
From: Robertson-Ravo, Neil (RX)
To: CF-Talk
Sent: Thursday, September 30, 2004 1:01 AM
Subject: RE: CFStoredProc vs CFQuery

Yeah if you must cache, then use CFQUERY but you can also just duplicate
the
results into a new query object and cache that.

_

From: Dave Watts [mailto:[EMAIL PROTECTED]
Sent: 29 September 2004 20:35
To: CF-Talk
Subject: RE: CFStoredProc vs CFQuery

 Are there any benefits to calling stored procedures using
 CFStoredProc vs. CFQuery? All the stored procs return a
 single record set.

 The limitation I am running in to is how to cache the results
 of a query that is created calling CFStoredProc.

In this case, I'd recommend using CFQUERY to call your stored procedure,
as
Mike recommended. It works just as well, and you can cache it.
Alternatively, you can cache recordsets from CFSTOREDPROC within the
Session, Application or Server scopes.

Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
phone: 202-797-5496
fax: 202-797-5444

_
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]
 [Donations and Support]

Re: PL/SQL stumper

[Dina Hess]

ROWNUM is calculated before the order by.

Good to know. Thanks. Guess it was a fluke that I actually got back what looked like the expected result set.

~Dina
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]

Re: PL/SQL stumper

[Dina Hess]

select provider, lastDate
from
(SELECT
d.provider,
MAX(m.date_submitted) AS lastDate
FROM
fsa_detail d,
fsa_master m
WHERE
d.masterid = m.id
AND m.ssn = '123-45-6789'
GROUP BY
d.provider)
where rownum = 7
ORDER BY lastDate desc, provider
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]

Re: CFMAIL 6.1 ARRGH!

[Dina Hess]

Mark Leder [EMAIL PROTECTED]

Unless the syntax has changed dramatically in 6.1, your FROM and TO email
addresses should be formatted as above.

~Dina



~|
Archives: http://www.houseoffusion.com/lists.cfm?link=t:4
Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Get the mailserver that powers this list at 
http://www.coolfusion.com

Re: CFMAIL 6.1 ARRGH!

[Dina Hess]

snip
Required attributes: 'from,group,query,subject,to'. Optional attributes:
'bcc,cc,charset,debug,failto,groupcasesensitive,mailerid,maxrows,mimeattach,
password,port,queryreplyto,server,spoolenable,startrow,timeout,type,username
,wraptext'.
/snip

According to the error message, the replyto attribute is not supported
when you use from,group, query, subject, and to. Try queryreplyto
instead, I guess. :)

~Dina





~|
Archives: http://www.houseoffusion.com/lists.cfm?link=t:4
Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Your ad could be here. Monies from ads go to support these lists and provide more 
resources for the community. 
http://www.fusionauthority.com/ads.cfm

Re: SQL data help.

[Dina Hess]

Steven,

Try this if your DBMS is SQL Server:

select str((select sum(num_emp)/sum(num_jobs) from test3 where
month(test_date)=1),5,1) as jan_rate, str((select sum(num_emp)/sum(num_jobs)
from test3 where month(test_date)=2),5,1) as feb_rate...

You get the idea. BTW, any jobs up your way? I'm looking. :)

~Dina


- Original Message -
From: DURETTE, STEVEN J (AIT) [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Thursday, July 31, 2003 1:16 PM
Subject: OT: SQL data help.


 Hi all,

 I have data that I am working with some data that I need to (for lack of a
 better term) pivot.

 I have data that comes into a table in the format of:
 Group_ID, Supervisor_ID, Number_Employees, Number_Jobs, File_Date
 Example:
 Engineering, MGR1, 5, 6, 1-1-2003
 Engineering, MGR1, 5, 9, 2-1-2003
 Engineering, MGR2, 5, 6, 1-1-2003
 Engineering, MGR2, 5, 9, 2-1-2003
 Maintenance, MGR1, 5, 6, 1-1-2003
 Maintenance, MGR1, 5, 9, 2-1-2003
 Maintenance, MGR2, 5, 6, 1-1-2003
 Maintenance, MGR2, 5, 9, 2-1-2003

 From this I figure the Rate (which is Number_Jobs / Number_Employees), so
 the table is actually:
 Group_ID, Supervisor_ID, Number_Employees, Number_Jobs, File_Date, RATE
 Example:
 Engineering, MGR1, 5, 6, 1-1-2003, 1.2
 Engineering, MGR1, 5, 9, 2-1-2003, 1.8
 Engineering, MGR2, 5, 6, 1-1-2003, 1.2
 Engineering, MGR2, 5, 9, 2-1-2003, 1.8
 Maintenance, MGR1, 5, 6, 1-1-2003, 1.2
 Maintenance, MGR1, 5, 9, 2-1-2003, 1.8
 Maintenance, MGR2, 5, 6, 1-1-2003, 1.2
 Maintenance, MGR2, 5, 9, 2-1-2003, 1.8

 My problem is that I have to display it like:

 Group_ID, Supervisor_ID, Jan_Rate, Feb_Rate, ..., Dec_Rate

 Example:
 Engineering, MGR1, 1.1, 2.2, 3.3, 4.4, 5.5, 6.6, 7.7, 8.8, 9.9, 10.10,
 11.11, 12.12
 Engineering, MGR2, 1.1, 2.2, 3.3, 4.4, 5.5, 6.6, 7.7, 8.8, 9.9, 10.10,
 11.11, 12.12
 Maintenance, MGR1, 1.1, 2.2, 3.3, 4.4, 5.5, 6.6, 7.7, 8.8, 9.9, 10.10,
 11.11, 12.12
 Maintenance, MGR2, 1.1, 2.2, 3.3, 4.4, 5.5, 6.6, 7.7, 8.8, 9.9, 10.10,
 11.11, 12.12

 Right now, I have it working, but it seems very inefficient.
 What I currently do is create a temp table in the format of the display.
 Then I insert the Group, Supervisor, and Jan Data.  After that I do an
 update for each month.

 This is very slow and just looks wrong. Does anyone have any ideas how I
 might be able to do a pivot (still lack of a better word)?

 Steve

 *COST SAVINGS SUGGESTION*
 For pages that must be printed, change your print settings to print in
 grayscale instead of color.



 Steve Durette
 Mgr-Eng.  Const. Systems Support
 100 S. Main
 Room 314
 Mt. Clemens, MI 48043
 Ofc: 586.466.7654
 Fax: 586.466.1109
  mailto:[EMAIL PROTECTED] [EMAIL PROTECTED]


 
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq

Signup for the Fusion Authority news alert and keep up with the latest news in 
ColdFusion and related topics. 
http://www.fusionauthority.com/signup.cfm

Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Refresh application-scoped variables

[Dina Hess]

Hi all,

Can't seem to get my head around this one... How can I programmatically refresh 
application-scoped variables whenever there's a change? 

Right now, I'm doing something like this in Application.cfm:

   cfparam name=application.init default=0

cfif not application.init
   use cfsavecontent to create application-scoped variables
   set application.init to 1
   /cfif

What I want is some way to automatically reset application.init to 0 whenever these 
variables are modified. 
Can someone point me in the right direction? Resources?

Thanks,
~Dina
http://www.dinahess.com/



 

~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq

Your ad could be here. Monies from ads go to support these lists and provide more 
resources for the community. 
http://www.fusionauthority.com/ads.cfm

Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: Refresh application-scoped variables

[Dina Hess]

So you have a refresh application scope link in an admin area to set
url.reset? Sounds like what I'm doing now. I just wondered if there was a
better to do it.

Thanks for the input, Mike.


- Original Message -
From: Mike Townend [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Sunday, July 13, 2003 1:02 PM
Subject: RE: Refresh application-scoped variables


 I usually do something like..

 CFIF Not IsDefined(Application.DSN) OR IsDefined(URL.Reset)
 !--- Create all Application Vars ---
 /CFIF

 So if I need to reset an app then just call the page and append a ?reset=1

 HTH



 -Original Message-
 From: Dina Hess [mailto:[EMAIL PROTECTED]
 Sent: Sunday, July 13, 2003 18:45
 To: CF-Talk
 Subject: Refresh application-scoped variables


 Hi all,

 Can't seem to get my head around this one... How can I programmatically
 refresh application-scoped variables whenever there's a change?

 Right now, I'm doing something like this in Application.cfm:

cfparam name=application.init default=0

 cfif not application.init
use cfsavecontent to create application-scoped variables
set application.init to 1
/cfif

 What I want is some way to automatically reset application.init to 0
 whenever these variables are modified.
 Can someone point me in the right direction? Resources?

 Thanks,
 ~Dina
 http://www.dinahess.com/






 
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq

Your ad could be here. Monies from ads go to support these lists and provide more 
resources for the community. 
http://www.fusionauthority.com/ads.cfm

Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: Refresh application-scoped variables

[Dina Hess]

No intrusion. I assume you mean why would I refresh the application scope by
using a link in the admin area? Because I'm too stupid to figure out a
better way? :)

I indeed thought I was saving overhead by not resetting the same
application-scoped variables for every request...since they don't change
often.



- Original Message -
From: Matt Robertson [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Sunday, July 13, 2003 2:30 PM
Subject: RE: Refresh application-scoped variables


 Pardon the intrusion, but why would you refresh the application scope
 like this?  Saving overhead rather than using cfparam in
 application.cfm?

 
  Matt Robertson   [EMAIL PROTECTED]
  MSB Designs, Inc.  http://mysecretbase.com
 



 -Original Message-
 From: Dina Hess [mailto:[EMAIL PROTECTED]
 Sent: Sunday, July 13, 2003 12:23 PM
 To: CF-Talk
 Subject: Re: Refresh application-scoped variables


 So you have a refresh application scope link in an admin area to set
 url.reset? Sounds like what I'm doing now. I just wondered if there was
 a
 better to do it.

 Thanks for the input, Mike.


 - Original Message -
 From: Mike Townend [EMAIL PROTECTED]
 To: CF-Talk [EMAIL PROTECTED]
 Sent: Sunday, July 13, 2003 1:02 PM
 Subject: RE: Refresh application-scoped variables


  I usually do something like..
 
  CFIF Not IsDefined(Application.DSN) OR IsDefined(URL.Reset)
  !--- Create all Application Vars ---
  /CFIF
 
  So if I need to reset an app then just call the page and append a
 ?reset=1
 
  HTH
 
 
 
  -Original Message-
  From: Dina Hess [mailto:[EMAIL PROTECTED]
  Sent: Sunday, July 13, 2003 18:45
  To: CF-Talk
  Subject: Refresh application-scoped variables
 
 
  Hi all,
 
  Can't seem to get my head around this one... How can I
 programmatically
  refresh application-scoped variables whenever there's a change?
 
  Right now, I'm doing something like this in Application.cfm:
 
 cfparam name=application.init default=0
 
  cfif not application.init
 use cfsavecontent to create application-scoped variables
 set application.init to 1
 /cfif
 
  What I want is some way to automatically reset application.init to 0
  whenever these variables are modified.
  Can someone point me in the right direction? Resources?
 
  Thanks,
  ~Dina
  http://www.dinahess.com/
 
 
 
 
 
 
 

 
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq

Your ad could be here. Monies from ads go to support these lists and provide more 
resources for the community. 
http://www.fusionauthority.com/ads.cfm

Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: Refresh application-scoped variables

[Dina Hess]

Well, I for one am grateful that you're a nerd. :) That's exactly the kind
of information I was looking for. Thanks, Matt.


- Original Message -
From: Matt Robertson [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Sunday, July 13, 2003 3:21 PM
Subject: RE: Refresh application-scoped variables


 Well, I don't fully understand where you're at applicationwise, but I
 try to use a cached query for most of my application-wide settings.
 Keeps the locks and copies to request scope down to near zero.  The few
 values that need to tunnel down to custom tags (a couple of base url and
 file path settings) get converted to request scope in application.cfm.

 I don't usually reset these values manually.  I tend to go for short
 caches of about 10 seconds.  If my server can't handle a db read once
 every 10 seconds I have bigger problems :D.  Still, I hacked up a cfif
 to allow a manual reset below.

 This probably has no bearing on your needs... Just a little daffy after
 a weekend of solid coding and looking for a distraction.  Egads...
 Escape from coding by hanging out on CF-Talk.  Am I a geek or what?

 
  Matt Robertson   [EMAIL PROTECTED]
  MSB Designs, Inc.  http://mysecretbase.com
 

 cfif url.reset is not kaboom
cfset request.SetMins=10
cfset request.SetSecs=0
 cfelse
cfset request.SetMins=0
cfset request.SetSecs=1
 /cfif
 cfset variables.mySettings=Foo
 cfquery
 name=SystemDefaults
 datasource=#request.SiteDSN#
 maxrows=1

 cachedwithin=#CreateTimeSpan(0,0,request.SetMins,request.SetSecs)#
 SELECT
 settings.myPath,
 settings.myBaseURL,
 settings.blahblahblah
 FROM settings
 WHERE 0=0
 /cfquery
 cfset request.BasePath=SystemDefaults.myPath
 cfset request.BaseURL=SystemDefaults.myBaseURL

 -Original Message-
 From: Dina Hess [mailto:[EMAIL PROTECTED]
 Sent: Sunday, July 13, 2003 12:49 PM
 To: CF-Talk
 Subject: Re: Refresh application-scoped variables


 No intrusion. I assume you mean why would I refresh the application
 scope by
 using a link in the admin area? Because I'm too stupid to figure out a
 better way? :)

 I indeed thought I was saving overhead by not resetting the same
 application-scoped variables for every request...since they don't change
 often.



 - Original Message -
 From: Matt Robertson [EMAIL PROTECTED]
 To: CF-Talk [EMAIL PROTECTED]
 Sent: Sunday, July 13, 2003 2:30 PM
 Subject: RE: Refresh application-scoped variables


  Pardon the intrusion, but why would you refresh the application scope
  like this?  Saving overhead rather than using cfparam in
  application.cfm?
 
  
   Matt Robertson   [EMAIL PROTECTED]
   MSB Designs, Inc.  http://mysecretbase.com
  
 
 
 
  -Original Message-
  From: Dina Hess [mailto:[EMAIL PROTECTED]
  Sent: Sunday, July 13, 2003 12:23 PM
  To: CF-Talk
  Subject: Re: Refresh application-scoped variables
 
 
  So you have a refresh application scope link in an admin area to set
  url.reset? Sounds like what I'm doing now. I just wondered if there
 was
  a
  better to do it.
 
  Thanks for the input, Mike.
 
 
  - Original Message -
  From: Mike Townend [EMAIL PROTECTED]
  To: CF-Talk [EMAIL PROTECTED]
  Sent: Sunday, July 13, 2003 1:02 PM
  Subject: RE: Refresh application-scoped variables
 
 
   I usually do something like..
  
   CFIF Not IsDefined(Application.DSN) OR IsDefined(URL.Reset)
   !--- Create all Application Vars ---
   /CFIF
  
   So if I need to reset an app then just call the page and append a
  ?reset=1
  
   HTH
  
  
  
   -Original Message-
   From: Dina Hess [mailto:[EMAIL PROTECTED]
   Sent: Sunday, July 13, 2003 18:45
   To: CF-Talk
   Subject: Refresh application-scoped variables
  
  
   Hi all,
  
   Can't seem to get my head around this one... How can I
  programmatically
   refresh application-scoped variables whenever there's a change?
  
   Right now, I'm doing something like this in Application.cfm:
  
  cfparam name=application.init default=0
  
   cfif not application.init
  use cfsavecontent to create application-scoped variables
  set application.init to 1
  /cfif
  
   What I want is some way to automatically reset application.init to 0
   whenever these variables are modified.
   Can someone point me in the right direction? Resources?
  
   Thanks,
   ~Dina
   http://www.dinahess.com/
  
  
  
  
  
  
  
 
 

 
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq

Get the mailserver that powers this list at 
http://www.coolfusion.com

Unsubscribe

Re: Refresh application-scoped variables

[Dina Hess]

oops...geek, that is. much cooler connotation. :)

- Original Message -
From: Dina Hess [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Sunday, July 13, 2003 3:35 PM
Subject: Re: Refresh application-scoped variables


 Well, I for one am grateful that you're a nerd. :) That's exactly the kind
 of information I was looking for. Thanks, Matt.


 - Original Message -
 From: Matt Robertson [EMAIL PROTECTED]
 To: CF-Talk [EMAIL PROTECTED]
 Sent: Sunday, July 13, 2003 3:21 PM
 Subject: RE: Refresh application-scoped variables


  Well, I don't fully understand where you're at applicationwise, but I
  try to use a cached query for most of my application-wide settings.
  Keeps the locks and copies to request scope down to near zero.  The few
  values that need to tunnel down to custom tags (a couple of base url and
  file path settings) get converted to request scope in application.cfm.
 
  I don't usually reset these values manually.  I tend to go for short
  caches of about 10 seconds.  If my server can't handle a db read once
  every 10 seconds I have bigger problems :D.  Still, I hacked up a cfif
  to allow a manual reset below.
 
  This probably has no bearing on your needs... Just a little daffy after
  a weekend of solid coding and looking for a distraction.  Egads...
  Escape from coding by hanging out on CF-Talk.  Am I a geek or what?
 
  
   Matt Robertson   [EMAIL PROTECTED]
   MSB Designs, Inc.  http://mysecretbase.com
  
 
  cfif url.reset is not kaboom
 cfset request.SetMins=10
 cfset request.SetSecs=0
  cfelse
 cfset request.SetMins=0
 cfset request.SetSecs=1
  /cfif
  cfset variables.mySettings=Foo
  cfquery
  name=SystemDefaults
  datasource=#request.SiteDSN#
  maxrows=1
 
  cachedwithin=#CreateTimeSpan(0,0,request.SetMins,request.SetSecs)#
  SELECT
  settings.myPath,
  settings.myBaseURL,
  settings.blahblahblah
  FROM settings
  WHERE 0=0
  /cfquery
  cfset request.BasePath=SystemDefaults.myPath
  cfset request.BaseURL=SystemDefaults.myBaseURL
 
  -Original Message-
  From: Dina Hess [mailto:[EMAIL PROTECTED]
  Sent: Sunday, July 13, 2003 12:49 PM
  To: CF-Talk
  Subject: Re: Refresh application-scoped variables
 
 
  No intrusion. I assume you mean why would I refresh the application
  scope by
  using a link in the admin area? Because I'm too stupid to figure out a
  better way? :)
 
  I indeed thought I was saving overhead by not resetting the same
  application-scoped variables for every request...since they don't change
  often.
 
 
 
  - Original Message -
  From: Matt Robertson [EMAIL PROTECTED]
  To: CF-Talk [EMAIL PROTECTED]
  Sent: Sunday, July 13, 2003 2:30 PM
  Subject: RE: Refresh application-scoped variables
 
 
   Pardon the intrusion, but why would you refresh the application scope
   like this?  Saving overhead rather than using cfparam in
   application.cfm?
  
   
Matt Robertson   [EMAIL PROTECTED]
MSB Designs, Inc.  http://mysecretbase.com
   
  
  
  
   -Original Message-
   From: Dina Hess [mailto:[EMAIL PROTECTED]
   Sent: Sunday, July 13, 2003 12:23 PM
   To: CF-Talk
   Subject: Re: Refresh application-scoped variables
  
  
   So you have a refresh application scope link in an admin area to set
   url.reset? Sounds like what I'm doing now. I just wondered if there
  was
   a
   better to do it.
  
   Thanks for the input, Mike.
  
  
   - Original Message -
   From: Mike Townend [EMAIL PROTECTED]
   To: CF-Talk [EMAIL PROTECTED]
   Sent: Sunday, July 13, 2003 1:02 PM
   Subject: RE: Refresh application-scoped variables
  
  
I usually do something like..
   
CFIF Not IsDefined(Application.DSN) OR IsDefined(URL.Reset)
!--- Create all Application Vars ---
/CFIF
   
So if I need to reset an app then just call the page and append a
   ?reset=1
   
HTH
   
   
   
-Original Message-
From: Dina Hess [mailto:[EMAIL PROTECTED]
Sent: Sunday, July 13, 2003 18:45
To: CF-Talk
Subject: Refresh application-scoped variables
   
   
Hi all,
   
Can't seem to get my head around this one... How can I
   programmatically
refresh application-scoped variables whenever there's a change?
   
Right now, I'm doing something like this in Application.cfm:
   
   cfparam name=application.init default=0
   
cfif not application.init
   use cfsavecontent to create application-scoped variables
   set application.init to 1
   /cfif
   
What I want is some way to automatically reset application.init to 0
whenever these variables are modified.
Can someone point me in the right direction? Resources?
   
Thanks,
~Dina
http://www.dinahess.com

Re: Refresh application-scoped variables

[Dina Hess]

Good stuff, Sean. Thank you.

I've decided to move the application-scoped variables in this small app into
the request scope. They do need to be available to all instances of the
application, but I have a feeling they will change more often than I
originally anticipated. :)


- Original Message -
From: Sean A Corfield [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Sunday, July 13, 2003 4:09 PM
Subject: Re: Refresh application-scoped variables


 On Sunday, Jul 13, 2003, at 13:21 US/Pacific, Matt Robertson wrote:
  Well, I don't fully understand where you're at applicationwise, but I
  try to use a cached query for most of my application-wide settings.

 Interesting. mm.com uses a couple of techniques:
 1. 'constant' values are set in request scope for every request via an
 include file pulled into Application.cfm (e.g., server names - the
 include file is auto-generated by the build system).
 2. 'initialized-once' entities are lazily created and stored in shared
 scope (we use server scope since we don't share with other apps, most
 folks would use application scope).

 The second class of entities includes certain database-derived values,
 e.g., localized application strings. These are created by running a
 'bootstrap' script - none of the applications of the server will run
 unless the system is bootstrapped and we can re-run the bootstrap if
 needed to refresh the server scope entities.

 The bootstrap script can only be executed within the firewall - the
 equivalent of having an application admin option to refresh the values
 stored in application scope.

 We don't rely on simple cached queries because:
 1. the 'constant' entities come from a file that is auto-generated by a
 build script: it would be more effort to have the build system populate
 the database and then have the applications pull those back from the
 database - it would also require that each server environment used a
 different database whereas we often share databases between distinct
 testing / development systems.
 2. the more complex entities are calculated from a variety of sources -
 sometimes including calling web services - so the calculated entity
 needs to cached, rather than the raw data underneath.

 For many, simpler applications, Matt's solution is a very good one -
 appropriate caching means the database is hit as rarely as you want it
 to be.

 Sean A Corfield -- http://www.corfield.org/blog/

 If you're not annoying somebody, you're not really alive.
 -- Margaret Atwood

 
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq

Your ad could be here. Monies from ads go to support these lists and provide more 
resources for the community. 
http://www.fusionauthority.com/ads.cfm

Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: Fairly Difficult SQL problem

[Dina Hess]

Just in case your database doesn't support some of that cool stuff Jochem
uses, try this. It works in SQL 2000:

SELECTs.id, s.sponsor, d.date, MIN(datediff(day, dp.date, d.date)) as
daysSince
FROM  drive d left outer join sponsor s on d.sponsor_id = s.id left
outer JOIN drive dp ON d.sponsor_id = dp.sponsor_id AND d.date  dp.date
WHERE MONTH(d.date) = 6
GROUP BY  s.id, s.sponsor, d.date

Sponsor 4 reports a NULL value for daysSince because there is no previous
drive date.

~Dina

- Original Message -
From: Jochem van Dieten [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Thursday, July 03, 2003 10:53 AM
Subject: Re: Fairly Difficult SQL problem


 Ian Skinner wrote:
 
 I have tables something like the following data structures.
 
 Sponsor
 ID Sponsor ...
 1 Foo
 2 Bar
 3 Clyde
 4 New Sponsor
 
 Drives
 ID SponsorID Date ...

 Date is a reserved word.


 1 1 4/15/03
 2 2 4/18/03
 3 3 4/24/03
 4 2 5/9/03
 5 1 5/16/03
 6 3 6/3/03
 7 2 6/11/03
 8 1 6/15/03
 9 4 6/18/03
 
 What I need to retreive is all the drives for a given month (June for
 example) plus the number of days it has been since any previous drive
for
 a given Sponsor.

 SELECTs.id, d.sponsor, d.date, MIN(d.date - dp.date) as daysSince
 FROM  sponsor s, drives d INNER JOIN drives pd ON (d.sponsorid =
 pd.sponsorid AND d.date  dp.date)
 WHERE EXTRACT (MONTH FROM d.date) = #month#
AND s.id = d.sponsorid
 GROUP BY  s.id, d.sponsor, d.date

 Jochem



 
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq

Signup for the Fusion Authority news alert and keep up with the latest news in 
ColdFusion and related topics. 
http://www.fusionauthority.com/signup.cfm

Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: Money Datatype Problem

[Dina Hess]

What am I doing wrong? I thought CF was typeless.

Sorry for the late response but...none of your INSERT parameters worked
because you were wrapping numbers with single quotes...another good reason
to use CFQUERYPARAM. :)



~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq

Signup for the Fusion Authority news alert and keep up with the latest news in 
ColdFusion and related topics. 
http://www.fusionauthority.com/signup.cfm

Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

DBAs/SQL Server experts?

[Dina Hess]

Hi all,

If a datasource verifies OK in CF Admin on a shared host, what would cause
the connection to fail?

I detached a SQL Server database and FTP'd a copy to the host (couldn't get
DTS to work...but that's another problem). He created an ODBC system data
source on his Windows box then set up the data source in CF Admin. The data
source verifies fine on his system, but my application, which references the
same data source in CFQUERY, is not getting a connection.

I'd been using MSDE and Query Analyzer so I could practice running queries
against SQL Server data sources, but I'm completely new to the full blown
SQL Server Enterprise Manager. I've probably done something brainless. But I
don't know what it could be. :/

Any ideas?

~Dina





~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq

Signup for the Fusion Authority news alert and keep up with the latest news in 
ColdFusion and related topics. 
http://www.fusionauthority.com/signup.cfm

Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: DBAs/SQL Server experts?

[Dina Hess]

Actually, I'm running CFMX on my development box and the application has
been ported to a shared server running CF 5. Do you think that is related to
this problem?

- Original Message -
From: Bryan Stevenson [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Friday, June 13, 2003 10:37 AM
Subject: Re: DBAs/SQL Server experts?


 Is this in MX??

 Bryan Stevenson B.Comm.
 VP  Director of E-Commerce Development
 Electric Edge Systems Group Inc.
 t. 250.920.8830
 e. [EMAIL PROTECTED]

 -
 Macromedia Associate Partner
 www.macromedia.com
 -
 Vancouver Island ColdFusion Users Group
 Founder  Director
 www.cfug-vancouverisland.com
 - Original Message -
 From: Dina Hess [EMAIL PROTECTED]
 To: CF-Talk [EMAIL PROTECTED]
 Sent: Friday, June 13, 2003 8:22 AM
 Subject: DBAs/SQL Server experts?


  Hi all,
 
  If a datasource verifies OK in CF Admin on a shared host, what would
cause
  the connection to fail?
 
  I detached a SQL Server database and FTP'd a copy to the host (couldn't
 get
  DTS to work...but that's another problem). He created an ODBC system
data
  source on his Windows box then set up the data source in CF Admin. The
 data
  source verifies fine on his system, but my application, which references
 the
  same data source in CFQUERY, is not getting a connection.
 
  I'd been using MSDE and Query Analyzer so I could practice running
queries
  against SQL Server data sources, but I'm completely new to the full
blown
  SQL Server Enterprise Manager. I've probably done something brainless.
But
 I
  don't know what it could be. :/
 
  Any ideas?
 
  ~Dina
 
 
 
 
 
 
 
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq

This list and all House of Fusion resources hosted by CFHosting.com. The place for 
dependable ColdFusion Hosting.
http://www.cfhosting.com

Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: DBAs/SQL Server experts?

[Dina Hess]

 You may have to specify your username/pwd for the datasource. I have seen
 connection issues when this is the case.

My host did specify a username/password in CF Admin for the datasource. And
the data source verifies fine on his server. That's why I don't understand
why my application isn't getting a connection. All I really need in the app
is the data source name, right? And I have that set up in the
Application.cfm file in the request scope.



~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq

Signup for the Fusion Authority news alert and keep up with the latest news in 
ColdFusion and related topics. 
http://www.fusionauthority.com/signup.cfm

Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: DBAs/SQL Server experts?

[Dina Hess]

 have you re-attached the database and restored it?  When you restore, you
 will need to reset all of your connection info for that database (unless
you
 are using sa as a login)


Well, I thought it might have something to do with the detach/reattach
thing, but if the database hadn't been reattached correctly, it wouldn't
verify in CF Admin, would it?



~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq

Your ad could be here. Monies from ads go to support these lists and provide more 
resources for the community. 
http://www.fusionauthority.com/ads.cfm

Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: DBAs/SQL Server experts?

[Dina Hess]

 try and do a solid connection and see if it worksi.e.

 cfquery name=queryname
   datasource=datasource
   username=username
 password=password

 select * from table

 /cfquery

Tried that already, but I'll give it another go. Thanks.



~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq

Your ad could be here. Monies from ads go to support these lists and provide more 
resources for the community. 
http://www.fusionauthority.com/ads.cfm

Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: DBAs/SQL Server experts?

[Dina Hess]

 Check your application.cfm. Do you have all the right log in info for
 SQL?

Dan, I have the data source set up in Application.cfm in the request scope.
That's all I need, isn't it? (When we started having problems with this, I
did specify the exact username/password my host used in his CF Admin data
source configuration, even changed all my CFQUERY tags to include those
attributes. But it still didn't work, so I backed off on that.)

On my MX development box, I have this SQL Server data source set up as a
trusted connection using the ODBC Socket. But that wouldn't affect the
connection on the shared host, would it?




~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq

This list and all House of Fusion resources hosted by CFHosting.com. The place for 
dependable ColdFusion Hosting.
http://www.cfhosting.com

Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: DBAs/SQL Server experts?

[Dina Hess]

 MX can act quite strange with DSNs


 How I get around it screwing things up:

 -setup System DSN through Windows ODBC (tell it to use Windows
 Authentication)
 -setup DSN in CF Admin (this is where you put the username and pass)

 If I don't do it in that orderKABOOM!!

Bryan, the DSN connection to the application on my MX dev box works fine.
The problem is the DSN connection to the application on the shared host is
not working...and they use CF 5.



~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq

Host with the leader in ColdFusion hosting. 
Voted #1 ColdFusion host by CF Developers. 
Offering shared and dedicated hosting options. 
www.cfxhosting.com/default.cfm?redirect=10481

Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: DBAs/SQL Server experts?

[Dina Hess]

 Are you getting ODBC errors of table does not exist etc.

Yes.



~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq

Your ad could be here. Monies from ads go to support these lists and provide more 
resources for the community. 
http://www.fusionauthority.com/ads.cfm

Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: DBAs/SQL Server experts?

[Dina Hess]

To everyone who responded, thank you so much for your support during this
Friday 13th headache. For some reason, it's now working (??), so I guess my
shared host found the solution.

To anyone who may be interested, my website is now up and running at
http://www.dinahess.com. All of the Best of Talk summaries are available
there and will be kept current if I can get a working DTS connection to my
shared host's server. There are also a few tips...and a SQL tutorial.

You guys are the best! Thank you for your help...again. :)

~Dina



~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq

This list and all House of Fusion resources hosted by CFHosting.com. The place for 
dependable ColdFusion Hosting.
http://www.cfhosting.com

Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: Checkbox woes

[Dina Hess]

I think you're just tiredovercomplicating it. Note that all I'm doing is
setting the value of the checkbox to 1. You've already got CFPARAMs to
default the values to 0, and you don't need to move those form values to the
session scope that I can see. Try this:

FORM PAGE:
form action=SearchSaleList.cfm method=get

table width=100% border=0 cellspacing=0 cellpadding=0
tr
  td width=20input name=SALEITEM1 type=checkbox  value=1/td
  tdBooks/td
  td width=20input name=SALEITEM2 type=checkbox value=1/td
  tdMusic Items/td
/tr
/table

input type =submit value=submit

/form

ACTION PAGE:

CFPARAM NAME=form.SALEITEM1 DEFAULT=0
CFPARAM NAME=form.SALEITEM2 DEFAULT=0

cfquery name=getsales  datasource=#dsnname#
SELECT sm.sid as rf1,sm.SALEADDR as rf2, sm.SALEcity as rf4,sM.saletype as
rf3, s.saledate as rf5, s.saletimefrom+' - '+saletimeto as rf6
FROM sale_master sm,  sale s
WHERE sm.sid = s.sid
AND s.saleDisplay = 'Yes'
AND sm.SALEreleased = 1
cfif form.saleitem1 AND sm.saleitem1 = true /cfif
cfif form.saleitem2 AND sm.SALEITEM2 = true /cfif
/cfquery

HTH,
~Dina



~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq

Get the mailserver that powers this list at 
http://www.coolfusion.com

Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

DTS to update SQL Server database?

[Dina Hess]

Hi all,

Can someone explain? Is it better to use DTS, replication, or FTP to update
SQL Server 2K database files on a shared host? (The database is small; there
will only be about 50 new records each week.)

TIA,
~Dina



~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq

Get the mailserver that powers this list at 
http://www.coolfusion.com

Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: DTS to update SQL Server database?

[Dina Hess]

In my perfect world though, in a batch situation, with a small update,
the publishing side would fire a DTS that connected to the remote side
and updated the data. This one takes very little time to setup, and
doesn't place the constraints that replication does on the schema. It
also can be done manually or automated, so it's flexible.

Wow, well said; thank you for taking the time to help me understand this. I
always thought DTS was used to migrate databases, or transfer data between
disparate data sources. I didn't realize it was useful for making small
batch updates.

I had already run into the schema problem you mentioned with replication
since my primary keys are identities.

But why don't you recommend FTPing the updated database files? Is that some
kind of SQL Server no-no? If so, why?



~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq

Your ad could be here. Monies from ads go to support these lists and provide more 
resources for the community. 
http://www.fusionauthority.com/ads.cfm

Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: DTS to update SQL Server database?

[Dina Hess]

 Other than importing a flat file, the other two have downsides as
 well. Like the phantom user issue...

Phantom user issue?



~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq

Your ad could be here. Monies from ads go to support these lists and provide more 
resources for the community. 
http://www.fusionauthority.com/ads.cfm

Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Checking radio buttons

[Dina Hess]

Sorry for the late response to this thread, but I'd like to note that the problem 
here, as I see it, is an incorrect use of CFINPUT's CHECKED attribute. CFINPUT will 
accept a boolean value for CHECKED. So if your code is revised as follows, it will 
work:

cfif isDefined(form.save.x)
cfif FORM.status is 1
cfset isChecked = true
cfelse
cfset isChecked = false
/cfif
cfinput type=radio name=Status value=1 onfocus=this.blur(); required=yes 
message=Status is *required*. checked = #isChecked# font 
class=dialoghdOpen/font
/cfif

Also, the CFFORM tag (in CFMX) has an optional PRESERVEDATA attribute. If you set it 
to yes, you don't have to deal with the CHECKED attribute at all. CF checks or 
unchecks a radio or checkbox based on a matching VALUE, rather than the value of the 
CHECKED attribute.


~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq

Host with the leader in ColdFusion hosting. 
Voted #1 ColdFusion host by CF Developers. 
Offering shared and dedicated hosting options. 
www.cfxhosting.com/default.cfm?redirect=10481

Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: Count(*) Query

[Dina Hess]

You could do something like this:

SELECT TOP 1 (select count(qty) from items where qty = 1) AS num_1s, (select
count(qty) from items where qty = 2) AS num_2s
FROM items
GROUP BY qty

I'm sure there's a more efficient way to do this, but this should work.


- Original Message -
From: Hawkes, Keith A CIV [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Friday, April 04, 2003 6:17 AM
Subject: Count(*) Query


 I have a table that has one field that holds numbers 1 thru 9.  Can I run
a
 single query that will give me the count(*) of 1's, count(*) of 2's, etc.
or
 do I have to run a separate query for each?

 Thanks to all,

 Keith
 
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Get the mailserver that powers this list at http://www.coolfusion.com

Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: 'Like' statement for Merant?

[Dina Hess]

 WHERE message IS LIKE '%#form.keywords#%'

IS might be the problem...change to:

WHERE message LIKE '%#form.keyword#%'


~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm

Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: Nested Query Loop Bug/Issue

[Dina Hess]

Tony,

That's a known problem. To work around it, just set a local variable in the
outer loop equal to the value in Query1 you want to pass to Query2 and
access *that* variable inside of your inner loop.

~Dina

- Original Message -
From: Tony Schreiber [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Thursday, March 27, 2003 11:04 AM
Subject: Nested Query Loop Bug/Issue


 I don't know if any of you have run into this before or if it's even been
 mentioned (couldn't find it at mm technotes either), but I've run into a
 situation where the values of an outer loop referred to in an inner loop
 are not what you expect. I was burned on this recently and remember
 vaguely it happening to me before. I've written some demonstration code to
 illustrate:

 http://www.simplemessageboard.com/loopbug.cfm

 The solution as shown in the code is to set a local variable BEFORE you
 begin the inner loop to hold the value of the outer loop you want to refer
 to within the inner loop.

 Anyone had this happen? Or is this expected behavior that I can't see?

 BTW, this is CF5, I don't know about MX.


 Tony Schreiber, Senior Partner  Man and Machine, Limited
 mailto:[EMAIL PROTECTED]   http://www.technocraft.com

 http://www.is300.net The Enthusiast's Home of the Lexus IS300 since 1999
 http://www.simplemessageboard.comFree Forum Software for Cold Fusion

 
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Signup for the Fusion Authority news alert and keep up with the latest news in 
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm

Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: CFQUERYPARAM with lists

[Dina Hess]

Should be fine if you add parentheses around the CFQUERYPARAM tag that
contains the list, like this:

AND table2.field1 IN (cfqueryparam cfsqltype=CF_SQL_VARCHAR
value=#attributes.typelist# list=Yes separator=, /)

It appears that the IN syntax is the only thing amiss.

~Dina



- Original Message -
From: Barney Boisvert [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Tuesday, March 18, 2003 12:44 PM
Subject: CFQUERYPARAM with lists


 I'm having all kinds of trouble with using CFQUERYPARAM on a list of
VARCHAR
 values.  I found a couple threads in the archives, and I can't figure what
 I'm doing wrong.  Here's the query:

 SELECT table1.field1
table2.field1, table2.field2, table2.field3
 FROM table1
INNER JOIN table2 ON table1.field1 = table2.field4
 WHERE table1.field1 = cfqueryparam cfsqltype=CF_SQL_INTEGER
 value=#attributes.id# /
AND table2.field1 IN cfqueryparam cfsqltype=CF_SQL_VARCHAR
 value=#attributes.typelist# list=Yes separator=, /
 ORDER BY table2.field1

 #attributes.id# a number and works fine.  #attributes.typelist# is a list
of
 strings (not my decision) that will be of length one of greater.  I've
tried
 with and without the SEPARATOR attribute, and I've also tried adding dummy
 list elements, to ensure that there is more than one value.

 The error i'm getting is this (when attributes.typelist is set to
'Images',
 without the quotes):

 Syntax error or access violation: You have an error in your SQL syntax
near
 ''Images' ORDER BY table2.field1' at line 6

 Near as I can tell, CF is not bothering to adding the parentheses around
the
 list of values, just the single quotes, so it's screwing up the SQL
parser,
 because it sees 'IN' and then expects a parentheis.

 CFMX w/ U2, Apache 2.0, MySQL 3.23, RedHat 8.

 MTIA,
 barneyb

 ---
 Barney Boisvert, Senior Development Engineer
 AudienceCentral (formerly PIER System, Inc.)
 [EMAIL PROTECTED]
 voice : 360.671.8708 x12
 fax   : 360.647.5351

 www.audiencecentral.com
 ---
 Outgoing mail is certified Virus Free.
 Checked by AVG anti-virus system (http://www.grisoft.com).
 Version: 6.0.462 / Virus Database: 261 - Release Date: 3/13/2003

 
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
This list and all House of Fusion resources hosted by CFHosting.com. The place for 
dependable ColdFusion Hosting.

Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: subquery and group by a non selected field

[Dina Hess]

I'm guessing you want this?

SELECT *
FROM courses A, classes B,sessions C
WHERE A.course_id = B.course_id
AND C.session_id = B.session_id
AND endDate BETWEEN '#url.minDate#' AND '#url.maxDate#'


- Original Message -
From: [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Thursday, March 13, 2003 3:20 PM
Subject: SQL: subquery and group by a non selected field


 I am trying to return a list of ids in a sub query but have them
 grouped by another column.

 View the subquery below. I am attempting to GROUP BY class_id
 because I need 1 session_id (the one with the MAX(endDate)) from
 each group of class_ids.

 SELECT *
 ~FROM courses A, classes B,sessions C
 ~WHERE A.course_id = B.course_id AND
 ~~  C.class_id = B.class_id AND
 ~C.session_id IN (SELECT session_id
 FROM sessions
 GROUP BY class_id
 HAVING MAX(endDate) BETWEEN '#url.minDate#' AND '#url.maxDate#')


 Any ideas on how to get this to work?

 DM

 ___
 Sent by ePrompter, the premier email notification software.
 Free download at http://www.ePrompter.com.

 
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Your ad could be here. Monies from ads go to support these lists and provide more 
resources for the community. http://www.fusionauthority.com/ads.cfm

Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: input tag

[Dina Hess]

Flash Guy,

If you just want links to a login form and a logout process, you don't need
the FORM tag. Just use anchor tags to create links to the appropriate
templates. You could display just the logon link until the user logs in,
then after they've logged in, display just the logout link:

cfif not isDefined(session.username)
  a href=login_form.cfmLogin/a
cfelse
  a href=logout_process.cfmLogout/a
/cfif

If you have your heart set on buttons, just replace the anchor tags with
something like this:

input type=button value=Login onclick=javascript: location =
'login_form.cfm';

~Dina

- Original Message -
From: FlashGuy [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Wednesday, March 12, 2003 10:35 AM
Subject: input tag


 Hi again,

 I have a form with two input tags. The Login button executes the file
assigned to the form tag.
 How can I have the Logout button run a template file rather then the
form?

 form action=index.cfm method=post

 input type=submit value=Login style=background:6C993F
class=loginbutton !--- THIS EXECUTES THE ABOVE form ---


 input type=submit value=Logout style=background:6C993F
class=loginbutton!--- I WANT THIS TO EXECUTE THE FILE logout.cfm ---
 /form


 ---
 Colonel Nathan R. Jessop
 Commanding Officer
 Marine Ground Forces
 Guatanamo Bay, Cuba
 ---



 
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Your ad could be here. Monies from ads go to support these lists and provide more 
resources for the community. http://www.fusionauthority.com/ads.cfm

Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: Special Characters driving me nuts

[Dina Hess]

Joshua,

All will be well if you send your form data directly to the DB in a
CFQUERYPARAM tag ***without applying any functions***. That's especially
important if you're using MX because there's an apostrophe bug that only
occurs when Trim() is applied to parameter values inserted into or updating
the DB. (Although I'm not convinced that the use of Trim() is even necessary
for that.) You can then apply the HTMLEditFormat and Trim functions to
display the data correctly.

~Dina

- Original Message -
From: Joshua Miller [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Monday, March 10, 2003 1:26 PM
Subject: Special Characters driving me nuts


 Greetings,

 I've been fighting with user-input and special characters for days now,
 I'm starting to get really frustrated.

 Here's the scenario:

 1. User enters data into a textarea field. This will usually contain
 several special characters (?/{}[]|\[EMAIL PROTECTED] mailto:[EMAIL PROTECTED]*,
 *,?/{}[]|\-_+)(^)
 2. I use URLEncodedFormat before sticking the data in my database table,
 so I'm storing encoded data
 3. I use URLDecode when retrieving the data into an HTML page to show
 the data entered.

 Here's the problem:

 Data that begins with a special character doesn't display. Data that is
 surrounded by  marks doesn't display
 For example:

 This line won't display in HTML
 This line $ will display in HTML
 This line will not display in HTML
 $This line will not display in HTML

 If I use HTMLEditFormat() I still can't see the items surrounded by 
 marks and it also displays the URLEncodedFormat information so space =
 %20 on screen in the HTML page.

 How do I overcome this issue?

 Thanks,

 Joshua Miller
 Head Programmer / IT Manager
 Garrison Enterprises Inc.
 www.garrisonenterprises.net http://www.garrisonenterprises.net/
 [EMAIL PROTECTED]
 (704) 569-9044 ext. 254

 
 *
 Any views expressed in this message are those of the individual sender,
 except where the sender states them to be the views of
 Garrison Enterprises Inc.

 This e-mail is intended only for the individual or entity to which it is
 addressed and contains information that is private and confidential. If
 you are not the intended recipient you are hereby notified that any
 dissemination, distribution or copying is strictly prohibited. If you
 have received this e-mail in error please delete it immediately and
 advise us by return e-mail to
 mailto:[EMAIL PROTECTED]
 [EMAIL PROTECTED]
 
 *


 
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Signup for the Fusion Authority news alert and keep up with the latest news in 
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm

Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: Special Characters driving me nuts

[Dina Hess]

Oops...didn't see this before I posted.

~Dina


- Original Message -
From: Joshua Miller [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Monday, March 10, 2003 3:52 PM
Subject: RE: Special Characters driving me nuts


 I HAVE to use URLEncodedFormat to store the data. Unfortunately what I
 have to do is allow a preview of the user's submission, but there are
 around 50 fields I have to store that typically go into about 6
 different tables, so I'm storing everything as a name=value;name=value;
 string in a table, displaying the preview and then cleaning out the
 preview table upon submission. So, I'm creating dynamic variables before
 the insert and I have to use URLEncodedFormat so that the users can
 enter the characters I use as delimiters.

 Basically, I use name=value;name=value; so if the user enters an = or ;
 character it breaks my string.

 This is how I'm doing it right now:

 1. URLEncodedFormat() the data into the database
 2. Grab that string from the database on the preview page
 3. Loop over the string and cut into name/value pairs for another list
 loop
 4. On the second list loop URLDecode the value so it's correct as it was
 entered by the user
 5. Display using HTMLEditFormat() on the document

 This should work just fine and be exactly like storing the data as-is in
 the database. The problem is if the value of a variable on the preview
 page begins with a special character it won't display. Everything else
 around it displays fine and even if there are quotes in the middle they
 work, it's just the first character. If the first character is a special
 character it won't display that entire value.

 Any other ideas?

 Joshua Miller
 Head Programmer / IT Manager
 Garrison Enterprises Inc.
 www.garrisonenterprises.net
 [EMAIL PROTECTED]
 (704) 569-9044 ext. 254

 
 *
 Any views expressed in this message are those of the individual sender,
 except where the sender states them to be the views of
 Garrison Enterprises Inc.

 This e-mail is intended only for the individual or entity to which it is
 addressed and contains information that is private and confidential. If
 you are not the intended recipient you are hereby notified that any
 dissemination, distribution or copying is strictly prohibited. If you
 have received this e-mail in error please delete it immediately and
 advise us by return e-mail to [EMAIL PROTECTED]
 
 *


 -Original Message-
 From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
 Sent: Monday, March 10, 2003 2:41 PM
 To: CF-Talk
 Subject: Re: Special Characters driving me nuts


 Don't use URLEncodedFormat() when inserting the data.  Place it into the
 database as-is.

 Use HTMLEditFormat() to display the data.

 You'll have to update rows that you URLEncodedFormat() back to their
 original format.

 - Original Message -
 From: Joshua Miller [EMAIL PROTECTED]
 Date: Monday, March 10, 2003 12:26 pm
 Subject: Special Characters driving me nuts

  Greetings,
 
  I've been fighting with user-input and special characters for days
  now,I'm starting to get really frustrated.
 
  Here's the scenario:
 
  1. User enters data into a textarea field. This will usually contain
  several special characters (?/{}[]|\[EMAIL PROTECTED] ') mailto:[EMAIL 
  PROTECTED]*,
  *,?/{}[]|\-_+)(^)
  2. I use URLEncodedFormat before sticking the data in my database
  table,so I'm storing encoded data
  3. I use URLDecode when retrieving the data into an HTML page to show
  the data entered.
 
  Here's the problem:
 
  Data that begins with a special character doesn't display. Data
  that is
  surrounded by  marks doesn't display
  For example:
 
  This line won't display in HTML
  This line $ will display in HTML
  This line will not display in HTML
  $This line will not display in HTML
 
  If I use HTMLEditFormat() I still can't see the items surrounded
  by 
  marks and it also displays the URLEncodedFormat information so
  space =
  %20 on screen in the HTML page.
 
  How do I overcome this issue?
 
  Thanks,
 
  Joshua Miller
  Head Programmer / IT Manager
  Garrison Enterprises Inc.
  www.garrisonenterprises.net 
  target=lhttp://www.garrisonenterprises.net/
  [EMAIL PROTECTED]
  (704) 569-9044 ext. 254
 
  **
  **
  *
  Any views expressed in this message are those of the individual
  sender,except where the sender states them to be the views of
  Garrison Enterprises Inc.
 
  This e-mail is intended only for the individual or entity to which
  it is
  addressed and contains information that is private and
  confidential. If
  you are not the intended recipient you are hereby notified that any
  dissemination, distribution or copying is strictly prohibited. If
  you
  have received this e-mail in error please delete it 

Re: cfloop/index question

[Dina Hess]

  The reason each checkbox has a unique name (service + number) is so the
 form
  can autocheck a particular service. Let's say a customer is on the
  Fluorescent Bulb Disposal page and hits the Send Me More Info
button.
  The form comes up with Fluorescent Bulb Disposal (service 370) already
  checked. They can then look over the list and see if they want
information
  on anything else while they're at it.

  I could use the service value as the checkbox value, but then I'd have
to
  build a lookup table with 80 services listed to see what names to put on
 the
  response email that went out. The client that owns the site doesn't have
  code numbers or anything for the services, I assigned those. Client only
  goes by the Service Names


If that's the only reason you're giving each checkbox a unique name, you may
want to consider using a CFIF within your INPUT tag to check the appropriate
checkboxes based on the value of a URL parameter or, better yet, a hidden
form field, like this:

input type=checkbox name=service value=Fluorescent Bulb Disposal
cfif form.source is =Fluorescent Bulb Disposalchecked/cfif

That way, you eliminate the need for a cryptic service code that's not truly
tied to the service name. If you ever have a need to check more than one
checkbox based on the source page, you can just pass a list in the hidden
form field and change your CFIF statement to: cfif
ListFindNoCase(form.source,Fluorescent Bulb Disposal)

This solution will also scale well:

cfoutput
cfloop list=#form.service# index=i
   #i#br
/cfloop
/cfoutput

~Dina

~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Your ad could be here. Monies from ads go to support these lists and provide more 
resources for the community. http://www.fusionauthority.com/ads.cfm

Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: cfloop/index question

[Dina Hess]

Les,

You may want to consider a different approach.

For example, you could give all of the service checkboxes one name, as in
NAME=Service, and specify the service code in the value, as in
VALUE=100.

The value of each checked checkbox will be appended to FORM.service so, on
the action page, you could just do a list loop over that variable to get at
the values, like this:

The following service have been selected:
cfloop list=#form.service# index=service
  #service#br
/cfloop

Seems simpler...which is always my goal. :)

~Dina



- Original Message -
From: Les Mizzell [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Wednesday, March 05, 2003 11:13 AM
Subject: cfloop/index question


 I've got a form with a large list of services that a client can click to
get
 more info. Depending on where they come from, the checkboxes will
auto-check
 a particular service they're interested in, and then they can check a few
 more if they wish information on other services.

 JAVASCRIPT CHECKBOX FUNCTION
 function makeCheckboxes(){
 STUFF.servicecfoutput#rq_SerID#/cfoutput.checked=true
 }

 On the form itself, each checkbox for each service is name like:

 service100
 service200
 service300

 This works together with the Javascript to check the appropriate box based
 on a passed URL variable. This part works great.

 I'm having a bit of trouble on the processing side to get a CFLOOP
properly
 set up to go through all the checkboxes and list the values of the ones
 checked.

 Had this working in ASP:
 I would like information on the following products.  vbCrlf
 REM ADD THE CHECKBOXES TO THE CDO EMAIL BODY
 For i=10 to 720
 if Request.Form(service  i)   THEN
 vBody = vBody  cStr(Request(service  i))  vbCrLf
 END IF
 next

 ...but having some trouble translating this over to Cold Fusion...

 Advice please...

 Thanks

 
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm

Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: Writing CF to a string

[Dina Hess]

Use the HTMLEditFormat() function, like this:

cffile
   action = write
   file = fullfilepath/myfile.cfm
   output = How can I prevent the code in my string from being
processed?br#HTMLEditFormat('cfinclude template=myfile.cfm')#brThis
is a test.

~Dina


- Original Message -
From: Andy Ewings [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Wednesday, February 26, 2003 8:50 AM
Subject: RE: Writing CF to a string


 thanks Rich

 -Original Message-
 From: Rich Wild [mailto:[EMAIL PROTECTED]
 Sent: 26 February 2003 14:37
 To: CF-Talk
 Subject: RE: Writing CF to a string


 sorry Andy, I'm not thinking straight, which is a requisite when answering
 questions.

 you don't even need that chr() bit.

 cfset myCFStr = 'cfinclude template=myTemplate.cfm'

 will work fine and doesn't get evaluated.

  -Original Message-
  From: Andy Ewings [mailto:[EMAIL PROTECTED]
  Sent: 26 February 2003 14:36
  To: CF-Talk
  Subject: RE: Writing CF to a string
 
 
  Rich
 
  But won't that still be evaluated?
 
  Also aint it chr? - and I think   is chr(60) (maybe wrong).
 
  -Original Message-
  From: Rich Wild [mailto:[EMAIL PROTECTED]
  Sent: 26 February 2003 13:52
  To: CF-Talk
  Subject: RE: Writing CF to a string
 
 
  cfset myCFstr = '#char(139)#cfinclude
  template=mytemplate.cfm#char(155)#'
 
   -Original Message-
   From: Andy Ewings [mailto:[EMAIL PROTECTED]
   Sent: 26 February 2003 13:53
   To: CF-Talk
   Subject: Writing CF to a string
  
  
   Does anyone know how to create a string containing CF code
   without it being
   evaluated?  i.e. I want to create a file containing the text
   cfinclude
   template-mytemplate.cfm
  
   I have tried this by creating a straing containing cfinclude
   template-mytemplate.cfm using the DE function but when I
   try to look at
   the contents of this string all I see is double quotation marks
  
  
  
  
 
 

 
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
This list and all House of Fusion resources hosted by CFHosting.com. The place for 
dependable ColdFusion Hosting.

Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: SQL Multiple Reference Tables Question - Thanks

[Dina Hess]

James,

I'm not too sure about that LEFT join.

I mean, if you were to add a row to tblThings that contained a NULL value
for any or all of the foreign keys, (if that's possible with your schema),
the LEFT join would return the row with the NULL(s) in addition to all the
rows that have matching key values.

But maybe that's what you want.

Anyway, I think it's worth noting that the INNER (or NATURAL) join will
*also* allow you to filter out rows based on conditions in your WHERE
clause, like ColorDescription = 'Red.'

~Dina

- Original Message -
From: James Brown [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Friday, February 28, 2003 4:30 PM
Subject: Re: SQL Multiple Reference Tables Question - Thanks


 This is the answer.  Many thanks.

 James Brown

 - Original Message -
 From: Cantrell, Adam [EMAIL PROTECTED]
 To: CF-Talk [EMAIL PROTECTED]
 Sent: Friday, February 28, 2003 4:41 PM
 Subject: RE: SQL Multiple Reference Tables Question


  This may work, you'll want to look into using cfqueryparam around your
  passed variables to avoid SQL injection attacks. You will also want to
 play
  with the WHERE contraint to fit your needs - you may want records that
 match
  all criteria (AND) or you may want records that match any of them (OR)
 
  Use LEFT OUTER JOIN, if you want to pull records that don't necessarily
 have
  a reference.
 
 
  CFQUERY name=thinglist  datasource=mydata
  SELECT
 tblThing.ThingKey,
 tblThing.ThingName,
 tblColor.ColorDescription,
 tblCategory.CatDescription,
 tblMaker.MakerName
  FROM
 tblThing LEFT JOIN tblColor ON tblThing.ColorKey = tblColor.ColorKey
 LEFT JOIN tblCategory ON tblThing.CatKey = tblCategory.CatKey
 LEFT JOIN tblMaker ON tblThing.MakerKey = tblMaker.MakerKey
  WHERE
  tblColor.ColorKey = #url.colorKey#
  AND tblCategory.CatKey = #url.catKey#
  AND tblMaker.MakerKey = #url.MakerKey#
  /CFQUERY
 
 
  Adam.
 
 
 
   -Original Message-
   From: James Brown [mailto:[EMAIL PROTECTED]
   Sent: Friday, February 28, 2003 3:28 PM
   To: CF-Talk
   Subject: SQL Multiple Reference Tables Question
  
  
   This is probably basic, since I am new at this, but I want to
   know what is
   best.  I have simplified the tables for illustration.
  
   I have one main table
  
   tblTHING
   ThingKey NameColorKeyCatKey
   MakerKey
   --   ---   --
  ---
   ---  -
   001  WinterSap  1
2
   6
   002  HorsePlay   1
4
   5
   003  HouseBarn 3
   1
   4
  
   and three reference tables.   The purpose of each is to
   supply a lookup
   value to the tblThing, the main table.  The foreign keys in
   the reference
   tables have the same field name as the corresponding fields
   in the main
   table.
  
   tblCOLOR
   ColorKey   ColorDescription
   -- --
 1   Blue
 2   Yellow
 3   Purple
 4   Red
  
   tblCATEGORY
   CatKey CatDescription
     -
  1   Animal
  2   Activity
  3   Human
  4   Dwelling
  5   Season
  6   Biological Substance
  
  
   tblMAKER
   MakerKey   MakerName
    
 1   Mary
 2   Joe
 3   Willy
 4   Mike
 5   Roman
 6   Jameson
 7   Amanda
  
   I want a query that produces the following result:
  
   ThingKey Name   ColorCategory
   Maker
   --   ---   --
  ---
   ---  -
   001  WinterSapBlue
 Activity
   Jameson
   002  HorsePlay Blue
 Dwelling
   Roman
   003  HouseBarn   Purple  Animal
   Mike
  
   The values in the Color, Category and Maker columns have, of
   course, been
   supplied from the reference tables.
  
   Which I CAN do with the following Query:
  
   CFQUERY name=thinglist  datasource=mydata
   SELECT
  tblThing.ThingKey,
  tblThing.ThingName,
  tblColor.ColorDescription,
  tblCategory.CatDescription,
  tblMaker.MakerName
  
   FROM
  tblThing,
  tblColor,
  tblCategory,
  tblMaker
  
   WHERE
   tblColor.ColorKey = tblThing.ColorKey
   AND tblCategory.CatKey = tblThing.CatKey
   AND tblMaker.MakerKey = tblThing.MakerKey
  
   /CFQUERY
  
   

Re: Correct Syntax for this piece of SQL ??

[Dina Hess]

Ian,

Replace WHERE 0 = 1 in your current query with either WHERE 0 = 0 or WHERE 1
= 1.

Why? This is just a dummy clause that's used in a dynamic SQL statement to
let's you move past the WHERE and on to the dynamic AND/OR statements that
are based on your conditions. But this dummy WHERE clause must be true, or
processing will stop right there. Obviously, WHERE 0 = 1 is *not* true. Fix
that and you should be OK.

~Dina

- Original Message -
From: Ian Vaughan [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Wednesday, February 26, 2003 9:17 AM
Subject: Re: Correct Syntax for this piece of SQL ??


 Pascal

 I removed the cached feature so the query looks like what I have below.
 However If I search for an entry in the orgname field it displays the
result
 as it should.

 If I try to search in the other two fields it returns no results ???

 CFQUERY datasource=liv8 name=funding
 SELECT *
 FROM funding
 WHERE 0=1
 cfif Len(Trim(form.orgname))
   OR UPPER(orgname) LIKE UPPER(cfqueryparam cfsqltype=CF_SQL_VARCHAR
 value=%#form.orgname#%)
 /cfif
 cfif Len(Trim(form.funding))
   OR UPPER(funding) LIKE UPPER(cfqueryparam cfsqltype=CF_SQL_VARCHAR
 value=%#form.funding#%)
 /cfif
 cfif Len(Trim(form.commapproval))
   OR UPPER(commapproval) LIKE UPPER(cfqueryparam
cfsqltype=CF_SQL_VARCHAR
 value=%#form.commapproval#%)
 /cfif
 /CFQUERY




 - Original Message -
 From: Pascal Peters [EMAIL PROTECTED]
 To: CF-Talk [EMAIL PROTECTED]
 Sent: Wednesday, February 26, 2003 2:49 PM
 Subject: RE: Correct Syntax for this piece of SQL ??


  You cannot use cfqueryparam with cached queries. Go back to
 UPPER('%#Trim(form.var)#%')
 
  -Oorspronkelijk bericht-
  Van: Ian Vaughan [mailto:[EMAIL PROTECTED]
  Verzonden: wo 26/02/2003 14:17
  Aan: CF-Talk
  CC:
  Onderwerp: Re: Correct Syntax for this piece of SQL ??
 
 
 
  Pascal
 
  Here is my query
 
  CFQUERY datasource=liv8 name=funding
  cachedwithin=#CreateTimeSpan(0,6,0,0)# blockfactor=100
  SELECT *
  FROM funding
  WHERE 0=1
  cfif Len(Trim(form.orgname))
OR UPPER(orgname) LIKE UPPER(cfqueryparam cfsqltype=CF_SQL_VARCHAR
  value=%#form.orgname#%)
  /cfif
  cfif Len(Trim(form.funding))
OR UPPER(funding) LIKE UPPER(cfqueryparam cfsqltype=CF_SQL_VARCHAR
  value=%#form.funding#%)
  /cfif
  cfif Len(Trim(form.commapproval))
OR UPPER(commapproval) LIKE UPPER(cfqueryparam
 cfsqltype=CF_SQL_VARCHAR
  value=%#form.commapproval#%)
  /cfif
  /CFQUERY
 
  CFIF funding.RecordCount is 0
  span class=black10pbNo files found for specified
  criteria/b/p
  !--- ... else at least one file found ---
 CFELSE
  cfif sgn(Evaluate(funding.RecordCount - Form.MaxRows -
  Form.StartRow)) -1
  p
  CFOUTPUT
 
   smallspan class=black10Viewing #Form.StartRow# to
  #funding.RecordCount# of b#funding.RecordCount#/b records
  found./font/small/span
 
 
  /cfoutput
 
 
  /cfif
 
  div align=left
  table width=761 border=0
  tbody
  tr
  th
 

background=http://intranet.neath-porttalbot.gov.uk/images/table_height.gif;
  align=leftspan class=black10Organization Name/th
  th
 

background=http://intranet.neath-porttalbot.gov.uk/images/table_height.gif;
  align=leftspan class=black10Funding/th
  th
 

background=http://intranet.neath-porttalbot.gov.uk/images/table_height.gif;
  align=leftspan class=black10Committe Approval/th
 
  /tr
  cfoutput query=funding startrow=#StartRow#
maxrows=#Form.MaxRows#
  tr BGCOLOR=###IIF(funding.currentrow MOD 2, DE ('e7efef'), DE
 ('f7f7de'))#
  !--- td align=left valign=top
 

background=http://intranet.neath-porttalbot.gov.uk/images/tablebg1.gif;sp
  an class=black10#Evaluate(CurrentRow)#/td ---
  td align=left valign=top span class=black10#orgname#/td
  td align=left valign=topspan class=black10#funding#/td
  td align=left valign=top span class=black10#commapproval#/td
 
  /tr
  /cfoutput/tbody/table
  - Original Message -
  From: Pascal Peters [EMAIL PROTECTED]
  To: CF-Talk [EMAIL PROTECTED]
  Sent: Wednesday, February 26, 2003 11:34 AM
  Subject: RE: Correct Syntax for this piece of SQL ??
 
 
   This should work on ORACLE8/CF4.5.1 (I'm developping for that platform
  right now)
   I can't make much of your error. Can you post your entire cfquery.
  
   CFQUERYPARAM is used for parameterized sql. It is usually good for
  performence and it helps to avoid sql hacks.
   cfsqltype describes the datatype of your column (in this case a
 VARCHAR2).
  You can check out the help or devnet
  (http://www.macromedia.com/desdev/articles/ben_forta_faster.html)
  
   -Oorspronkelijk bericht-
   Van: Ian Vaughan [mailto:[EMAIL PROTECTED]
   Verzonden: wo 26/02/2003 11:55
   Aan: CF-Talk
   CC:
   Onderwerp: Re: Correct Syntax for this piece of SQL ??
  
  
  
   Pascal
  
   Thanks for your solution but it does not work, I am using CF 4.5 and
  Oracle
   8.  I am getting the following error when using your code, is it not
   compatible with 

Re: unexpeted result with cfqueryparam

[Dina Hess]

 cfset value = String
 cfquery ..
 INSERT INTO table (field)
 VALUES (cfqueryparam cfsqltype=cf_sql_varchar value=#value#
 /cfquery

 Make the value anything you want, quotes, backslashes etc., it will work.

I did...and it doesn't. But I think I know why now.

The inserted data has to be passed directly from an HTML form to
CFQUERYPARAM.  In other words, you can't set the value directly using CFSET
and get the results you expect. But if you enter the value in an INPUT box
and send it directly to CFQUERYPARAM, it works great.

I've been using CFQUERYPARAM successfully for some time now. When I looked
at my code, I noticed that when I'm inserting form values into the DB, I
don't use any functions at all to wrap the form variables in the VALUE
attribute. But when I output, I wrap the DB values with both
HTMLEditFormat() and Trim(). There are all kinds of special characters in
the values I'm displaying, including single quotes. But this method of
handling the data seems to work very well.

 And if you leave the trim() out it will be abc's again. trim() should
 not influence the number of quotes, only the numer of spaces at the
 beginning and end of the string.

I agree that Trim() should not influence a single quote. Nor should
HTMLEditFormat(). But both do, even without CFQUERYPARAM.

Why?

 For a peak at the java behind the scenes, take a look at
 http://spike.oli.tudelft.nl/jochemd/index.cfm?PageID=23

Thanks for the link, Jochem...very helpful. I now have a much better
understanding of how Java uses JDBC to interact with a database
behind-the-scenes.

~Dina




~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
This list and all House of Fusion resources hosted by CFHosting.com. The place for 
dependable ColdFusion Hosting.

Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: unexpeted result with cfqueryparam

[Dina Hess]

  I agree that Trim() should not influence a single quote. Nor should
  HTMLEditFormat(). But both do, even without CFQUERYPARAM.

Please disregard the even without CFQUERYPARAM clause in the above
statement...that's not true. Can't imagine what in the world I was doing to
cause me to come to that conclusion. Totally not true. Erase, erase...

You have to perform all operations on the variable before you use it
 in the SQL statement.

Not really. I mean, not if you're sending a form variable directly to
CFQUERYPARAM for a DB INSERT or UPDATE.

Maybe where the confusion comes in is that Trim() and HTMLEditFormat()
should be used to *display* information coming from the database, not to
manipulate the data before it goes in. CFQUERYPARAM does a great job of
getting the database to handle special characters for us, which is exactly
the point that Jochem was trying to get across, I'm sure.

But then I digressed... :)

~Dina

~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Get the mailserver that powers this list at http://www.coolfusion.com

Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: unexpeted result with cfqueryparam

[Dina Hess]

 Dina Hess wrote:
 cfset value = String
 cfquery ..
 INSERT INTO table (field)
 VALUES (cfqueryparam cfsqltype=cf_sql_varchar value=#value#
 /cfquery
 
 Make the value anything you want, quotes, backslashes etc., it will
work.
 
  I did...and it doesn't. But I think I know why now.

 It doesn't? Even if you just use value=#value# without any function
calls?

Right. I was taking you literally. You said enter *anything.* :) But I can't
directly enter a double quote or a pound sign without escaping or CF will
throw an error...that's what I meant...and that's why I changed my testing
methodology to make use of form fields for the data entry.

~Dina


~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Your ad could be here. Monies from ads go to support these lists and provide more 
resources for the community. http://www.fusionauthority.com/ads.cfm

Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: unexpeted result with cfqueryparam

[Dina Hess]

Yes, but you are setting the value using CFSET and CF will throw an error if
you enter double quotes or pounds via CFSET without escaping. But I suspect
you knew what I meant... :)

~Dina

- Original Message -
From: Jochem van Dieten [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Monday, February 24, 2003 4:53 PM
Subject: Re: unexpeted result with cfqueryparam


 Dina Hess wrote:
 Dina Hess wrote:
 
 cfset value = String
 cfquery ..
 INSERT INTO table (field)
 VALUES (cfqueryparam cfsqltype=cf_sql_varchar value=#value#
 /cfquery
 
 Make the value anything you want, quotes, backslashes etc., it will
 work.
 
 I did...and it doesn't. But I think I know why now.
 
 It doesn't? Even if you just use value=#value# without any function
 calls?
 
  Right. I was taking you literally. You said enter *anything.* :)

 Anything isn't the problem, value is. I meant the variable named
 value, not the argument named value ;-)

 Jochem

 
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Signup for the Fusion Authority news alert and keep up with the latest news in 
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm

Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: unexpeted result with cfqueryparam

[Dina Hess]

 But not when using cfqueryparam. Using cfqueryparam is meant to call the
 functionality of the database driver to do the escaping. Any escaping done
by CF
 before that will only result in doubling the quotes twice, and therefore
CF
 should do no escaping whatsoever of any value passed through cfqueryparam.


Yes, I do remember seeing your previous comments about using CFQUERYPARAM to
preserve single quotes...but I could never get that to test out. Do you have
a resource to share? Maybe this is just another Access anomaly...

Anyway, here's some test code I ran (CFMX/Access) before sending my last
post...

cfset variables.showLabel = abc's
cfquery datasource=test
  UPDATE foo
  SET showLabel = cfqueryparam cfsqltype=cf_sql_varchar
value=#trim(PreserveSingleQuotes(showLabel))# /
  where foo_id = 1
/cfquery

...foo is now abc's in the database, which is what we want.

But when I change the CFQUERYPARAM tag to cfqueryparam
cfsqltype=cf_sql_varchar value=#trim(HTMLEditFormat(showLabel))# /

...foo becomes abc''s in the database

If I change the CFQUERYPARAM tag again to cfqueryparam
cfsqltype=cf_sql_varchar value=#trim(showLabel)# /

foo is still abc''s




~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Signup for the Fusion Authority news alert and keep up with the latest news in 
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm

Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: unexpeted result with cfqueryparam

[Dina Hess]

 What still doesn't seem to make sense is that using the trim() in the
 cfqueryparam value does add an additional apostrophe, but not using trim()
 doesn't.

Hmmmcould've sworn I tested this before but...I just tested
cfqueryparam cfsqltype=cf_sql_varchar value=#showLabel# / and, sure
enough, foo becomes abc's again...just what we want. So...I guess as long as
we leave well enough alone (that is, don't use *any* functions with
CFQUERYPARAM), CFQUERYPARAM gets the database to do all the work, right?

~Dina






~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm

Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

test-please ignore

[Dina Hess]

Archived messages are not making it to my Inbox.
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Your ad could be here. Monies from ads go to support these lists and provide more 
resources for the community. http://www.fusionauthority.com/ads.cfm

Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: Update Multiple Records With One Query

[Dina Hess]

Jillian,

I looked over your code and notice you have your update query on the same
page as your form, yet your form's action page is set to
grading.cfm?id=#courses.id#. If you move that chunk of code to
grading.cfm, all should work fine...and you shouldn't need to use url.action
or form.action variables.

BTW, just to be on the safe side, *all* of the form parameters used in your
update query should be wrapped with CFQUERYPARAM...but you already have a
good start on that.

~Dina


- Original Message -
From: Joshua Miller [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Thursday, February 13, 2003 10:32 PM
Subject: RE: Update Multiple Records With One Query


 Even better - thanks Dave!

 Joshua Miller
 Head Programmer / IT Manager
 Garrison Enterprises Inc.
 www.garrisonenterprises.net
 [EMAIL PROTECTED]
 (704) 569-9044 ext. 254

 
 *
 Any views expressed in this message are those of the individual sender,
 except where the sender states them to be the views of
 Garrison Enterprises Inc.

 This e-mail is intended only for the individual or entity to which it is
 addressed and contains information that is private and confidential. If
 you are not the intended recipient you are hereby notified that any
 dissemination, distribution or copying is strictly prohibited. If you
 have received this e-mail in error please delete it immediately and
 advise us by return e-mail to [EMAIL PROTECTED]
 
 *


 -Original Message-
 From: Dave Watts [mailto:[EMAIL PROTECTED]]
 Sent: Thursday, February 13, 2003 11:30 PM
 To: CF-Talk
 Subject: RE: Update Multiple Records With One Query


  Try this for your UPDATE routine (adding your remaining
  fields as needed):
 
  cfloop index=Indx from=1 to=#Form.RowCount#
 
  cfquery datasource=#dsn# name=update
  UPDATE attendee SET
  assigneddate ='#evaluate(form.assigneddate#Indx#)#',
  attended = '#evaluate(form.attended#Indx#)#'
  WHERE users_id = #evaluate(form.ID#Indx#)#
  /cfquery
 
  /cfloop

 Just to nitpick, you don't need to use Evaluate here, and you don't need
 so many hashes:

 ... SET assigneddate = '#Form[assigneddate  Indx]#', ...

 Dave Watts, CTO, Fig Leaf Software
 http://www.figleaf.com/
 voice: (202) 797-5496
 fax: (202) 797-5444


 
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Signup for the Fusion Authority news alert and keep up with the latest news in 
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm

Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: Not all Records Being Returned

[Dina Hess]

Jillian,

I'd start by checking the attendeeupdate query since userinfo is
dependent upon its results. How many records are returned? Do they all have
the same users_id? Is assigneddate really supposed to be equal to url.id?

~Dina


- Original Message -
From: Jillian Carroll [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Wednesday, February 12, 2003 8:48 AM
Subject: Not all Records Being Returned


 Hey everybody... I'm having a problem that is causing me to knock my
 head against the wall... I'm sure it's something completely obvious.  I
 have the following code (pasted below) and it should return 3
 users/rows... but it only returns 1.  There isn't any error message, it
 just lists one.  I did confirm in my DB that there are 3 users who are
 assigned to this course.



 Any suggestions would be most appreciated.  I'm not sure it helps, by my
 environment is: RH 7.2/CF 5/Postgres 7.2.



 Thank you!



 --

 Jillian



 !--- QUERIES AND VARIABLE SETTING ---

 cfset today = DateFormat(now(),mm/dd/)



 cfquery name=permissions datasource=#DSN#

 SELECT id, centraladmin, provadmin, trainer FROM users WHERE id =
 #client.id#

 /cfquery



 cfquery name=courses datasource=#DSN#

 SELECT * from courses WHERE id = #url.id#

 /cfquery



 cfquery name=attendeeupdate datasource=#DSN#

 SELECT * from attendee WHERE assigneddate = #url.id#

 /cfquery



 cfquery name=userinfo datasource=#DSN#

 SELECT * FROM users WHERE id = #attendeeupdate.users_id# ORDER BY lname,
 fname ASC

 /cfquery



 cfquery name=salutation datasource=#DSN#

 SELECT * FROM salutation ORDER by salutation ASC

 /cfquery



 cfquery name=province datasource=#DSN#

 SELECT * FROM prov ORDER by abbreviation ASC

 /cfquery



 cfquery name=country datasource=#DSN#

 SELECT * FROM country ORDER by name ASC

 /cfquery



 cfquery name=profession datasource=#DSN#

 SELECT * FROM profession

 /cfquery



 cfquery name=designation datasource=#DSN#

 SELECT * FROM designation

 /cfquery



 cfquery name=yearsprac datasource=#DSN#

 SELECT * FROM years

 /cfquery



 cfquery name=empstatus datasource=#DSN#

 SELECT * FROM empstatus

 /cfquery



 cfquery name=empplace datasource=#DSN#

 SELECT * FROM empplace

 /cfquery



 cfquery name=empresp datasource=#DSN#

 SELECT * FROM empresp

 /cfquery



 cfquery name=dpccat datasource=#DSN#

 SELECT * FROM dpccat

 /cfquery



 cfquery name=dpcclass datasource=#DSN#

 SELECT * FROM dpcclass

 /cfquery



 cfquery name=caeyear datasource=#DSN#

 select * FROM caeyear ORDER BY year DESC

 /cfquery



 cfquery name=payment datasource=#DSN#

 SELECT * FROM payment ORDER BY type ASC

 /cfquery



 cfquery name=postassign datasource=#DSN#

 SELECT * FROM postassign

 /cfquery



 cfquery name=courses datasource=#DSN#

 SELECT id, type, other, city, prov, s1_startdate

 FROM courses

 WHERE s1_startdate = #Now()#

 ORDER BY s1_startdate DESC

 /cfquery

 !--- END QUERIES AND VARIABLE SETTING ---



 !--- SET DEFAULT VALUES FOR CHECKBOX/RADIO BUTTONS ---

 CFPARAM NAME=FORM.attended default=0

 CFPARAM NAME=FORM.withdrawn default=0

 CFPARAM NAME=FORM.paymentcleared default=0

 CFPARAM NAME=FORM.assignrec1 default=0

 CFPARAM NAME=FORM.assignrec2 default=0

 CFPARAM NAME=FORM.assignrec3 default=0

 CFPARAM NAME=FORM.pretest default=0

 CFPARAM NAME=FORM.skilltest1 default=0

 CFPARAM NAME=FORM.skilltest2 default=0

 CFPARAM NAME=FORM.skilltest3 default=0



 cfif attendeeupdate.assignrec1 EQ 1CFSET
 checked4=YesCFELSECFSET checked4=No/CFIF

 cfif attendeeupdate.assignrec2 EQ 1CFSET
 checked5=YesCFELSECFSET checked5=No/CFIF

 cfif attendeeupdate.assignrec3 EQ 1CFSET
 checked6=YesCFELSECFSET checked6=No/CFIF

 cfif attendeeupdate.pretest EQ 1CFSET checked7=YesCFELSECFSET
 checked7=No/CFIF

 cfif attendeeupdate.skilltest1 EQ 1CFSET
 checked8=YesCFELSECFSET checked8=No/CFIF

 cfif attendeeupdate.skilltest2 EQ 1CFSET
 checked9=YesCFELSECFSET checked9=No/CFIF

 cfif attendeeupdate.skilltest3 EQ 1CFSET
 checked10=YesCFELSECFSET checked10=No/CFIF

  !--- END SET DEFAULT VALUES FOR CHECKBOX/RADIO BUTTONS ---



 cfinclude template=headerinc.cfm /



 br /



 table border=0 cellpadding=0 cellspacing=0 width=100%

 tr

 td colspan=2

 table border=0 cellpadding=2 cellspacing=0
 width=100% class=tableborder

 tr

 td class=tabletitleCOURSE ATTENDEE GRADING
 (Please scroll to the right to see entire record for each user.)/td

 td class=tabletitle
 align=rightcfoutputa href=user_form.cfm?id=#client.id#
 class=tabletitlesm[Modify My Personal Information]/a/cfoutput a
 href=main.cfm class=tabletitlesm[Home]/a/td

 /tr

 tr

 td class=tabletitlegrey colspan=2The
 following attendees are assigned to: cfoutput#courses.city#,
 #courses.prov# - #courses.s1_startdate#/cfoutput/td

 /tr

 /table

 /td

 /tr

 tr height=1td colspan2/td/tr

 tr

 

Re: CFLOCATION IE 6

[Dina Hess]

I've heard this can happen if you're running Microsoft UrlScan. There should
be something in the archives on that.

- Original Message -
From: Matt Kornguth [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Tuesday, February 11, 2003 1:29 PM
Subject: Re: CFLOCATION  IE 6


 Aaron,
 Actually, no, we're not using CFHTMLHEAD. We are preventing caching in our
application.cfm file by using CFHEADER tags (expires, pragma, 
cache-control), but not CFHTMLHEAD.

 Maybe the CFHEADER tags are causing it? Since it is showing up above all
Body output, it could be something in our application.cfm.

 Any thoughts?



 On 2/11/2003 2:23 PM, Aaron Johnson [EMAIL PROTECTED] wrote:
 Hi Matt,
 
  Has anyone found an issue using CFLOCATION in IE 6, where http
  header information displays on the new url ... above the page
  output? This is in CF 5.0 Enterprise BTW.
 
  Can avoid it by redirecting using CFHEADER instead, but I'd hate
  to have to program around this.
  -- we've seen this issue before when using CFHTMLHEAD and CFLOCATION
 when
 CFHTMLHEAD writes more that n characters into the head before doing
 a
 cflocation.  You using CFHTMLHEAD by chance?
 
 AJ
 
 Aaron Johnson
 http://cephas.net/blog/
 [EMAIL PROTECTED]
 
 
 
 
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
This list and all House of Fusion resources hosted by CFHosting.com. The place for 
dependable ColdFusion Hosting.

Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: WOT: Access SQL Question - Round 2

[Dina Hess]

Matthew and Isaac have provided you with two excellent workarounds. I just have one 
thing to add.

The where pk=fk syntax doesn't produce an inner join in Access. Instead use from 
issues i inner join users u on i.owner = u.name.

Too bad you have to go thru all of this...a normalized database schema would've 
prevented this problem...which you probably haven't seen the last of... :)

~Dina



~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
This list and all House of Fusion resources hosted by CFHosting.com. The place for 
dependable ColdFusion Hosting.

Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: WOT: Access SQL Question - Round 2

[Dina Hess]

In Access, if you use FROM issues, owners WHERE issues.owner = users.name you're 
doing a cartesian join, which produces every possible combination of the two tables. 
An inner join, on the other hand, will return only the rows that match your join 
condition.

In SQL Server, however, the WHERE issues.owner = users.name syntax *does* produce an 
inner join.

Does that help? 

BTW, kudos on that workaround...I was still scratching over it when your post came 
through. :) 



  I'm know any anything about inner/outer joins - how is using an inner
  join different from using pk=fk, and why would you want to use it in
  this case?



~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm

Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: WOT: Access SQL Question - Round 2

[Dina Hess]

I'm not too sure what you're getting at...maybe I wasn't clear.

My point was that a list of values should not be stored in a DB field. This is a basic 
criterion of a normalized database. It tends to become increasingly difficult to work 
with such databases.


   Too bad you have to go thru all of this...a normalized database 
   schema would've prevented this problem...which you probably 
   haven't seen the last of... :)
   
   ~Dina

  Ouch! Database - Refactoring 101 
  http://www.agiledata.org/essays/databaseRefactoring.html

  Always fix broken windows...


~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Signup for the Fusion Authority news alert and keep up with the latest news in 
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm

Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: WOT: Access SQL Question - Round 2

[Dina Hess]

Admittedly, I don't use Access that often, but that hasn't been my
experience. My experience has been that Access will treat non-ANSI joins as
described above as inner joins, at least with regard to passthrough SQL
statements from CF.

I just tried Matthew's query in Access and it worked just fine. So...my apologies, 
Matthew, for misleading you and thank you, Dave, for sharing the wisdom of your 
experience. :)

~Dina





~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
This list and all House of Fusion resources hosted by CFHosting.com. The place for 
dependable ColdFusion Hosting.

Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: WOT: Access SQL Question - Round 2

[Dina Hess]

While the recordsets produced by both of these queries are the same, the second query 
(with the ANSI join) performs *much* better. 

My simple test returned a recordset of only two records, yet the time on the first 
query was 135ms compared to only 15ms on the second query.

cfquery name=getUsers datasource=test
SELECT u.name
FROM issues i, users u
WHERE u.name = left(i.owner,len(u.name))
/cfquery

cfdump var=#getUsers#br

cfquery name=getOwners datasource=test
SELECT u.name
FROM issues i inner join users u on left(i.owner,len(u.name))= u.name
/cfquery

cfdump var=#getOwners#


  I'm with you on this, Dave.  I think the existence of the join condition
  eliminates the Cartesian cross-product result.


   In Access, if you use FROM issues, owners WHERE issues.owner 
   = users.name you're doing a cartesian join, which produces 
   every possible combination of the two tables. An inner join, 
   on the other hand, will return only the rows that match your 
   join condition.



~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Signup for the Fusion Authority news alert and keep up with the latest news in 
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm

Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: HELP!!!! jrun-resources.xml

[Dina Hess]

Tony,

Could this problem be related to case sensitivity?? Maybe if you changed the 
datasource in your templates to Platypus MX would recognize it.

~Dina
  - Original Message - 
  From: Tony Weeg 
  To: CF-Talk 
  Sent: Monday, January 13, 2003 9:41 AM
  Subject: HELP jrun-resources.xml


  did an sql 2000 service pack upgrade
  and a win2k adv critical updates on all servers
  last night, and now one of my servers is having
  issues making a datasource connection to
  a machine that prior to last night, had no issues...

  here is the error I am getting, HELP

  Connection verification failed for data source: platypus
  []java.sql.SQLException: No datasource named Platypus is configured in
  jrun-resources.xml
  The root cause was that: java.sql.SQLException: No datasource named
  Platypus is configured in jrun-resources.xml

  first, where the heck is jrun-resources.xml? second, should I even be
  looking
  for this cause it is or isnt the problem? what should I do? 
  I have verified the odbc connection on the machine, and it is fine
  going to the server, datasource test works 100% (from windows odbc
  connection form) just not from cf or the administrator setting it up

  thanks.


  ...tony

  Tony Weeg
  Senior Web Developer
  UnCertified Advanced ColdFusion Developer
  Information System Design
  Navtrak, Inc.
  Mobile workforce monitoring, mapping  reporting
  www.navtrak.net
  410.548.2337 

  
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Your ad could be here. Monies from ads go to support these lists and provide more 
resources for the community. http://www.fusionauthority.com/ads.cfm

Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: Help with a query.

[Dina Hess]

If you're using SQL Server, you could rewrite your query like this:

SELECT s.shopperID
FROM tblShopper s
WHERE NOT EXISTS (SELECT * FROM tblorder o where o.shopperID = s.shopperID)

~Dina



- Original Message - 
  From: Eric Creese 
  To: CF-Talk 
  Sent: Friday, January 10, 2003 10:15 AM
  Subject: Help with a query.


  I would do the following

  SELECT tblShopper.shopperID
  FROM tblShopper
  WHERE shopperID NOT IN (SELECT DISTINCT tblOrder.shopperid FROM tblorder)

  Not sure what DB your using but using a NOT and or a DISTINCT can slow performance. 
Also your distinct appeared to be in the wrong place, I am guessing the 
tblShopper.shopperid is unique so a DISTINCT there will not help you, however using it 
in the subquery makes sense since a sopper could have multiple orders. Truthfully 
though you could eliminate it from the subquery as well.
  
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
This list and all House of Fusion resources hosted by CFHosting.com. The place for 
dependable ColdFusion Hosting.

Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: Help with a query.

[Dina Hess]

Just for the record, EXISTS can be used in Access, too.

Joe, I think the SQL statement Samuel provided would be your best bet for performance. 
In fact, it's probably safe to say that a JOIN will generally perform better than 
EXISTS and EXISTS will generally perform better than IN.

~Dina
  - Original Message - 
  From: Dina Hess 
  To: CF-Talk 
  Sent: Friday, January 10, 2003 2:07 PM
  Subject: Re: Help with a query.


  If you're using SQL Server, you could rewrite your query like this:

  SELECT s.shopperID
  FROM tblShopper s
  WHERE NOT EXISTS (SELECT * FROM tblorder o where o.shopperID = s.shopperID)

  ~Dina



  - Original Message - 
From: Eric Creese 
To: CF-Talk 
Sent: Friday, January 10, 2003 10:15 AM
Subject: Help with a query.


I would do the following

SELECT tblShopper.shopperID
FROM tblShopper
WHERE shopperID NOT IN (SELECT DISTINCT tblOrder.shopperid FROM tblorder)

Not sure what DB your using but using a NOT and or a DISTINCT can slow 
performance. Also your distinct appeared to be in the wrong place, I am guessing the 
tblShopper.shopperid is unique so a DISTINCT there will not help you, however using it 
in the subquery makes sense since a sopper could have multiple orders. Truthfully 
though you could eliminate it from the subquery as well.

  
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Signup for the Fusion Authority news alert and keep up with the latest news in 
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm

Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: SOLVED Re: OT: style sheet weirdness

[Dina Hess]

Netscape 4 doesn't like background-color: transparent; either, even though the W3C 
validator OKs it. If you use anything other than transparent, it's fine though.

- Original Message - 
  From: Ryan Kime 
  To: CF-Talk 
  Sent: Thursday, January 09, 2003 4:43 PM
  Subject: RE: SOLVED Re: OT: style sheet weirdness


  FYI, I believe using underscores is against CSS spec (not sure which
  one(s))...use dashes instead.

  -Original Message-
  From: Bryan Stevenson [mailto:[EMAIL PROTECTED]] 
  Sent: Thursday, January 09, 2003 4:37 PM
  To: CF-Talk
  Subject: SOLVED Re: OT: style sheet weirdness


  OK...the issue was the use of underscores in the style element
  names...Netscape doesn't like it (at least ver. 4.07)

  Bryan Stevenson B.Comm.
  VP  Director of E-Commerce Development
  Electric Edge Systems Group Inc.
  t. 250.920.8830
  e. [EMAIL PROTECTED]

  -
  Macromedia Associate Partner
  www.macromedia.com
  -
  Vancouver Island ColdFusion Users Group
  Founder  Director
  www.cfug-vancouverisland.com
  - Original Message -
  From: Bryan Stevenson [EMAIL PROTECTED]
  To: CF-Talk [EMAIL PROTECTED]
  Sent: Thursday, January 09, 2003 2:25 PM
  Subject: Re: OT: style sheet weirdness


   Well Billy Bob...that's Shaw for yaTelus ADSL for me ;-)
  
   Does anyone know why a style sheet would be completely ignored in 
   Netscape 4.07?
  
   I'm using this to include the sheet:
   head
titleGeneric CSS Examples/title
 link href=generic_styles.css rel=stylesheet type=text/css 
   /head
  
   and a style sheet snippet as follows (not wrapped in a STYLE tag): 
   .Normal_Text_Black {
font-family: Verdana, Geneva, Arial;
font-size: 12px;
color: 00;
   }
  
   .Bold_Normal_Text_Black
   {
font-family: Verdana, Geneva, Arial;
font-size: 12px;
 font-weight: bold;
color: 00;
   }
  
   Bryan Stevenson B.Comm.
   VP  Director of E-Commerce Development
   Electric Edge Systems Group Inc.
   t. 250.920.8830
   e. [EMAIL PROTECTED]
  
   -
   Macromedia Associate Partner
   www.macromedia.com
   -
   Vancouver Island ColdFusion Users Group
   Founder  Director
   www.cfug-vancouverisland.com
   - Original Message -
   From: [EMAIL PROTECTED]
   To: CF-Talk [EMAIL PROTECTED]
   Sent: Thursday, January 09, 2003 2:06 PM
   Subject: Re: OT: style sheet weirdness
  
  
It's my webmail client.  Keeps putting my email address as my name 
even
   though I tell it to use my name.
   
I'm actually Billy Bob Thorton :)
   
- Original Message -
From: Bryan Stevenson [EMAIL PROTECTED]
Date: Thursday, January 9, 2003 3:08 pm
Subject: Re: OT: style sheet weirdness
   
 Ahhhthat did the trick.thanks [EMAIL PROTECTED] whoever you are 
 (yer a Canuck and that's good enough for me) ;-)

 Bryan Stevenson B.Comm.
 VP  Director of E-Commerce Development
 Electric Edge Systems Group Inc.
 t. 250.920.8830
 e. [EMAIL PROTECTED]

 -
 Macromedia Associate Partner
 www.macromedia.com
 -
 Vancouver Island ColdFusion Users Group
 Founder  Director
 www.cfug-vancouverisland.com
 - Original Message -
 From: [EMAIL PROTECTED]
 To: CF-Talk [EMAIL PROTECTED]
 Sent: Thursday, January 09, 2003 1:44 PM
 Subject: Re: OT: style sheet weirdness


  Is this a style sheet you're including via the link tag?  If
 so, then
 you need to remove the style tags.
 
  - Original Message -
  From: Bryan Stevenson [EMAIL PROTECTED]
  Date: Thursday, January 9, 2003 2:42 pm
  Subject: Re: OT: style sheet weirdness
 
   Nope...removing semi-colons did not help...but thanks ;-)
  
   Bryan Stevenson B.Comm.
   VP  Director of E-Commerce Development
   Electric Edge Systems Group Inc.
   t. 250.920.8830
   e. [EMAIL PROTECTED]
  
   -
   Macromedia Associate Partner
   www.macromedia.com
   -
   Vancouver Island ColdFusion Users Group
   Founder  Director
   www.cfug-vancouverisland.com
   - Original Message -
   From: Christian Cantrell [EMAIL PROTECTED]
   To: CF-Talk [EMAIL PROTECTED]
   Sent: Thursday, January 09, 2003 1:21 PM
   Subject: Re: OT: style sheet weirdness
  
  
Try removing the semicolons from the last properites in each
 of your
classes.  For instance:
   
.Normal_Text_Black
{
 font-family: Verdana, Geneva, 

Re: newbie: help with sql query

[Dina Hess]

If you are on access, the outer join won't work and you have to do a union:

Sorry...that's not true. Access supports outer joins, but the syntax is either RIGHT 
JOIN or LEFT JOIN rather than OUTER JOIN.

Conversely if bugs.bug_type_id = 0 then simply add that statement to the query. .

Azeem, if you're still listening, the above comment and the code at the end of your 
original post to output the data indicates that you are retrieving *all* bug_type_ids, 
whether they are 0 or not. If that is true, you don't need the last six lines of your 
SQL statement since you can control your output based on a CFIF condition, which 
you've done. If you *do* want to filter out all bug_type_ids that are 0, simply add 
AND bugs.bug_type_id  '0' to your subquery (but don't forget to correct the join 
syntax in the subquery).

Since you appear to be joining multiple tables: bugs, bug_type, bug_status, and 
priority using an inner join, please be aware that Access does not support the WHERE 
PK=FK inner join syntax (that's fine in SQL Server though). 

Instead you will need to join the tables in the FROM clause. The easiest way to build 
an Access query is to use the Access Query Designer, switching to SQL view to run it 
and see the syntax. When you get the results you expect, just copy/paste into CF.

***

Regarding your question about why you cannot output the bug_type, it may be that CF is 
seeing the datatype as numeric. Therefore, try eliminating the quotes around 0. 

As an aside, it is unnecessary to wrap your variables with pound signs in the CFIF 
tag. And, logically, you should change cfelseif #bug_type_id# eq 0 to CFELSE.

~Dina






~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Signup for the Fusion Authority news alert and keep up with the latest news in 
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm

Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: Loop Errors

[Dina Hess]

Also on Line 77, the CFOUTPUT should *not* contain the GROUP attribute because it's 
being used to populate a select, not display output by group.

Oops! My statement above is incorrect. In this case, the GROUP attribute in the 
CFOUTPUT behaves like SQL's DISTINCT function. It's not only OK to use, it's necessary 
to return the correct results. My apologies for any confusion this may have caused.

~Dina

~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Your ad could be here. Monies from ads go to support these lists and provide more 
resources for the community. http://www.fusionauthority.com/ads.cfm

Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: Loop Errors

[Dina Hess]

The CFOUTPUT on Line 30 is fine because it's nested under a CFOUTPUT that contains the 
GROUP attribute... 

However, both 

Line 24, 
cfoutput query=rsATA1 group=category 

and 

Line 77, 
option value=0Select ATA Code cfoutput query=rsATA1 group=category 

are in error because the query that is referenced does not exist in this template. The 
only query in this template is rsquery1. 

Also on Line 77, the CFOUTPUT should *not* contain the GROUP attribute because it's 
being used to populate a select, not display output by group.

And...Lines 3 thru 8 contain duplicate HTML tags.

~Dina


  - Original Message - 
  From: Tony Weeg 
  To: CF-Talk 
  Sent: Monday, January 06, 2003 10:55 AM
  Subject: RE: Loop Errors


  but strangely enough, cfmx doesn't barf on nested cfoutputs.

  ...tony

  Tony Weeg
  Senior Web Developer
  UnCertified Advanced ColdFusion Developer
  Information System Design
  Navtrak, Inc.
  Mobile workforce monitoring, mapping  reporting
  www.navtrak.net
  410.548.2337 

  -Original Message-
  From: Jason Lees (National Express)
  [mailto:[EMAIL PROTECTED]] 
  Sent: Monday, January 06, 2003 11:41 AM
  To: CF-Talk
  Subject: RE: Loop Errors


  the simple answer about the nested cfoutput tags is whats the point?
  and
  so the parser catches the nested cfoutput tags as a coding error.

  Its like doing 

  cfoutput
  #myVar1#
  cfoutput
  #MyVar2#
  /cfoutput
  /cfoutput



  Jason Lees
  Development Team Leader
  National Express Coaches Ltd.



  -Original Message-
  From: Ian Vaughan [mailto:[EMAIL PROTECTED]]
  Sent: 06 January 2003 16:25
  To: CF-Talk
  Subject: Re: Loop Errors


  Thanks

  For the replies

  Taking them in no particular order

  Jason Lees said
  Well for a start you dont neet to nest the cfoutput's

  Why don't I need to nest the cfoutputs?

  Jeff Garza and Mosh Teitelbaum picked up on the same point, jeff
  said..

  I beleive that you have referenced the query wrong.

  Try:

  cfoutput query=rsquery1 group=category

  I know what you are saying however, the query is linked to the output of
  the
  actual select shown below

  option value=0Select ATA Code cfoutput query=rsATA1
  group=category
  option value=#ATA_Chapter##category# /cfoutput

  If you look at my code you should see this..


  Jerry Johnson said

  cfoutput
  cfset i = i + 1
  ChapterArray#category#[#i#] = #category#;
  ChapterArrayVal#category#[#i#] = #catno# #category#;
  /cfoutput
  /cfoutput

  why do you think I dont need this??

  The code I am using is based on the code below , which works but I think
  they were using two tables I am only using one table??  From this you
  may be
  able to solve some of these problems???


  ---ORIGINAL--
  !DOCTYPE HTML PUBLIC -//W3C//DTD HTML 4.0 Transitional//EN

  html
  head
   titleUntitled/title
  /head

  body
  !--- Select the ATA_Chapter, ATA_Section and title_Codes. ---
  cfquery name=rsquery1 datasource=ATA
  SELECT a.ata_chapter, a.ata_title, b.ata_section, b.ATA_TITLE SubTitle
  FROM ata_codes a, ata_sub_codes b
  WHERE a.ata_chapter = b.ata_chapter
  ORDER BY a.ata_chapter, b.ata_section
  /cfquery
  !--- Select all the sub codes. ---
  script language = JavaScript
  !--
  // For each chapter, create an array to hold the sections.
  // Each chapter array will be identified by the ATA_Chapter
  cfoutput query=rsATA1 group=ATA_Chapter
  // Create the array
  ChapterArray#ATA_Chapter# = new Array();
  ChapterArrayVal#ATA_Chapter# = new Array();
  cfset i = 0
  // Populate the array
  cfoutput
  cfset i = i + 1
  ChapterArray#ATA_Chapter#[#i#] = #ATA_Chapter#;
  ChapterArrayVal#ATA_Chapter#[#i#] = #ata_section# #SubTitle#;
  /cfoutput
  /cfoutput


  // Function to populate the ATA_Sub_Codes for the ATA_Code selected
  function PopulateATASubCode() {
  // Only process the function if the first item is not selected.
  var indx = document.InsertForm.ATACode.selectedIndex;
  if (indx != 0) {
  // Find the Chapter
  var ThisChapter = document.InsertForm.ATACode[indx].value;
  // Set the length of the ATA_Sub_Codes drop down equal to the length of
  the
  Chapter's array
  var len;
  len = eval(ChapterArray + ThisChapter + .length);
  document.InsertForm.ATA_SubCode.length = len;
  // Put 'Select' as the first option in the SubCode drop-down
  document.InsertForm.ATA_SubCode[0].value = ;
  document.InsertForm.ATA_SubCode[0].text = Select ATA Sub Code;
  document.InsertForm.ATA_SubCode[0].selected = true;
  // Loop through the chapter's array and populate the SubCode drop down.
  for (i=1; ilen; i++) {
  document.InsertForm.ATA_SubCode.value = eval(ChapterArray +
  ThisChapter +
  );
  document.InsertForm.ATA_SubCode.text = eval(ChapterArrayVal +
  ThisChapter
  + );
  }
  }
  }
  //--
  /script

  html
  head
  titleRequirements Database Add Requirement/title
  meta http-equiv=Content-Type content=text/html; charset=iso-8859-1
  /head
  body
  form 

Re: (SQL) Query returning too many rows

[Dina Hess]

You didn't mention what database you're using...
  - Original Message - 
  From: Owens, Howard 
  To: CF-Talk 
  Sent: Thursday, December 12, 2002 7:58 PM
  Subject: (SQL) Query returning too many rows


  I'm stuck on this query:


  SELECT T1.AD_ID, 
  T1.COMPANY_NAME, 
  T1.CAPTION, 
  T1.AD_NUM, 
  T2.IMAGE_ID, 
 T2.IMAGE_NAME, 
  T2.HEIGHT, 
  T2.WIDTH
  FROM T1, T2
  WHERE T1.DATE_START between  #createodbcdate(DateAdd(D, -7, NOW()))#
  AND #createodbcdate(now())#
  AND T1.AD_NUM = T2.AD_NUM


  Here's the issue ... AD_NUM exists once in T1, but can exist multiple times
  in T2.

  Where I should be getting 24 rows returned, I'm getting 55. I'm getting
  duplicates.  And I'm having a brain fart on how to write a query that won't
  get me duplicates. 

  Some help would be appreciated.

  Thanks.

  H.
  
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
This list and all House of Fusion resources hosted by CFHosting.com. The place for 
dependable ColdFusion Hosting.

Re: What does U2 actually update?

[Dina Hess]

I couldn't agree more.

~Dina
  - Original Message - 
  From: Joshua Miller 
  To: CF-Talk 
  Sent: Friday, December 13, 2002 10:55 AM
  Subject: RE: What does U2 actually update?


  Ok, ok, ok, voice of reason here ...

  Jochem is typically a fabulous resource for information on this list -
  he's helped many of us out several times. 

  Let him have a bad day if he wants, I can certainly understand being
  frustrated ... Just drop it.

  Joshua Miller
  Head Programmer / IT Manager
  Garrison Enterprises Inc.
  www.garrisonenterprises.net
  [EMAIL PROTECTED]
  (704) 569-9044 ext. 254
   
  
  *
  Any views expressed in this message are those of the individual sender,
  except where the sender states them to be the views of 
  Garrison Enterprises Inc.
   
  This e-mail is intended only for the individual or entity to which it is
  addressed and contains information that is private and confidential. If
  you are not the intended recipient you are hereby notified that any
  dissemination, distribution or copying is strictly prohibited. If you 
  have received this e-mail in error please delete it immediately and
  advise us by return e-mail to [EMAIL PROTECTED]
  
  *


  -Original Message-
  From: Matthew Small [mailto:[EMAIL PROTECTED]] 
  Sent: Friday, December 13, 2002 11:29 AM
  To: CF-Talk
  Subject: RE: What does U2 actually update?


  Well, I don't read every mail on this thing, it's impossible to do
  especially given the amount of mail that this list generates.

  If you don't want to answer a question - then don't.  No reason to be a
  jerk about it. 

  Matthew Small
  IT Supervisor
  Showstopper National Dance Competitions
  3660 Old Kings Hwy 
  Murrells Inlet, SC 29576
  843-357-1847
  http://www.showstopperonline.com

  -Original Message-
  From: Jochem van Dieten [mailto:[EMAIL PROTECTED]] 
  Sent: Friday, December 13, 2002 11:14 AM
  To: CF-Talk
  Subject: RE: What does U2 actually update?

  Quoting Robertson-Ravo, Neil (RX) Neil.Robertson-
  [EMAIL PROTECTED]:

   someones in a grumpy mood :-)

  Apart from the obviousness, the answer had been sent less than an hour
  before:

  - Original message -
  Date: Fri, 13 Dec 2002 14:36:07 +0100
  Subject: Re: Happy story with updater2?
  snip
  For me, it does what the release notes say it will do.

  - Original message -
  Date: Fri, 13 Dec 2002 09:27:19 -0500
  Subject: What does U2 actually update?
  snip
  So what problems is U2 reported to fix?


  And yes, that does make somebody feel unappreciated. Combine that with
  being less than happy about the issues fixed in U2, the non-issues fixed
  in U2 and the issues not fixed in U2, add in a little disappointment
  about the release date and if it weren't Friday I would indeed be grumpy
  :-)

  Jochem



  
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Get the mailserver that powers this list at http://www.coolfusion.com

Re: Another Newbie Problem

[Dina Hess]

Well, Jochem, has already given you some valuable information but since
Month() and Year() are standard SQL functions, this should work, too:

where month(datefield) = #form.month#
and year(datefield) = #form.year#

~Dina
  - Original Message - 
  From: Jochem van Dieten 
  To: CF-Talk 
  Sent: Wednesday, December 11, 2002 9:44 AM
  Subject: RE: Another Newbie Problem


  Quoting [EMAIL PROTECTED]:
   
   We have several thousand records in a db with a standard odbc
   datetime stamp column.  We want to be able to filter out end of month
   statistics (all trouble calls in November, 2002, for instance) by
   using a set of drop down menus (month, year, type of call).   

  There are 2 generic ways to do this:
  - create a startdate and an enddate and do a BETWEEN search
  - use SQL date functions to extract year and month from the field and do
  an exact search on that

  Code example of the latter (the former has been posted already):
  SELECT   *
  FROM table
  WHEREEXTRACT(YEAR FROM datefield) = #year#
  AND  EXTRACT(MONTH FROM datefield) = #month#
  (If your database hasn't implemented SQL date functions the exact syntax
  might be different and you should naturally use cfqueryparam.)

  The question which approach is most efficient is highly dependent on the
  database you are using, and especially the index functionality. I would
  probably create some indexes and do timings on them. Specifically:
  CREATE INDEX test1_idx ON table (datefield);
  CREATE INDEX test2_idx ON table (EXTRACT(YEAR FROM datefield),
  EXTRACT(MONTH FROM datefield), calltype);
  If your database supports it, play around with index types (try an RTREE
  for test1_idx). But unless you have millions of records, speed
  difference is probably neglegible, and you should go for the strategy
  that uses the smallest index.

  Jochem
  
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm

Re: Another Newbie Problem

[Dina Hess]

Thank you for the clarification, Jochem. 

Actually, any attempt to apply the word standard to the many flavors of SQL probably 
*is* questionable. :) 

It may be more appropriate to state that the Month() and Year() functions are 
available for use in SQL Server's Transact-SQL, Oracle's PL/SQL, and MS Access's Jet 
SQL.

But I'm unfamiliar with SQL:1999. A quick check on google seems to indicate this is an 
attempt to provide a SQL standard. Care to comment further on that?

~Dina


- Original Message - 
  From: Jochem van Dieten 
  To: CF-Talk 
  Sent: Wednesday, December 11, 2002 10:49 AM
  Subject: Re: Another Newbie Problem


  Dina Hess wrote:
   Well, Jochem, has already given you some valuable information but since
   Month() and Year() are standard SQL functions

  Maybe they work in some non-standard implementation, but that does not 
  make them standard functions. There is no reference to a Month() or a 
  Year() function in SQL:1999.

  Jochem

  
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Your ad could be here. Monies from ads go to support these lists and provide more 
resources for the community. http://www.fusionauthority.com/ads.cfm

Re: CFOUTPUT group by recordcount HELP

[Dina Hess]

Recordcount isn't what you're after since it returns the total number of rows in the 
query recordset.

Instead you can get the number of records returned for each distinct category by 
requerying (or in CF5+ do a query of the original query)...something like this:

cfquery name=CatCount DATASOURCE=efds
  Select d.DomainCategoryID as catid, 
  count(d.DomainCategoryID) as num_cat_recs
  FROM DomainCategoryTbl c, DomainNamesTbl d
  WHERE c.DomainCategoryID=d.DomainCategoryID
  Group By d.DomainCategoryID
/cfquery

Then you could output it like this:

CFOUTPUT QUERY=GroupDomainCategories GROUP=DomainCategory

Category: #DomainCategory# - 

cfset thiscat = DomainCategoryID

cfloop query=CatCount
cfif catid eq thiscat
#num_cat_recs#br
/cfif
/cfloop

CFOUTPUT
#DomainName#BR
/CFOUTPUT

/CFOUTPUT
  - Original Message - 
  From: Tom Forbes 
  To: CF-Talk 
  Sent: Monday, December 09, 2002 9:28 PM
  Subject: CFOUTPUT group by  recordcount HELP


  I have checked my books and the CF achieve, but need a little help in the 
  right direction, please.

  I have a db table that contains, among other things, Domain Names and about 
  20 Different Domain Name Categories. I want to Output the results of this 
  db table by GROUPING the Domain Name Categories (I got that to work) and 
  next to the Category I would like to use the RecordCount parameter of the 
  Query to indicate how many Domain Names in each category. The problem with 
  the code below is that the RecordCount parameter returns the TOTAL number 
  of categories, not the Total Number of Categories for each particular 
  Domain Name.

  I would greatly appreciate any help. Thanks!

  I have:

  CFQUERY NAME=GroupDomainCategories DATASOURCE=efds
   SELECT *
   FROM DomainCategoryTbl,DomainNamesTbl
  WHERE DomainCategoryTbl.DomainCategoryID=DomainNamesTbl.DomainCategoryID
  ORDER By DomainCategory, DomainName
  /CFQUERY

  and for the output I have:

  CFOUTPUT QUERY=GroupDomainCategories GROUP=DomainCategory

  Category: #DomainCategory# - #GroupDomainCategories.RecordCount#

  CFOUTPUT
  #DomainName#BR
  /CFOUTPUT

  /CFOUTPUT

  
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Signup for the Fusion Authority news alert and keep up with the latest news in 
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm

Re: CFOUTPUT group by recordcount HELP

[Dina Hess]

Tom, you're welcome. My pleasure to be of assistance.

~Dina
  - Original Message - 
  From: Tom Forbes 
  To: CF-Talk 
  Sent: Tuesday, December 10, 2002 1:41 PM
  Subject: Re: CFOUTPUT group by  recordcount HELP


  Dear Dina Hess,

  Thank you very much for taking the time this morning to help me with this 
  Query/RecordCount problem. There is NO WAY I would have come to this 
  solution on my own. I have since read up on column aliases, aggregate 
  functions and loops so that I can understand how this works. I have tested 
  it on my db, and it runs great - no tweaking!

  You made it possible for me to get the problem solved, and I learned a lot 
  in the process.

  Thanks again for your excellent help, and have a very happy Holiday.

  Tom




  At 09:20 AM 12/10/02, you wrote:
  Recordcount isn't what you're after since it returns the total number of 
  rows in the query recordset.
  
  Instead you can get the number of records returned for each distinct 
  category by requerying (or in CF5+ do a query of the original 
  query)...something like this:
  
  cfquery name=CatCount DATASOURCE=efds
 Select d.DomainCategoryID as catid,
 count(d.DomainCategoryID) as num_cat_recs
 FROM DomainCategoryTbl c, DomainNamesTbl d
 WHERE c.DomainCategoryID=d.DomainCategoryID
 Group By d.DomainCategoryID
  /cfquery
  
  Then you could output it like this:
  
  CFOUTPUT QUERY=GroupDomainCategories GROUP=DomainCategory
  
  Category: #DomainCategory# -
  
  cfset thiscat = DomainCategoryID
  
  cfloop query=CatCount
   cfif catid eq thiscat
   #num_cat_recs#br
   /cfif
  /cfloop
  
  CFOUTPUT
  #DomainName#BR
  /CFOUTPUT
  
  /CFOUTPUT
 - Original Message -
 From: Tom Forbes
 To: CF-Talk
 Sent: Monday, December 09, 2002 9:28 PM
 Subject: CFOUTPUT group by  recordcount HELP
  
  
 I have checked my books and the CF achieve, but need a little help in the
 right direction, please.
  
 I have a db table that contains, among other things, Domain Names and 
   about
 20 Different Domain Name Categories. I want to Output the results of this
 db table by GROUPING the Domain Name Categories (I got that to work) and
 next to the Category I would like to use the RecordCount parameter of the
 Query to indicate how many Domain Names in each category. The problem with
 the code below is that the RecordCount parameter returns the TOTAL number
 of categories, not the Total Number of Categories for each particular
 Domain Name.
  
 I would greatly appreciate any help. Thanks!
  
 I have:
  
 CFQUERY NAME=GroupDomainCategories DATASOURCE=efds
  SELECT *
  FROM DomainCategoryTbl,DomainNamesTbl
 WHERE DomainCategoryTbl.DomainCategoryID=DomainNamesTbl.DomainCategoryID
 ORDER By DomainCategory, DomainName
 /CFQUERY
  
 and for the output I have:
  
 CFOUTPUT QUERY=GroupDomainCategories GROUP=DomainCategory
  
 Category: #DomainCategory# - #GroupDomainCategories.RecordCount#
  
 CFOUTPUT
 #DomainName#BR
 /CFOUTPUT
  
 /CFOUTPUT
  
  
  
  
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Your ad could be here. Monies from ads go to support these lists and provide more 
resources for the community. http://www.fusionauthority.com/ads.cfm

Re: Alternating Row Colours Number Listing in CFOutput

[Dina Hess]

You could also do it like this:

 tr
  CFIF latestlinks.currentrow MOD 2 is 1
   bgcolor=##DFDFDF
  CFELSE
   bgcolor=##FF
  /CFIF
 
which I think is slightly faster in CF5 or earlier.

Regarding DE, or Delay Evaluation, this is straight from CF5 docs:

DE prevents evaluation of a string as an expression when it is passed as an argument 
to IIf or Evaluate. 

  - Original Message - 
  From: charlie griefer 
  To: CF-Talk 
  Sent: Wednesday, November 27, 2002 11:23 AM
  Subject: Re: Alternating Row Colours  Number Listing in CFOutput


  Cutter (CF_Talk) writes: 

   Could you please explain what the following statement does? 
   
   ###iif(currentrow mod 2, DE('DFDFDF'), DE('FF'))#

  actually, i'd appreciate an explanation as well.  I understand the 
  IIF()...but i never really understood DE()...what exactly it does or why it 
  makes the world a better place :) 

  thx.
  charlie
  
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm

Re: Need help with missing submit buttons

[Dina Hess]

When you're laying out a page, it's often best to get the HTML right before adding 
functionality. The HTML Validator at http://www.w3c.org can help you locate missing 
tr tags and the like. 
  - Original Message - 
  From: Joshua Miller 
  To: CF-Talk 
  Sent: Wednesday, November 27, 2002 3:38 PM
  Subject: Re: Need help with missing submit buttons


  This may not be the problem, but the bottom TR has only 2 TDs that  
  contain buttons  the TR above that has 3 TDs

  On Wednesday, November 27, 2002, at 03:49 PM, Clark, Aimee wrote:

   Ok, for some reason, my submit buttons are not showing up at the  
   bottom of
   my page. This is the code for the page in question. Can someone tell  
   me why
   this is happening?
  
   !DOCTYPE HTML PUBLIC -//W3C//DTD HTML 4.0 Transitional//EN
   html
   head
   titleConflicts Check Request Continued/title
   /head
  
   cfif IsDefined(Conflict_ID)
   cfquery datasource=Conflicts name=GetConflictID
   Select Conflict_ID from ConflictsCheck
   where (Conflict_ID=#Conflict_ID#)
   /cfquery
   cfelse
   cfquery datasource=Conflicts name=GetConflictID
   Select Conflict_ID from ConflictsCheck
   where (SubmitterEmail='#SubmitterEmail#' AND
   EntryDateTime=#EntryDateTime#)
   /cfquery
   /cfif
  
   body text=green
   table
   td width=25%a href=HelpDeskFrame.htm target=_parent  
   title=Return
   to Daily News Home Pageimg src=../images/SMH3.jpg border=0
   alt=Return to Firm Daily News width=200 height=100/a/td
   tddiv align=centerfont color=CAh2Conflicts Check  
   Request
   Continued/h2/font/div/td
   /table
  
   div align=centerh4 align=centerfont color=blackIf you need  
   to
   enter more than the allotted fields provided please choose Add More at  
   the
   bottom of the page./h4/font/div
  
   cfform action= OtherParties.cfm method= post
   table width=100% border=1 bgcolor=#00
   tr
   tdstrong(#1)  Name of Adverse Party:/strong/td
   tdcfinput type=Text name=AdverseParty1 required=Yes  
   size=66/td
   /tr
   tr
   tdstrong (#1)  Address of Adverse Party:/strong/td
   tdtextarea cols=50 rows=3  
   name=AdverseAddress1/textarea/td
   /tr
   /table
   table width=100% border=1 bgcolor=#80
   tdstrong(#2)  Name of Adverse Party:/strong/td
   tdcfinput type=Text name=AdverseParty2 required=No  
   size=66/td
   /tr
   tr
   tdstrong(#2)  Address of Adverse Party:/strong/td
   tdtextarea cols=50 rows=3  
   name=AdverseAddress2/textarea/td
   /tr
   /table
   table
   tr
   tdinput type=hidden name=SubmitterEmail
   value=cfoutput#SubmitterEmail#/cfoutput/td
   tdinput type=hidden name=EntryDateTime
   value=cfoutput#EntryDateTime#/cfoutput/td
   tdinput type=hidden name=Conflict_ID
   value=cfoutput#Conflict_ID#/cfoutput/td
   /tr
   tr
   td align=leftinput type=submit name=Operation value=Add
   More/td
   td align=rightinput type=submit name=Operation
   value=Continue/td
   /tr
   /table
   /body
   /cfform
   /html
  
   Thank you,
   Aimee' Clark
   
  
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Get the mailserver that powers this list at http://www.coolfusion.com

Re: Using a counter variable

[Dina Hess]

Aimee,

This works for me:

!--- application.cfm ---
cfapplication name=myapp

CFIF NOT IsDefined(APPLICATION.Counter)
 CFLOCK TYPE=Exclusive SCOPE=application TIMEOUT=15
  CFSET APPLICATION.Counter = 0
 /CFLOCK
/CFIF

!--- form.cfm ---
form name=frmTicket action=frm_action.cfm method=post
input type=submit value=Save Ticket
/form

!--- action.cfm ---

!--- Increment counter ---
 CFLOCK TYPE=Exclusive SCOPE=application TIMEOUT=15
  CFSET APPLICATION.Counter = APPLICATION.Counter + 1
 /CFLOCK

!--- Get counter value ---
CFLOCK TYPE=ReadOnly SCOPE=application TIMEOUT=15
 CFSET Counter = APPLICATION.Counter
/CFLOCK

CFIF Counter MOD 7 EQ 0
!--- send survey email ---
 Counter = cfoutput#counter#/cfoutputbr
 a href=Survey Link/a
CFELSE
 Counter = cfoutput#counter#/cfoutput
/CFIF

~Dina

  - Original Message - 
  From: Clark, Aimee 
  To: CF-Talk 
  Sent: Tuesday, November 26, 2002 4:42 PM
  Subject: RE: Using a counter variable


  Mosh,
  I tried this and it did work, but, it's still showing my survey link
  even though the counter has been set back to 0. Can you tell why?

  cflock type=READONLY name=lckReadCounter timeout=20
  cfset counter = application.counter
  /cflock

  cfif application.counter mod 7 eq 0
  cflock type=EXCLUSIVE name=lckWriteCounter timeout=20
  cfset application.counter = 0
  /cflock
  cfmail query = GetCall to=[EMAIL PROTECTED]
  from=[EMAIL PROTECTED]
  subject=Closed Help Desk Ticket
  type=HTMLbody link=red alink=red vlink=red
  Your Help Desk call (Ticket No. #GetCall.ID#) has been
  closed.  If you believe the call should not be closed, or the problem occurs
  again, please reply to this message or call the Help Desk, to let us know.
  P
  div align=centerH3Call Information/h3/divP
  h4bfont color=RedTicket Number:/font/b  #GetCall.ID#P
  bfont color=RedProblem Description:/font/b  BR
  #GetCall.Issue#P
  bfont color=RedResolution:/font/b BR
  #GetCall.Resolution#brbr
  P/h4/body
  divfont color=Red/fonta
  href=http://dailynews/helpdesk/test/HDSurvey.cfm?ID=#ID#;
  target=_blankClick here to answer a brief survey about your issue and
  resolution./a/font/div
  p#counter#/p
  /cfmail

  cfelse
  cflock type=EXCLUSIVE name=lckWriteCounter timeout=20
  cfset application.counter = application.counter + 1
  /cflock
  cfmail query = GetCall to=[EMAIL PROTECTED]
  from=[EMAIL PROTECTED]
  subject=Closed Help Desk Ticket
  type=HTMLbody link=red alink=red vlink=red
  Your Help Desk call (Ticket No. #GetCall.ID#) has been
  closed.  If you believe the call should not be closed, or the problem occurs
  again, please reply to this message or call the Help Desk, to let us know.
  P
  div align=centerH3Call Information/h3/divP
  h4bfont color=RedTicket Number:/font/b  #GetCall.ID#P
  bfont color=RedProblem Description:/font/b  BR
  #GetCall.Issue#P
  bfont color=RedResolution:/font/b BR
  #GetCall.Resolution#brbr
  P/h4/body
  p#counter#/p
  /cfmail
  /cfif

  Thank you,
  Aimee' Clark

  -Original Message-
  From: Mosh Teitelbaum [mailto:[EMAIL PROTECTED]]
  Sent: Tuesday, November 26, 2002 3:41 PM
  To: CF-Talk
  Subject: RE: Using a counter variable


  You haven't made your counter variable persistent.  Every time someone makes
  a new request of your application, Application.cfm creates a new variable
  called counter and sets it equal to 0.  You need to make your variable
  persistent.  And since you want it to work across multiple users, it should
  probably persist in the APPLICATION scope.

  So, your Application.cfm should look something like:

  CFIF NOT IsDefined(APPLICATION.Counter)
  CFLOCK TYPE=Exclusive NAME=lckCreateCounter
  CFSET APPLICATION.Counter = 0
  /CFLOCK
  /CFIF

  Your action page should look something like:

  !--- Get counter value ---
  CFLOCK TYPE=ReadOnly NAME=lckReadCounter
  CFSET Counter = APPLICATION.Counter
  /LOCK

  CFIF Counter EQ 6
  !--- Increment counter ---
  CFLOCK TYPE=Exclusive NAME=lckWriteCounter
  CFSET APPLICATION.Counter = 0
  /CFLOCK

  !--- * show survey code * ---
  CFELSE
  !--- Increment counter ---
  CFLOCK TYPE=Exclusive NAME=lckWriteCounter
  CFSET APPLICATION.Counter = APPLICATION.Counter +
  1
  /CFLOCK
  /CFIF


  --
  Mosh Teitelbaum
  evoch, LLC
  Tel: (301) 625-9191
  Fax: (301) 933-3651
  Email: [EMAIL PROTECTED]
  WWW: http://www.evoch.com/


   -Original Message-
   From: Clark, Aimee [mailto:[EMAIL PROTECTED]]
   Sent: Tuesday, November 26, 2002 4:03 PM
   To: CF-Talk
   Subject: Using a counter variable
  
  
   I have an application in which I want every 7th record closed in
   a table to
   generate an email asking the user to participate in a survey.
  
   I have cfset counter = 0 in the application.cfm file.
  
   I have cfset counter = counter +1 in my action page right after
   an update
   that is done to the table.
  
   Then I have cfif counter EQ 7
   execute generating email
   then it resets the counter back to 0
  
   However my counter is 

Re: Database design question

[Dina Hess]

To refine a bit...

STUDENT(student_id [PK], student_name, ...)
GOAL(goal_id [PK], student_id [FK], goal_name, ...)
GRADE(grade_id [PK], goal_id [FK], grading_period, grade)

This reflects the one-to-many relationships---

** One student has many goals
** One goal has many grades

~Dina


- Original Message - 
  From: Bruce, Rodney S HQISEC/Veridian IT Services 
  To: CF-Talk 
  Sent: Thursday, November 21, 2002 3:34 PM
  Subject: RE: Database design question


  Janine

  one question:  would the goals be the same for all the students?

  I would go with 3 tables

  1.  Student info
  2. Goal info
  3.  link table with fk from student/goal tables and comments(grades) 

  something like

  Table1

  Studentid  StudentName 


  Table2
  Goalid GoalName 


  Table3
  Studentid  goalid   period(date)  Comments(grades)


  just my .02

   

  -Original Message-
  From: Janine Jakim [mailto:[EMAIL PROTECTED]]
  Sent: Thursday, November 21, 2002 1:59 PM
  To: CF-Talk
  Subject: OT: Database design question


  hmmm- I am very into keepting my database relational, but am wondering what
  the best route for thi particular issue is...
  Heres' the scoop 
  1.  One student can be working on many goals so I have 1 one to many
  relationships.
  2.  Each goal needs graded every marking period (up to 6 times a year)-
  these are not traditioanl grades but a long comments piece) So goals have a
  one to many relationship with the grades.
  I'm thinking the best way to handle it is to break it into 3 tables
  table one student info 
  table 2 all the goals for the students hooked to students with the studentid
  table 3 all the grades for the goals hooked to goals by goalID

  My other option is to have 2 tables
  table one student info
  table two goals +  6 grading periods comment sections
  Thanks in advance for any input.
  j




 

  
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Signup for the Fusion Authority news alert and keep up with the latest news in 
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm

Re: Reading database and inserting fields into another database

[Dina Hess]

 cfset commalist = valuelist(GetAlias.env_var)
 cfquery name=Insert datasource=data2 dbtype=ODBC
 INSERT  into profs
 (strField1)
 VALUES
 ('#commalist#')
 WHERE strUsername=cfqueryparam value=#auth#
cfsqltype=CF_SQL_VARCHAR
 /cfquery

My apologies for the late response to this thread, but I couldn't
help noticing the WHERE clause in this INSERT statement. If there
is already a value in strUsername, then an UPDATE statement
should be used rather than an INSERT.

~Dina

~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Get the mailserver that powers this list at http://www.coolfusion.com

Re: Return a Sub-Query as a Comma Delimited List

[Dina Hess]

 DECLARE EmployeeList varchar(100)

 SELECT EmployeeList = COALESCE(EmployeeList + ',', '') +
CAST(Emp_UniqueID AS varchar(5))
 FROM SalesCallsEmployees
 WHERE SalCal_UniqueID = 1

 SELECT EmployeeList

I ran that code snippet through the SQL Query Analyzer after
customizing it for the pubs database and got this:


~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Get the mailserver that powers this list at http://www.coolfusion.com