RE: [KCFusion] Simple Query Question
I am having a problem with the cfset hlist = replace(form.value2, , , ',' , all) Statementfor some reason it is converting the ' to when I insert it in my IN ( ' #hlist# ' ) statement...any ideas why? Christopher V. Holdman Webmaster - City of Olathe (913) 971-6286 (913) 238-4681 Cell [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, March 14, 2003 8:29 AM To: [EMAIL PROTECTED] Subject: RE: [KCFusion] Simple Query Question Depend on what the data type of the field you are calling from the table. If it is a numeric field you will not need to have any ' around the numeric values, but if the data type is varchar / char then you will have to have ' around the string. varchar/char select * from table where field1='value1' AND field2 IN ( 'a','b','c') select * from table where field1='value1' AND field2 IN (1,2,3,4) Chris Holdman [EMAIL PROTECTED] To: '[EMAIL PROTECTED]' [EMAIL PROTECTED] KS.ORGcc: Sent by: Subject: RE: [KCFusion] Simple Query Question [EMAIL PROTECTED] fusion.org 03/13/03 04:35 PM Please respond to CF-List I am not receiving any errors but I am not receiving any records and when I do a record count I get 0. Also, why couln;t I just skip the cfset and put #form.value2# instead of #hlist#. Christopher V. Holdman Webmaster - City of Olathe (913) 971-6286 (913) 238-4681 Cell [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, March 13, 2003 4:11 PM To: [EMAIL PROTECTED] Subject: Re: [KCFusion] Simple Query Question not setup exactly like you have it but you can use the IN sql statment with your list of variables it does not require you to split it up cfset hlist = replace(form.value2, , , ',' , all) cfquery name=query datasource=data select * from table where field1='value1' AND field2 IN ( ' #hlist# ' ) /cfquery Chris Holdman [EMAIL PROTECTED] To: '[EMAIL PROTECTED]' [EMAIL PROTECTED] KS.ORGcc: Sent by: Subject: [KCFusion] Simple Query Question [EMAIL PROTECTED] fusion.org 03/13/03 03:58 PM Please respond to CF-List I have a form with a multiple select box that I am want to build a query from, but am having problems. I set up an index loop with an OR statement, but I have obviously set it up wrong beacuase it is adding an OR to the end of the query. How do I set it up to not put an OR after the last index? cfquery name=query datasource=data select * from table where field1='value1' AND ( cfloop index=index list=#form.value2# delimiters=, field2='#index#' OR /cfloop ) /cfquery Christopher V. Holdman Webmaster - City of Olathe (913) 971-6286 (913) 238-4681 Cell [EMAIL PROTECTED] -Original Message- From: Dunwiddie, Bruce [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 11, 2003 3:16 PM To: '[EMAIL PROTECTED]' Subject: RE: [KCFusion] Printer Friendly Directory Listing style=page-break-after: always if you say apply that to a table tag, and then have multiple tables each having that style, it will have a page break between them. of course, I'm sure it's only ie, but better than nothing. -Original Message- From: Chris Holdman [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 11, 2003 3:03 PM To: '[EMAIL PROTECTED]' Subject: [KCFusion] Printer Friendly Directory Listing I have a directory listing pulling from a database and sorting by departments. Everything work great online, but some people are wanting to print it out...when they do sometimes the page break for printing occurs in the middle of a department listing and the second page does not have the department title. Is there a way to determine where a printing page break will occur and force the follwoing page to repeat the department title. Below is an example of what I mean and the code I currently use. Department A address phone # - name phone # - name phone # - name Department B address phone # - name phone # - name phone # - name phone # - name phone # - name phone # - name phone # - name phone
RE: [KCFusion] Simple Query Question
That worked Thanks for all the help. Christopher V. Holdman Webmaster - City of Olathe (913) 971-6286 (913) 238-4681 Cell [EMAIL PROTECTED] -Original Message- From: Benjamin J. Price [mailto:[EMAIL PROTECTED] Sent: Friday, March 14, 2003 9:12 AM To: [EMAIL PROTECTED] Subject: Re: [KCFusion] Simple Query Question Try IN ( ' #PreserveSingleQuotes(hlist)# ' ) -Ben Price Chris Holdman writes: I am having a problem with the cfset hlist = replace(form.value2, , , ',' , all) Statementfor some reason it is converting the ' to when I insert it in my IN ( ' #hlist# ' ) statement...any ideas why? Christopher V. Holdman Webmaster - City of Olathe (913) 971-6286 (913) 238-4681 Cell [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, March 14, 2003 8:29 AM To: [EMAIL PROTECTED] Subject: RE: [KCFusion] Simple Query Question Depend on what the data type of the field you are calling from the table. If it is a numeric field you will not need to have any ' around the numeric values, but if the data type is varchar / char then you will have to have ' around the string. varchar/char select * from table where field1='value1' AND field2 IN ( 'a','b','c') select * from table where field1='value1' AND field2 IN (1,2,3,4) Chris Holdman [EMAIL PROTECTED] To: '[EMAIL PROTECTED]' [EMAIL PROTECTED] KS.ORGcc: Sent by: Subject: RE: [KCFusion] Simple Query Question [EMAIL PROTECTED] fusion.org 03/13/03 04:35 PM Please respond to CF-List I am not receiving any errors but I am not receiving any records and when I do a record count I get 0. Also, why couln;t I just skip the cfset and put #form.value2# instead of #hlist#. Christopher V. Holdman Webmaster - City of Olathe (913) 971-6286 (913) 238-4681 Cell [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, March 13, 2003 4:11 PM To: [EMAIL PROTECTED] Subject: Re: [KCFusion] Simple Query Question not setup exactly like you have it but you can use the IN sql statment with your list of variables it does not require you to split it up cfset hlist = replace(form.value2, , , ',' , all) cfquery name=query datasource=data select * from table where field1='value1' AND field2 IN ( ' #hlist# ' ) /cfquery Chris Holdman [EMAIL PROTECTED] To: '[EMAIL PROTECTED]' [EMAIL PROTECTED] KS.ORGcc: Sent by: Subject: [KCFusion] Simple Query Question [EMAIL PROTECTED] fusion.org 03/13/03 03:58 PM Please respond to CF-List I have a form with a multiple select box that I am want to build a query from, but am having problems. I set up an index loop with an OR statement, but I have obviously set it up wrong beacuase it is adding an OR to the end of the query. How do I set it up to not put an OR after the last index? cfquery name=query datasource=data select * from table where field1='value1' AND ( cfloop index=index list=#form.value2# delimiters=, field2='#index#' OR /cfloop ) /cfquery Christopher V. Holdman Webmaster - City of Olathe (913) 971-6286 (913) 238-4681 Cell [EMAIL PROTECTED] -Original Message- From: Dunwiddie, Bruce [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 11, 2003 3:16 PM To: '[EMAIL PROTECTED]' Subject: RE: [KCFusion] Printer Friendly Directory Listing style=page-break-after: always if you say apply that to a table tag, and then have multiple tables each having that style, it will have a page break between them. of course, I'm sure it's only ie, but better than nothing. -Original Message- From: Chris Holdman [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 11, 2003 3:03 PM To: '[EMAIL PROTECTED]' Subject: [KCFusion] Printer Friendly Directory Listing I have a directory listing pulling from a database and sorting by departments. Everything work great online, but some people are wanting to print it out...when they do sometimes the page break for printing occurs in the middle of a department
[KCFusion] Simple Query Question
Title: Message I have a form with a multiple select box that I am want to build a query from, but am having problems. I set up an index loop with an OR statement, but I have obviously set it up wrong beacuase it is adding an OR to the end of the query. How do I set it up to not put an OR after the last index? cfquery name="query" datasource="data"select * fromtable wherefield1='value1' AND ( cfloop index="index" list="#form.value2#" delimiters="," field2='#index#' OR /cfloop )/cfquery Christopher V. Holdman Webmaster - City of Olathe (913) 971-6286 (913) 238-4681 Cell [EMAIL PROTECTED] -Original Message-From: Dunwiddie, Bruce [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 11, 2003 3:16 PMTo: '[EMAIL PROTECTED]'Subject: RE: [KCFusion] Printer Friendly Directory Listing if you say apply that to a table tag, and then have multiple tables each having that style, it will have a page break between them. of course, I'm sure it's only ie, but better than nothing. -Original Message-From: Chris Holdman [mailto:[EMAIL PROTECTED]Sent: Tuesday, March 11, 2003 3:03 PMTo: '[EMAIL PROTECTED]'Subject: [KCFusion] Printer Friendly Directory Listing I have a directory listing pulling from a database and sorting by departments. Everything work great online, but some people are wanting to print it out...when they do sometimes the page break for printing occurs in the middle of a department listing and the second page does not have the department title. Is there a way to determine where a printing page break will occur and force the follwoing page to repeat the department title. Below is an example of what I mean and the code I currently use. Department Aaddressphone # - namephone # - namephone # - name Department Baddressphone # - namephone # - namephone # - namephone # - namephone # - namephone # - namephone # - namephone # - namephone # - name [page break]Department Baddressphone # - namephone # - namephone # - name DepartmentCaddressphone # - namephone # - namephone # - name cfoutput query="list" group="Department" bfont size="+1"#Department#/font/bbr #building#br hr align="left" width="300" cfoutput #Ext# - b#Name#/bbr /cfoutput p/cfoutput Christopher V. Holdman Webmaster - City of Olathe (913) 971-6286 (913) 238-4681 Cell [EMAIL PROTECTED]
RE: [KCFusion] Simple Query Question
This should do the trick cfloop from=3D1 to=3D#listLen(form.value2)# index=3Di field2=3D'#listGetAt(form.value2,i)#' cfif i lt listLen()OR/cfif /cfloop Or cfset locList2OR =3D field2=3D' replace(form.value2,,,' OR = field2=3D' ' cfquery name=3Dquery datasource=3Ddata select * from table where field1=3D'value1' AND (#preservesinglequotes(locList2OR)#) /cfquery Or Now its your turn Matt... :) Justin Hansen ~~ Uhlig Communications Systems Engineer ~~ [EMAIL PROTECTED] 913-754-4273 Office 816-695-4045 Mobile ~~ -Original Message- From: Chris Holdman [mailto:[EMAIL PROTECTED] Sent: Thursday, March 13, 2003 3:59 PM To: '[EMAIL PROTECTED]' Subject: [KCFusion] Simple Query Question I have a form with a multiple select box that I am want to build a query from, but am having problems. I set up an index loop with an OR statement, but I have obviously set it up wrong beacuase it is adding an OR to the end of the query. How do I set it up to not put an OR after the last index? cfquery name=query datasource=data select * from table where field1='value1' AND ( cfloop index=index list=#form.value2# delimiters=, field2='#index#' OR /cfloop ) /cfquery Christopher V. Holdman Webmaster - City of Olathe (913) 971-6286 (913) 238-4681 Cell [EMAIL PROTECTED] -Original Message- From: Dunwiddie, Bruce [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 11, 2003 3:16 PM To: '[EMAIL PROTECTED]' Subject: RE: [KCFusion] Printer Friendly Directory Listing style=page-break-after: always if you say apply that to a table tag, and then have multiple tables each having that style, it will have a page break between them. of course, I'm sure it's only ie, but better than nothing. -Original Message- From: Chris Holdman [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 11, 2003 3:03 PM To: '[EMAIL PROTECTED]' Subject: [KCFusion] Printer Friendly Directory Listing I have a directory listing pulling from a database and sorting by departments. Everything work great online, but some people are wanting to print it out...when they do sometimes the page break for printing occurs in the middle of a department listing and the second page does not have the department title. Is there a way to determine where a printing page break will occur and force the follwoing page to repeat the department title. Below is an example of what I mean and the code I currently use. Department A address phone # - name phone # - name phone # - name Department B address phone # - name phone # - name phone # - name phone # - name phone # - name phone # - name phone # - name phone # - name phone # - name [page break] Department B address phone # - name phone # - name phone # - name Department C address phone # - name phone # - name phone # - name cfoutput query=list group=Department bfont size=+1#Department#/font/bbr #building#br hr align=left width=300 cfoutput #Ext# - b#Name#/bbr /cfoutput p /cfoutput Christopher V. Holdman Webmaster - City of Olathe (913) 971-6286 (913) 238-4681 Cell [EMAIL PROTECTED] __ The KCFusion.org list and website is hosted by Humankind Systems, Inc. List Archives http://www.mail-archive.com/[EMAIL PROTECTED] Questions, Comments or Glowing Praise.. mailto:[EMAIL PROTECTED] To Subscribe mailto:[EMAIL PROTECTED] To Unsubscribe mailto:[EMAIL PROTECTED]
Re: [KCFusion] Simple Query Question
You could add a boolean variable and then use it to check to see if it's the first time through, if it is don't use the OR, otherwise add the OR before you add the next variable. Might be able to use the IN clause as well instead of hte OR. cfset firsttime='True' cfquery name=query datasource=data select * from table where field1='value1' AND ( cfloop index=index list=#form.value2# delimiters=, cfif #firsttime# = 'True' !--- First time through, don't add OR --- cfset firsttime = 'False' cfelse !--- Second, third, fourth, etc time through add OR before variable --- OR /cfif field2='#index#' /cfloop ) /cfquery Chris --- Chris Holdman [EMAIL PROTECTED] wrote: I have a form with a multiple select box that I am want to build a query from, but am having problems. I set up an index loop with an OR statement, but I have obviously set it up wrong beacuase it is adding an OR to the end of the query. How do I set it up to not put an OR after the last index? cfquery name=query datasource=data select * from table where field1='value1' AND ( cfloop index=index list=#form.value2# delimiters=, field2='#index#' OR /cfloop ) /cfquery Christopher V. Holdman Webmaster - City of Olathe (913) 971-6286 (913) 238-4681 Cell [EMAIL PROTECTED] -Original Message- From: Dunwiddie, Bruce [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 11, 2003 3:16 PM To: '[EMAIL PROTECTED]' Subject: RE: [KCFusion] Printer Friendly Directory Listing style=page-break-after: always if you say apply that to a table tag, and then have multiple tables each having that style, it will have a page break between them. of course, I'm sure it's only ie, but better than nothing. -Original Message- From: Chris Holdman [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 11, 2003 3:03 PM To: '[EMAIL PROTECTED]' Subject: [KCFusion] Printer Friendly Directory Listing I have a directory listing pulling from a database and sorting by departments. Everything work great online, but some people are wanting to print it out...when they do sometimes the page break for printing occurs in the middle of a department listing and the second page does not have the department title. Is there a way to determine where a printing page break will occur and force the follwoing page to repeat the department title. Below is an example of what I mean and the code I currently use. Department A address phone # - name phone # - name phone # - name Department B address phone # - name phone # - name phone # - name phone # - name phone # - name phone # - name phone # - name phone # - name phone # - name [page break] Department B address phone # - name phone # - name phone # - name Department C address phone # - name phone # - name phone # - name cfoutput query=list group=Department bfont size=+1#Department#/font/bbr #building#br hr align=left width=300 cfoutput #Ext# - b#Name#/bbr /cfoutput p /cfoutput Christopher V. Holdman Webmaster - City of Olathe (913) 971-6286 (913) 238-4681 Cell [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Web Hosting - establish your business online http://webhosting.yahoo.com __ The KCFusion.org list and website is hosted by Humankind Systems, Inc. List Archives http://www.mail-archive.com/[EMAIL PROTECTED] Questions, Comments or Glowing Praise.. mailto:[EMAIL PROTECTED] To Subscribe mailto:[EMAIL PROTECTED] To Unsubscribe mailto:[EMAIL PROTECTED]
RE: [KCFusion] Simple Query Question
yeah, they are right, the 'IN' statement is the way to go geez, what the heck was i thinkin? ;P -Original Message- From: Justin Hansen Sent: Thursday, March 13, 2003 4:24 PM To: [EMAIL PROTECTED] Subject: RE: [KCFusion] Simple Query Question oops.. correction, i dont know how all the 3D's came from anyway. This should do the trick cfloop from=1 to=#listLen(form.value2)# index=i field2='#listGetAt(form.value2,i)#' cfif i lt listLen()OR/cfif /cfloop Or cfset locList2OR =field2=' replace(form.value2,,,' OR = field2=' ' cfquery name=query datasource=data select * from table where field1='value1' AND (#preservesinglequotes(locList2OR)#) /cfquery Or Now its your turn Matt... :) Justin Hansen ~~ Uhlig Communications Systems Engineer ~~ [EMAIL PROTECTED] 913-754-4273 Office 816-695-4045 Mobile ~~ -Original Message- From: Chris Holdman [mailto:[EMAIL PROTECTED] Sent: Thursday, March 13, 2003 3:59 PM To: '[EMAIL PROTECTED]' Subject: [KCFusion] Simple Query Question I have a form with a multiple select box that I am want to build a query from, but am having problems. I set up an index loop with an OR statement, but I have obviously set it up wrong beacuase it is adding an OR to the end of the query. How do I set it up to not put an OR after the last index? cfquery name=query datasource=data select * from table where field1='value1' AND ( cfloop index=index list=#form.value2# delimiters=, field2='#index#' OR /cfloop ) /cfquery Christopher V. Holdman Webmaster - City of Olathe (913) 971-6286 (913) 238-4681 Cell [EMAIL PROTECTED] -Original Message- From: Dunwiddie, Bruce [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 11, 2003 3:16 PM To: '[EMAIL PROTECTED]' Subject: RE: [KCFusion] Printer Friendly Directory Listing style=page-break-after: always if you say apply that to a table tag, and then have multiple tables each having that style, it will have a page break between them. of course, I'm sure it's only ie, but better than nothing. -Original Message- From: Chris Holdman [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 11, 2003 3:03 PM To: '[EMAIL PROTECTED]' Subject: [KCFusion] Printer Friendly Directory Listing I have a directory listing pulling from a database and sorting by departments. Everything work great online, but some people are wanting to print it out...when they do sometimes the page break for printing occurs in the middle of a department listing and the second page does not have the department title. Is there a way to determine where a printing page break will occur and force the follwoing page to repeat the department title. Below is an example of what I mean and the code I currently use. Department A address phone # - name phone # - name phone # - name Department B address phone # - name phone # - name phone # - name phone # - name phone # - name phone # - name phone # - name phone # - name phone # - name [page break] Department B address phone # - name phone # - name phone # - name Department C address phone # - name phone # - name phone # - name cfoutput query=list group=Department bfont size=+1#Department#/font/bbr #building#br hr align=left width=300 cfoutput #Ext# - b#Name#/bbr /cfoutput p /cfoutput Christopher V. Holdman Webmaster - City of Olathe (913) 971-6286 (913) 238-4681 Cell [EMAIL PROTECTED] __ The KCFusion.org list and website is hosted by Humankind Systems, Inc. List Archives http://www.mail-archive.com/[EMAIL PROTECTED] Questions, Comments or Glowing Praise.. mailto:[EMAIL PROTECTED] To Subscribe mailto:[EMAIL PROTECTED] To Unsubscribe mailto:[EMAIL PROTECTED] __ The KCFusion.org list and website is hosted by Humankind Systems, Inc. List Archives http://www.mail-archive.com/[EMAIL PROTECTED] Questions, Comments or Glowing Praise.. mailto:[EMAIL PROTECTED] To Subscribe mailto:[EMAIL PROTECTED] To Unsubscribe mailto:[EMAIL PROTECTED] __ The KCFusion.org list and website is hosted by Humankind Systems, Inc. List Archives http://www.mail-archive.com/[EMAIL PROTECTED] Questions, Comments or Glowing Praise.. mailto:[EMAIL PROTECTED] To Subscribe mailto:[EMAIL PROTECTED] To Unsubscribe mailto:[EMAIL PROTECTED]
