problem on cfoutput query syntax too complex for cfmx
Hi everybody need help on the following: I have two cfquery which are cfquery name=english datasource=db select * from lang where langid=1 /cfquery cfquery name=spanish datasource=db select * from lang where langid=2 /cfquery cfparam name=Session.Lang default=1 CFSWITCH expression=#Session.Lang# cfcase Value=1 cfoutput cfset qtext=#english# /cfoutput /cfcase cfcase Value=2 cfoutput cfset qtext=#spanish# /cfoutput /cfcase /cfswitch cfoutput query=#qtext# #field1# /cfoutput the error is coming from cfoutput query which says Complex object types cannot be converted to simple values. IS THERE A WAY TO POST THE QUERY DYNAMICALLY IN THE CFOUTPUT I HAVE BEEN TRYING MANY METHODS BUT COULDN'T GET IT THANKS ~| 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:262683 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: problem on cfoutput query syntax too complex for cfmx
scrap the hashes. cfset qtext=spanish cfoutput query=qtext On 04/12/06, alex poyaoan [EMAIL PROTECTED] wrote: Hi everybody need help on the following: I have two cfquery which are cfquery name=english datasource=db select * from lang where langid=1 /cfquery cfquery name=spanish datasource=db select * from lang where langid=2 /cfquery cfparam name=Session.Lang default=1 CFSWITCH expression=#Session.Lang# cfcase Value=1 cfoutput cfset qtext=#english# /cfoutput /cfcase cfcase Value=2 cfoutput cfset qtext=#spanish# /cfoutput /cfcase /cfswitch cfoutput query=#qtext# #field1# /cfoutput the error is coming from cfoutput query which says Complex object types cannot be converted to simple values. IS THERE A WAY TO POST THE QUERY DYNAMICALLY IN THE CFOUTPUT I HAVE BEEN TRYING MANY METHODS BUT COULDN'T GET IT THANKS ~| 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:262685 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: problem on cfoutput query syntax too complex for cfmx
I think you'll find that that may need to be: cfset qtext=spanish cfoutput query=#qtext# You will need the hashes around 'qtext' for coldfusion to resolve it in to the desired name of your query (i.e. English or Spanish) Regards Rich On 12/4/06, Adrian [EMAIL PROTECTED] wrote: scrap the hashes. cfset qtext=spanish cfoutput query=qtext On 04/12/06, alex poyaoan [EMAIL PROTECTED] wrote: Hi everybody need help on the following: I have two cfquery which are cfquery name=english datasource=db select * from lang where langid=1 /cfquery cfquery name=spanish datasource=db select * from lang where langid=2 /cfquery cfparam name=Session.Lang default=1 CFSWITCH expression=#Session.Lang# cfcase Value=1 cfoutput cfset qtext=#english# /cfoutput /cfcase cfcase Value=2 cfoutput cfset qtext=#spanish# /cfoutput /cfcase /cfswitch cfoutput query=#qtext# #field1# /cfoutput the error is coming from cfoutput query which says Complex object types cannot be converted to simple values. IS THERE A WAY TO POST THE QUERY DYNAMICALLY IN THE CFOUTPUT I HAVE BEEN TRYING MANY METHODS BUT COULDN'T GET IT THANKS ~| 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:262686 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: problem on cfoutput query syntax too complex for cfmx
And if we are being syntactically correct, you'll need the quotes around spanish... Actually. Apologies here... i am half asleep still and have only just finished the coffee... Adrian is completely correct another Americano please On 12/4/06, RichL [EMAIL PROTECTED] wrote: I think you'll find that that may need to be: cfset qtext=spanish cfoutput query=#qtext# You will need the hashes around 'qtext' for coldfusion to resolve it in to the desired name of your query (i.e. English or Spanish) Regards Rich On 12/4/06, Adrian [EMAIL PROTECTED] wrote: scrap the hashes. cfset qtext=spanish cfoutput query=qtext On 04/12/06, alex poyaoan [EMAIL PROTECTED] wrote: Hi everybody need help on the following: I have two cfquery which are cfquery name=english datasource=db select * from lang where langid=1 /cfquery cfquery name=spanish datasource=db select * from lang where langid=2 /cfquery cfparam name=Session.Lang default=1 CFSWITCH expression=#Session.Lang# cfcase Value=1 cfoutput cfset qtext=#english# /cfoutput /cfcase cfcase Value=2 cfoutput cfset qtext=#spanish# /cfoutput /cfcase /cfswitch cfoutput query=#qtext# #field1# /cfoutput the error is coming from cfoutput query which says Complex object types cannot be converted to simple values. IS THERE A WAY TO POST THE QUERY DYNAMICALLY IN THE CFOUTPUT I HAVE BEEN TRYING MANY METHODS BUT COULDN'T GET IT THANKS ~| 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:262687 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: problem on cfoutput query syntax too complex for cfmx
scrap the hashes. cfset qtext=spanish cfoutput query=qtext On 04/12/06, alex poyaoan [EMAIL PROTECTED] wrote: THANKS A LOT ADRIAN THE CODE NOW WORKS.. ~| 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:262688 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Can anyone spot a problem with this query?
MySQL is giving me an ODBC Error Code = 23000 (Integrity contraint violation) and says I have an error in my SQL syntax...I can't find it...data being sent from form fields has been tested an appear to be in proper form...I can't figure out why it's getting heartburn over this query... ??? Thanks, Rick CFQUERY Name=InsertCampWeek Datasource=#DSN# Insert into campweeks ( CampWeekName, CFIF IsDate(Trim(Form.StartDate)) StartDate, /CFIF CFIF IsDate(Trim(Form.EndDate)) EndDate, /CFIF DeanName, DeanBio, DeanPhoto, WeekDescription, Cost, Grades, CFIF IsDate(Trim(Form.RegistrationStartTime)) RegistrationStartTime, /CFIF CFIF IsDate(Trim(Form.DismissalTime)) DismissalTime, /CFIF SpacesAvailable ) Values ( cfqueryparam cfsqltype=CF_SQL_VARCHAR Value=#Form.CampWeekName#, CFIF IsDate(Trim(Form.StartDate)) cfqueryparam cfsqltype=CF_SQL_DATE Value=#DateFormat(CreateODBCDate(ParseDateTime(Form.StartDa te)), 'M/D/')#, /CFIF CFIF IsDate(Trim(Form.EndDate)) cfqueryparam cfsqltype=CF_SQL_DATE Value=#DateFormat(CreateODBCDate(ParseDateTime(Form.EndDate )), 'M/D/')#, /CFIF cfqueryparam cfsqltype=CF_SQL_VARCHAR Value=#Form.DeanName#, cfqueryparam cfsqltype=CF_SQL_LONGVARCHAR Value=#Form.DeanBio#, cfqueryparam cfsqltype=CF_SQL_VARCHAR Value=#Form.DeanPhoto#, cfqueryparam cfsqltype=CF_SQL_LONGVARCHAR Value=#Form.WeekDescription#, cfqueryparam cfsqltype=CF_SQL_DOUBLE Value=#LSParseCurrency(Form.Cost)#, cfqueryparam cfsqltype=CF_SQL_VARCHAR Value=#Form.Grades#, CFIF IsDate(Form.RegistrationStartTime) cfqueryparam cfsqltype=CF_SQL_TIME Value=#TimeFormat(CreateODBCTime(Form.RegistrationStartTime ), 'h:mm tt')#, /CFIF CFIF IsDate(Form.DismissalTime) cfqueryparam cfsqltype=CF_SQL_TIME Value=#TimeFormat(CreateODBCTime(Form.DismissalTime), 'h:mm tt')#, /CFIF cfqueryparam cfsqltype=CF_SQL_INTEGER Value=#Form.SpacesAvailable# /CFQUERY ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:230441 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: Can anyone spot a problem with this query?
Constraint Violation beans that there would be more than one records with the same Set of values. Check your Primary Keys and Indexes. -Original Message- From: Rick Faircloth [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 25, 2006 4:17 PM To: CF-Talk Subject: Can anyone spot a problem with this query? MySQL is giving me an ODBC Error Code = 23000 (Integrity contraint violation) and says I have an error in my SQL syntax...I can't find it...data being sent from form fields has been tested an appear to be in proper form...I can't figure out why it's getting heartburn over this query... ??? Thanks, Rick CFQUERY Name=InsertCampWeek Datasource=#DSN# Insert into campweeks ( CampWeekName, CFIF IsDate(Trim(Form.StartDate)) StartDate, /CFIF CFIF IsDate(Trim(Form.EndDate)) EndDate, /CFIF DeanName, DeanBio, DeanPhoto, WeekDescription, Cost, Grades, CFIF IsDate(Trim(Form.RegistrationStartTime)) RegistrationStartTime, /CFIF CFIF IsDate(Trim(Form.DismissalTime)) DismissalTime, /CFIF SpacesAvailable ) Values ( cfqueryparam cfsqltype=CF_SQL_VARCHAR Value=#Form.CampWeekName#, CFIF IsDate(Trim(Form.StartDate)) cfqueryparam cfsqltype=CF_SQL_DATE Value=#DateFormat(CreateODBCDate(ParseDateTime(Form.StartDa te)), 'M/D/')#, /CFIF CFIF IsDate(Trim(Form.EndDate)) cfqueryparam cfsqltype=CF_SQL_DATE Value=#DateFormat(CreateODBCDate(ParseDateTime(Form.EndDate )), 'M/D/')#, /CFIF cfqueryparam cfsqltype=CF_SQL_VARCHAR Value=#Form.DeanName#, cfqueryparam cfsqltype=CF_SQL_LONGVARCHAR Value=#Form.DeanBio#, cfqueryparam cfsqltype=CF_SQL_VARCHAR Value=#Form.DeanPhoto#, cfqueryparam cfsqltype=CF_SQL_LONGVARCHAR Value=#Form.WeekDescription#, cfqueryparam cfsqltype=CF_SQL_DOUBLE Value=#LSParseCurrency(Form.Cost)#, cfqueryparam cfsqltype=CF_SQL_VARCHAR Value=#Form.Grades#, CFIF IsDate(Form.RegistrationStartTime) cfqueryparam cfsqltype=CF_SQL_TIME Value=#TimeFormat(CreateODBCTime(Form.RegistrationStartTime ), 'h:mm tt')#, /CFIF CFIF IsDate(Form.DismissalTime) cfqueryparam cfsqltype=CF_SQL_TIME Value=#TimeFormat(CreateODBCTime(Form.DismissalTime), 'h:mm tt')#, /CFIF cfqueryparam cfsqltype=CF_SQL_INTEGER Value=#Form.SpacesAvailable# /CFQUERY ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:230446 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: Can anyone spot a problem with this query?
Thanks for the reply, Randy... there would be more than one records with the same Set of values. The table is empty...I'm trying to add the first record. The only key of any type is the CampWeekID, which is the Primary Key...no Foreign Keys or Indexes... Rick -Original Message- From: Adkins, Randy [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 25, 2006 4:27 PM To: CF-Talk Subject: RE: Can anyone spot a problem with this query? Constraint Violation beans that there would be more than one records with the same Set of values. Check your Primary Keys and Indexes. -Original Message- From: Rick Faircloth [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 25, 2006 4:17 PM To: CF-Talk Subject: Can anyone spot a problem with this query? MySQL is giving me an ODBC Error Code = 23000 (Integrity contraint violation) and says I have an error in my SQL syntax...I can't find it...data being sent from form fields has been tested an appear to be in proper form...I can't figure out why it's getting heartburn over this query... ??? Thanks, Rick CFQUERY Name=InsertCampWeek Datasource=#DSN# Insert into campweeks ( CampWeekName, CFIF IsDate(Trim(Form.StartDate)) StartDate, /CFIF CFIF IsDate(Trim(Form.EndDate)) EndDate, /CFIF DeanName, DeanBio, DeanPhoto, WeekDescription, Cost, Grades, CFIF IsDate(Trim(Form.RegistrationStartTime)) RegistrationStartTime, /CFIF CFIF IsDate(Trim(Form.DismissalTime)) DismissalTime, /CFIF SpacesAvailable ) Values ( cfqueryparam cfsqltype=CF_SQL_VARCHAR Value=#Form.CampWeekName#, CFIF IsDate(Trim(Form.StartDate)) cfqueryparam cfsqltype=CF_SQL_DATE Value=#DateFormat(CreateODBCDate(ParseDateTime(Form.StartDa te)), 'M/D/')#, /CFIF CFIF IsDate(Trim(Form.EndDate)) cfqueryparam cfsqltype=CF_SQL_DATE Value=#DateFormat(CreateODBCDate(ParseDateTime(Form.EndDate )), 'M/D/')#, /CFIF cfqueryparam cfsqltype=CF_SQL_VARCHAR Value=#Form.DeanName#, cfqueryparam cfsqltype=CF_SQL_LONGVARCHAR Value=#Form.DeanBio#, cfqueryparam cfsqltype=CF_SQL_VARCHAR Value=#Form.DeanPhoto#, cfqueryparam cfsqltype=CF_SQL_LONGVARCHAR Value=#Form.WeekDescription#, cfqueryparam cfsqltype=CF_SQL_DOUBLE Value=#LSParseCurrency(Form.Cost)#, cfqueryparam cfsqltype=CF_SQL_VARCHAR Value=#Form.Grades#, CFIF IsDate(Form.RegistrationStartTime) cfqueryparam cfsqltype=CF_SQL_TIME Value=#TimeFormat(CreateODBCTime(Form.RegistrationStartTime ), 'h:mm tt')#, /CFIF CFIF IsDate(Form.DismissalTime) cfqueryparam cfsqltype=CF_SQL_TIME Value=#TimeFormat(CreateODBCTime(Form.DismissalTime), 'h:mm tt')#, /CFIF cfqueryparam cfsqltype=CF_SQL_INTEGER Value=#Form.SpacesAvailable# /CFQUERY ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:230448 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: Can anyone spot a problem with this query?
You're missing the closing parenthesis ')' after the last queryparam. - Rod On 1/25/06, Rick Faircloth [EMAIL PROTECTED] wrote: MySQL is giving me an ODBC Error Code = 23000 (Integrity contraint violation) and says I have an error in my SQL syntax...I can't find it...data being sent from form fields has been tested an appear to be in proper form...I can't figure out why it's getting heartburn over this query... ??? Thanks, Rick CFQUERY Name=InsertCampWeek Datasource=#DSN# Insert into campweeks ( CampWeekName, CFIF IsDate(Trim(Form.StartDate)) StartDate, /CFIF CFIF IsDate(Trim(Form.EndDate)) EndDate, /CFIF DeanName, DeanBio, DeanPhoto, WeekDescription, Cost, Grades, CFIF IsDate(Trim(Form.RegistrationStartTime)) RegistrationStartTime, /CFIF CFIF IsDate(Trim(Form.DismissalTime)) DismissalTime, /CFIF SpacesAvailable ) Values ( cfqueryparam cfsqltype=CF_SQL_VARCHAR Value=#Form.CampWeekName#, CFIF IsDate(Trim(Form.StartDate)) cfqueryparam cfsqltype=CF_SQL_DATE Value=#DateFormat(CreateODBCDate(ParseDateTime(Form.StartDa te)), 'M/D/')#, /CFIF CFIF IsDate(Trim(Form.EndDate)) cfqueryparam cfsqltype=CF_SQL_DATE Value=#DateFormat(CreateODBCDate(ParseDateTime(Form.EndDate )), 'M/D/')#, /CFIF cfqueryparam cfsqltype=CF_SQL_VARCHAR Value=#Form.DeanName#, cfqueryparam cfsqltype=CF_SQL_LONGVARCHAR Value=#Form.DeanBio#, cfqueryparam cfsqltype=CF_SQL_VARCHAR Value=#Form.DeanPhoto#, cfqueryparam cfsqltype=CF_SQL_LONGVARCHAR Value=#Form.WeekDescription#, cfqueryparam cfsqltype=CF_SQL_DOUBLE Value=#LSParseCurrency(Form.Cost)#, cfqueryparam cfsqltype=CF_SQL_VARCHAR Value=#Form.Grades#, CFIF IsDate(Form.RegistrationStartTime) cfqueryparam cfsqltype=CF_SQL_TIME Value=#TimeFormat(CreateODBCTime(Form.RegistrationStartTime ), 'h:mm tt')#, /CFIF CFIF IsDate(Form.DismissalTime) cfqueryparam cfsqltype=CF_SQL_TIME Value=#TimeFormat(CreateODBCTime(Form.DismissalTime), 'h:mm tt')#, /CFIF cfqueryparam cfsqltype=CF_SQL_INTEGER Value=#Form.SpacesAvailable# /CFQUERY ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:230457 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: Can anyone spot a problem with this query?
Unbelievable... Thanks, Rodney! Rick -Original Message- From: Rodney Enke [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 25, 2006 6:10 PM To: CF-Talk Subject: Re: Can anyone spot a problem with this query? You're missing the closing parenthesis ')' after the last queryparam. - Rod On 1/25/06, Rick Faircloth [EMAIL PROTECTED] wrote: MySQL is giving me an ODBC Error Code = 23000 (Integrity contraint violation) and says I have an error in my SQL syntax...I can't find it...data being sent from form fields has been tested an appear to be in proper form...I can't figure out why it's getting heartburn over this query... ??? Thanks, Rick CFQUERY Name=InsertCampWeek Datasource=#DSN# Insert into campweeks ( CampWeekName, CFIF IsDate(Trim(Form.StartDate)) StartDate, /CFIF CFIF IsDate(Trim(Form.EndDate)) EndDate, /CFIF DeanName, DeanBio, DeanPhoto, WeekDescription, Cost, Grades, CFIF IsDate(Trim(Form.RegistrationStartTime)) RegistrationStartTime, /CFIF CFIF IsDate(Trim(Form.DismissalTime)) DismissalTime, /CFIF SpacesAvailable ) Values ( cfqueryparam cfsqltype=CF_SQL_VARCHAR Value=#Form.CampWeekName#, CFIF IsDate(Trim(Form.StartDate)) cfqueryparam cfsqltype=CF_SQL_DATE Value=#DateFormat(CreateODBCDate(ParseDateTime(Form.StartDa te)), 'M/D/')#, /CFIF CFIF IsDate(Trim(Form.EndDate)) cfqueryparam cfsqltype=CF_SQL_DATE Value=#DateFormat(CreateODBCDate(ParseDateTime(Form.EndDate )), 'M/D/')#, /CFIF cfqueryparam cfsqltype=CF_SQL_VARCHAR Value=#Form.DeanName#, cfqueryparam cfsqltype=CF_SQL_LONGVARCHAR Value=#Form.DeanBio#, cfqueryparam cfsqltype=CF_SQL_VARCHAR Value=#Form.DeanPhoto#, cfqueryparam cfsqltype=CF_SQL_LONGVARCHAR Value=#Form.WeekDescription#, cfqueryparam cfsqltype=CF_SQL_DOUBLE Value=#LSParseCurrency(Form.Cost)#, cfqueryparam cfsqltype=CF_SQL_VARCHAR Value=#Form.Grades#, CFIF IsDate(Form.RegistrationStartTime) cfqueryparam cfsqltype=CF_SQL_TIME Value=#TimeFormat(CreateODBCTime(Form.RegistrationStartTime ), 'h:mm tt')#, /CFIF CFIF IsDate(Form.DismissalTime) cfqueryparam cfsqltype=CF_SQL_TIME Value=#TimeFormat(CreateODBCTime(Form.DismissalTime), 'h:mm tt')#, /CFIF cfqueryparam cfsqltype=CF_SQL_INTEGER Value=#Form.SpacesAvailable# /CFQUERY ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:230459 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: Does anyone see a problem with this query?
Old: SELECT topc.id, topc.topics, subt.id, subt.topic_id, subt.s_topics FROM topics AS topc INNER JOIN sub_topics AS subt ON topc.id = subt.id WHERE topc.id = subt.id New: SELECT topc.id, topc.topics, subt.id, subt.topic_id, subt.s_topics FROM topics topc INNER JOIN sub_topics subt ON topc.id = subt.id WHERE topc.id = subt.id Might give that a try, Bill K -Original Message- From: Michael Haggerty [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 11, 2001 11:28 AM To: CF-Talk Subject: RE: Does anyone see a problem with this query? What is the error you are getting? -Original Message- From: Douglas Brown [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 11, 2001 11:39 AM To: CF-Talk Subject: Re: Does anyone see a problem with this query? Absolutely nothing else. Doug - Original Message - From: Mike Connolly [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Tuesday, December 11, 2001 2:12 AM Subject: RE: Does anyone see a problem with this query? I don't know if I'm alone in this, but at first, second, and third glance your query syntax seems fine. Is there anything else to this query? -Original Message- From: Douglas Brown [mailto:[EMAIL PROTECTED]] Sent: 11 December 2001 00:42 To: CF-Talk Subject: Does anyone see a problem with this query? I keep getting this error when I run the query. This is a SQL 2K database ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect syntax near '.'. SELECT topc.id, topc.topics, subt.id, subt.topic_id, subt.s_topics FROM topics AS topc INNER JOIN sub_topics AS subt ON topc.id = subt.id WHERE topc.id = subt.id Thanks Doug ~~ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Does anyone see a problem with this query?
I don't know if I'm alone in this, but at first, second, and third glance your query syntax seems fine. Is there anything else to this query? -Original Message- From: Douglas Brown [mailto:[EMAIL PROTECTED]] Sent: 11 December 2001 00:42 To: CF-Talk Subject: Does anyone see a problem with this query? I keep getting this error when I run the query. This is a SQL 2K database ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect syntax near '.'. SELECT topc.id, topc.topics, subt.id, subt.topic_id, subt.s_topics FROM topics AS topc INNER JOIN sub_topics AS subt ON topc.id = subt.id WHERE topc.id = subt.id Thanks Doug ~~ 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 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Does anyone see a problem with this query?
Absolutely nothing else. Doug - Original Message - From: Mike Connolly [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Tuesday, December 11, 2001 2:12 AM Subject: RE: Does anyone see a problem with this query? I don't know if I'm alone in this, but at first, second, and third glance your query syntax seems fine. Is there anything else to this query? -Original Message- From: Douglas Brown [mailto:[EMAIL PROTECTED]] Sent: 11 December 2001 00:42 To: CF-Talk Subject: Does anyone see a problem with this query? I keep getting this error when I run the query. This is a SQL 2K database ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect syntax near '.'. SELECT topc.id, topc.topics, subt.id, subt.topic_id, subt.s_topics FROM topics AS topc INNER JOIN sub_topics AS subt ON topc.id = subt.id WHERE topc.id = subt.id Thanks Doug ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Does anyone see a problem with this query?
What is the error you are getting? -Original Message- From: Douglas Brown [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 11, 2001 11:39 AM To: CF-Talk Subject: Re: Does anyone see a problem with this query? Absolutely nothing else. Doug - Original Message - From: Mike Connolly [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Tuesday, December 11, 2001 2:12 AM Subject: RE: Does anyone see a problem with this query? I don't know if I'm alone in this, but at first, second, and third glance your query syntax seems fine. Is there anything else to this query? -Original Message- From: Douglas Brown [mailto:[EMAIL PROTECTED]] Sent: 11 December 2001 00:42 To: CF-Talk Subject: Does anyone see a problem with this query? I keep getting this error when I run the query. This is a SQL 2K database ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect syntax near '.'. SELECT topc.id, topc.topics, subt.id, subt.topic_id, subt.s_topics FROM topics AS topc INNER JOIN sub_topics AS subt ON topc.id = subt.id WHERE topc.id = subt.id Thanks Doug ~~ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Does anyone see a problem with this query?
Here is the original post ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect syntax near '.'. SELECT topc.id, topc.topics, subt.id, subt.topic_id, subt.s_topics FROM topics AS topc INNER JOIN sub_topics AS subt ON topc.id = subt.id WHERE topc.id = subt.id - Original Message - From: Michael Haggerty [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Tuesday, December 11, 2001 8:27 AM Subject: RE: Does anyone see a problem with this query? What is the error you are getting? -Original Message- From: Douglas Brown [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 11, 2001 11:39 AM To: CF-Talk Subject: Re: Does anyone see a problem with this query? Absolutely nothing else. Doug - Original Message - From: Mike Connolly [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Tuesday, December 11, 2001 2:12 AM Subject: RE: Does anyone see a problem with this query? I don't know if I'm alone in this, but at first, second, and third glance your query syntax seems fine. Is there anything else to this query? -Original Message- From: Douglas Brown [mailto:[EMAIL PROTECTED]] Sent: 11 December 2001 00:42 To: CF-Talk Subject: Does anyone see a problem with this query? I keep getting this error when I run the query. This is a SQL 2K database ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect syntax near '.'. SELECT topc.id, topc.topics, subt.id, subt.topic_id, subt.s_topics FROM topics AS topc INNER JOIN sub_topics AS subt ON topc.id = subt.id WHERE topc.id = subt.id Thanks Doug ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Does anyone see a problem with this query?
I have a feeling that this is not the actual query that's running, you're just assuming it is. I may be wrong, but what is the actual cfquery block containing this query? don't modify it at all, just show the actual cfm query... -Original Message- From: Douglas Brown [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 11, 2001 10:49 AM To: CF-Talk Subject: Re: Does anyone see a problem with this query? Here is the original post ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect syntax near '.'. SELECT topc.id, topc.topics, subt.id, subt.topic_id, subt.s_topics FROM topics AS topc INNER JOIN sub_topics AS subt ON topc.id = subt.id WHERE topc.id = subt.id - Original Message - From: Michael Haggerty [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Tuesday, December 11, 2001 8:27 AM Subject: RE: Does anyone see a problem with this query? What is the error you are getting? -Original Message- From: Douglas Brown [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 11, 2001 11:39 AM To: CF-Talk Subject: Re: Does anyone see a problem with this query? Absolutely nothing else. Doug - Original Message - From: Mike Connolly [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Tuesday, December 11, 2001 2:12 AM Subject: RE: Does anyone see a problem with this query? I don't know if I'm alone in this, but at first, second, and third glance your query syntax seems fine. Is there anything else to this query? -Original Message- From: Douglas Brown [mailto:[EMAIL PROTECTED]] Sent: 11 December 2001 00:42 To: CF-Talk Subject: Does anyone see a problem with this query? I keep getting this error when I run the query. This is a SQL 2K database ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect syntax near '.'. SELECT topc.id, topc.topics, subt.id, subt.topic_id, subt.s_topics FROM topics AS topc INNER JOIN sub_topics AS subt ON topc.id = subt.id WHERE topc.id = subt.id Thanks Doug ~~ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Does anyone see a problem with this query?
I was hoping that was not really the error... I don't see how this could be causing the error, but it seems like you are joining on the wrong id field. Did you really mean to join on the id fields of each table, or were you meaning to join on topics.id to to sub_topics.topic_id? I dunno... this is a wierd one. Maybe the problem has to do with the permissions on CF? Mike -Original Message- From: Douglas Brown [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 11, 2001 11:49 AM To: CF-Talk Subject: Re: Does anyone see a problem with this query? Here is the original post ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect syntax near '.'. SELECT topc.id, topc.topics, subt.id, subt.topic_id, subt.s_topics FROM topics AS topc INNER JOIN sub_topics AS subt ON topc.id = subt.id WHERE topc.id = subt.id - Original Message - From: Michael Haggerty [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Tuesday, December 11, 2001 8:27 AM Subject: RE: Does anyone see a problem with this query? What is the error you are getting? -Original Message- From: Douglas Brown [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 11, 2001 11:39 AM To: CF-Talk Subject: Re: Does anyone see a problem with this query? Absolutely nothing else. Doug - Original Message - From: Mike Connolly [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Tuesday, December 11, 2001 2:12 AM Subject: RE: Does anyone see a problem with this query? I don't know if I'm alone in this, but at first, second, and third glance your query syntax seems fine. Is there anything else to this query? -Original Message- From: Douglas Brown [mailto:[EMAIL PROTECTED]] Sent: 11 December 2001 00:42 To: CF-Talk Subject: Does anyone see a problem with this query? I keep getting this error when I run the query. This is a SQL 2K database ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect syntax near '.'. SELECT topc.id, topc.topics, subt.id, subt.topic_id, subt.s_topics FROM topics AS topc INNER JOIN sub_topics AS subt ON topc.id = subt.id WHERE topc.id = subt.id Thanks Doug ~~ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Does anyone see a problem with this query?
Can't see anything to cause a syntax error but you do have a superfluous WHERE clause as the expression is being handled by the join. Could you post the CF code that generates the SQL pls. Steve -Original Message- From: Douglas Brown [mailto:[EMAIL PROTECTED]] Sent: 11 December 2001 16:49 To: CF-Talk Subject: Re: Does anyone see a problem with this query? Here is the original post ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect syntax near '.'. SELECT topc.id, topc.topics, subt.id, subt.topic_id, subt.s_topics FROM topics AS topc INNER JOIN sub_topics AS subt ON topc.id = subt.id WHERE topc.id = subt.id - Original Message - From: Michael Haggerty [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Tuesday, December 11, 2001 8:27 AM Subject: RE: Does anyone see a problem with this query? What is the error you are getting? -Original Message- From: Douglas Brown [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 11, 2001 11:39 AM To: CF-Talk Subject: Re: Does anyone see a problem with this query? Absolutely nothing else. Doug - Original Message - From: Mike Connolly [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Tuesday, December 11, 2001 2:12 AM Subject: RE: Does anyone see a problem with this query? I don't know if I'm alone in this, but at first, second, and third glance your query syntax seems fine. Is there anything else to this query? -Original Message- From: Douglas Brown [mailto:[EMAIL PROTECTED]] Sent: 11 December 2001 00:42 To: CF-Talk Subject: Does anyone see a problem with this query? I keep getting this error when I run the query. This is a SQL 2K database ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect syntax near '.'. SELECT topc.id, topc.topics, subt.id, subt.topic_id, subt.s_topics FROM topics AS topc INNER JOIN sub_topics AS subt ON topc.id = subt.id WHERE topc.id = subt.id Thanks Doug ~~ 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 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Does anyone see a problem with this query?
That is totally weird. Right after I got this error I refreshed and it worked. Error Diagnostic Information ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect syntax near '.'. The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (1:1) to (1:65) in the template file C:\CARNIVOREPC\_FORUM\_QRY\QRY_GET_TOPICS.CFM. Date/Time: 12/11/01 09:08:45 Browser: Mozilla/4.0 (compatible; MSIE 5.5; Windows NT 5.0) Remote Address: 66.27.3.82 CFQUERY DATASOURCE=#request.site.forum_dsn# NAME=get_topics SELECT topc.id, topc.topics, sub.id, sub.topic_id, sub.s_topics FROM topics AS topc INNER JOIN sub_topics AS sub ON topc.id = sub.id WHERE topc.id = sub.id /CFQUERY Doug - Original Message - From: Dunwiddie, Bruce [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Tuesday, December 11, 2001 8:51 AM Subject: RE: Does anyone see a problem with this query? I have a feeling that this is not the actual query that's running, you're just assuming it is. I may be wrong, but what is the actual cfquery block containing this query? don't modify it at all, just show the actual cfm query... -Original Message- From: Douglas Brown [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 11, 2001 10:49 AM To: CF-Talk Subject: Re: Does anyone see a problem with this query? Here is the original post ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect syntax near '.'. SELECT topc.id, topc.topics, subt.id, subt.topic_id, subt.s_topics FROM topics AS topc INNER JOIN sub_topics AS subt ON topc.id = subt.id WHERE topc.id = subt.id - Original Message - From: Michael Haggerty [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Tuesday, December 11, 2001 8:27 AM Subject: RE: Does anyone see a problem with this query? What is the error you are getting? -Original Message- From: Douglas Brown [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 11, 2001 11:39 AM To: CF-Talk Subject: Re: Does anyone see a problem with this query? Absolutely nothing else. Doug - Original Message - From: Mike Connolly [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Tuesday, December 11, 2001 2:12 AM Subject: RE: Does anyone see a problem with this query? I don't know if I'm alone in this, but at first, second, and third glance your query syntax seems fine. Is there anything else to this query? -Original Message- From: Douglas Brown [mailto:[EMAIL PROTECTED]] Sent: 11 December 2001 00:42 To: CF-Talk Subject: Does anyone see a problem with this query? I keep getting this error when I run the query. This is a SQL 2K database ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect syntax near '.'. SELECT topc.id, topc.topics, subt.id, subt.topic_id, subt.s_topics FROM topics AS topc INNER JOIN sub_topics AS subt ON topc.id = subt.id WHERE topc.id = subt.id Thanks Doug ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Does anyone see a problem with this query?
yeah, I see a problem alright! From: Dunwiddie, Bruce [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Subject: RE: Does anyone see a problem with this query? Date: Tue, 11 Dec 2001 10:51:50 -0600 I have a feeling that this is not the actual query that's running, you're just assuming it is. I may be wrong, but what is the actual cfquery block containing this query? don't modify it at all, just show the actual cfm query... -Original Message- From: Douglas Brown [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 11, 2001 10:49 AM To: CF-Talk Subject: Re: Does anyone see a problem with this query? Here is the original post ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect syntax near '.'. SELECT topc.id, topc.topics, subt.id, subt.topic_id, subt.s_topics FROM topics AS topc INNER JOIN sub_topics AS subt ON topc.id = subt.id WHERE topc.id = subt.id - Original Message - From: Michael Haggerty [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Tuesday, December 11, 2001 8:27 AM Subject: RE: Does anyone see a problem with this query? What is the error you are getting? -Original Message- From: Douglas Brown [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 11, 2001 11:39 AM To: CF-Talk Subject: Re: Does anyone see a problem with this query? Absolutely nothing else. Doug - Original Message - From: Mike Connolly [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Tuesday, December 11, 2001 2:12 AM Subject: RE: Does anyone see a problem with this query? I don't know if I'm alone in this, but at first, second, and third glance your query syntax seems fine. Is there anything else to this query? -Original Message- From: Douglas Brown [mailto:[EMAIL PROTECTED]] Sent: 11 December 2001 00:42 To: CF-Talk Subject: Does anyone see a problem with this query? I keep getting this error when I run the query. This is a SQL 2K database ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect syntax near '.'. SELECT topc.id, topc.topics, subt.id, subt.topic_id, subt.s_topics FROM topics AS topc INNER JOIN sub_topics AS subt ON topc.id = subt.id WHERE topc.id = subt.id Thanks Doug ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Does anyone see a problem with this query?
Well for one, get rid of the WHERE clause as you're effectively cancelling out the INNER JOIN. It's probably the topc.topics, bit of the query, but I can't be sure. Darryl -Original Message- From: Mike Connolly [mailto:[EMAIL PROTECTED]] Sent: Tuesday, 11 December 2001 8:12 PM To: CF-Talk Subject: RE: Does anyone see a problem with this query? I don't know if I'm alone in this, but at first, second, and third glance your query syntax seems fine. Is there anything else to this query? -Original Message- From: Douglas Brown [mailto:[EMAIL PROTECTED]] Sent: 11 December 2001 00:42 To: CF-Talk Subject: Does anyone see a problem with this query? I keep getting this error when I run the query. This is a SQL 2K database ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect syntax near '.'. SELECT topc.id, topc.topics, subt.id, subt.topic_id, subt.s_topics FROM topics AS topc INNER JOIN sub_topics AS subt ON topc.id = subt.id WHERE topc.id = subt.id Thanks Doug ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Does anyone see a problem with this query?
I keep getting this error when I run the query. This is a SQL 2K database ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect syntax near '.'. SELECT topc.id, topc.topics, subt.id, subt.topic_id, subt.s_topics FROM topics AS topc INNER JOIN sub_topics AS subt ON topc.id = subt.id WHERE topc.id = subt.id Thanks Doug ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Does anyone see a problem with this query?
P.S This same query works great in query analyzer Doug - Original Message - From: Douglas Brown [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Monday, December 10, 2001 4:41 PM Subject: Does anyone see a problem with this query? I keep getting this error when I run the query. This is a SQL 2K database ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect syntax near '.'. SELECT topc.id, topc.topics, subt.id, subt.topic_id, subt.s_topics FROM topics AS topc INNER JOIN sub_topics AS subt ON topc.id = subt.id WHERE topc.id = subt.id Thanks Doug ~~ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Does anyone see a problem with this query?
leave out the 'AS' in the from line At 04:47 PM 12/10/2001 -0800, you wrote: P.S This same query works great in query analyzer Doug - Original Message - From: Douglas Brown [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Monday, December 10, 2001 4:41 PM Subject: Does anyone see a problem with this query? I keep getting this error when I run the query. This is a SQL 2K database ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect syntax near '.'. SELECT topc.id, topc.topics, subt.id, subt.topic_id, subt.s_topics FROM topics AS topc INNER JOIN sub_topics AS subt ON topc.id = subt.id WHERE topc.id = subt.id Thanks Doug ~~ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Problem moving a query to stored procedure
Here is my problem. I have a query which I pass a comma-delimited list and use the IN predicate to return only the rows I want. Now I want to convert it to a stored procedure. How do I send that comma-delimited list and then process it as type int in the stored procedure?? What I currently have appears to see the first facility but not the rest. BOL says that the datatypes much match. What if I put the list in an array? Can I pass that to the stored procedure? What do I do with it there? Code follows = Where Auth_Facility is 1,2,3,4,5 which matchs the FacilityIDs I want to return Current query: CFQUERY NAME=GetAnnouncements MAXROWS=5 DATASOURCE=#Application.DataSource_# SELECT * FROM Announcements WHERE ((Announcements.StartDate = #CreateODBCDate(Now())#)) AND ((Announcements.EndDate = #CreateODBCDate(Now())#)) AND FacilityID IN (#Auth_Facility#) ORDER BY facilityid, StartDate DESC == NEW CF Code: cfstoredproc procedure=spShowAnnouncements datasource=#Application.DataSource_# returncode=Yes cfprocparam type=In cfsqltype=CF_SQL_VARCHAR dbvarname=@EntityList value=#Auth_Facility# maxlength=30 null=No cfprocresult name=GetAnnouncements /cfstoredproc == Stored Procedure: CREATE PROCEDURE spShowAnnouncements @EntityList varchar AS SELECT * FROM Announcements WHERE (Announcements.StartDate = getDate()) AND (Announcements.EndDate = getDate()) AND FacilityID in (@EntityList) ORDER BY FacilityID, StartDate DESC == ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Problem generating a query based on a specific date field
On 4/7/01, Johnny Boursiquot penned: CFQUERY datasource="#DSN#" name="clock" SELECT * FROM Clock WHERE (StaffID = #session.StaffID#) AND (ClockDate = Now()) /CFQUERY Try ClockDate = #Now()# -- Bud Schneehagen - Tropical Web Creations _/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/ ColdFusion Solutions / eCommerce Development [EMAIL PROTECTED] http://www.twcreations.com/ 954.721.3452 ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Problem generating a query based on a specific date field
I have an Access 2000 db that has a Clock table which has a ClockDate colum. I tried generating results from that table using the following code: CFQUERY datasource="#DSN#" name="clock" SELECT * FROM Clock WHERE (StaffID = #session.StaffID#) AND (ClockDate = Now()) /CFQUERY No results were returned. I verified the table and made sure I had the content with the appropriate requirements (a staffid and date for today) yet nothing came back. I went into Access, built a query that had #4/6/01# as a criteria for the colum ClockDate and actually got results back. Now I obviously cannot use #s in my cfml. Does anyone know how I overcome this? Thanks in advance Johnny Boursiquot iDjin Web Works, Inc. www.idjin.com ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Problem generating a query based on a specific date field
By default Now() returns time down to the second, you might want to try createODBCDate(Now()) or DateFormat(Now(), "mm/dd/yy"). HTH, Michael Caulfield -Original Message- From: Johnny Boursiquot [mailto:[EMAIL PROTECTED]] Sent: Saturday, April 07, 2001 1:31 AM To: CF-Talk Subject: Problem generating a query based on a specific date field I have an Access 2000 db that has a Clock table which has a ClockDate colum. I tried generating results from that table using the following code: CFQUERY datasource="#DSN#" name="clock" SELECT * FROM Clock WHERE (StaffID = #session.StaffID#) AND (ClockDate = Now()) /CFQUERY No results were returned. I verified the table and made sure I had the content with the appropriate requirements (a staffid and date for today) yet nothing came back. I went into Access, built a query that had #4/6/01# as a criteria for the colum ClockDate and actually got results back. Now I obviously cannot use #s in my cfml. Does anyone know how I overcome this? Thanks in advance Johnny Boursiquot iDjin Web Works, Inc. www.idjin.com ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: CF_QuerySort-problem (Was: SQL-SORT-Problem in a query !)
Courtney, I don't want to do a search. I want to do a sort. The sort in a numeric way over a varchar field is possible with this Tag. But I will try what you supposed on taking QueryAddColumn() Thanks. Uwe - Original Message - From: "Courtney Payne" [EMAIL PROTECTED] To: "CF-Talk" [EMAIL PROTECTED] Sent: Freitag, 27. Oktober 2000 14:14 Subject: RE: CF_QuerySort-problem (Was: SQL-SORT-Problem in a query !) Well, you can't do a numeric search on non-numeric values. Umm... I think I'd add a field/column to my result set -- using the QueryAddColumn() -- and, for each row, set this new field to the 'housenumber' field's *numeric* equivalent -- using the QuerySetCell() and Val() functions. Then sort on this, now numeric, field in my cf_querysort. Courtney E. Payne, Developer Fig Leaf Software "We've got you covered" [EMAIL PROTECTED] www.figleaf.com -Original Message- From: CF-Talk [mailto:[EMAIL PROTECTED]] Sent: Thursday, October 26, 2000 5:21 PM To: CF-Talk Subject: CF_QuerySort-problem (Was: SQL-SORT-Problem in a query !) o.k. I had a sorting problem yesterday but with the help of the Custom Tag CF_QuerySort I am able to sort a varchar-column in a numeric way. (This morning I thought at least !) Although right now it doesn't seem to work anymore, since in my housenumber field there are most likely values like 93-94 and 101B and so on. I don't know why exactly but he (CF) is telling me nonstop when I order via the housenumber-field in a numeric manner the following: --- Error Diagnostic Information In function ArraySort the array element at position 4 is not a numeric value The error occurred while evaluating the expression: Temp = ArraySort(MyArray, Attributes.SortType, Attributes.SortOrder) The error occurred while processing an element with a general identifier of (CFSET), occupying document position (39:1) to (39:76). --- Ideas why ? Uwe SD Solutions Lange Zeile 27 85435 Erding Fon: 08122/903791 Fax: 08122/903792 Mail: [EMAIL PROTECTED] Web: www.sdsolutions.de Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED] Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED] Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED]
Re: CF_QuerySort-problem (Was: SQL-SORT-Problem in a query !)
Peter, thank you. This is probably it. I was thinking this is the reason for the error as well. I probably have to add another column additionally to the housenumber field which is taking the additions like "A" or "B" or "-" so that the values in the field itself stay as numeric/text values. Uwe - Original Message - From: "Peter J. MacDonald" [EMAIL PROTECTED] To: "CF-Talk" [EMAIL PROTECTED] Sent: Freitag, 27. Oktober 2000 23:37 Subject: RE: CF_QuerySort-problem (Was: SQL-SORT-Problem in a query !) The reason is that 93-94 and 101B can not be turned into numbers. The tag is probably suppose to take VARCHAR columns and make them into numeric columns only if the value of the column is a number(i.e. no A-Z or special characters. Pete -Original Message- From: CF-Talk [mailto:[EMAIL PROTECTED]] Sent: Thursday, October 26, 2000 17:21 To: CF-Talk Subject: CF_QuerySort-problem (Was: SQL-SORT-Problem in a query !) o.k. I had a sorting problem yesterday but with the help of the Custom Tag CF_QuerySort I am able to sort a varchar-column in a numeric way. (This morning I thought at least !) Although right now it doesn't seem to work anymore, since in my housenumber field there are most likely values like 93-94 and 101B and so on. I don't know why exactly but he (CF) is telling me nonstop when I order via the housenumber-field in a numeric manner the following: --- Error Diagnostic Information In function ArraySort the array element at position 4 is not a numeric value The error occurred while evaluating the expression: Temp = ArraySort(MyArray, Attributes.SortType, Attributes.SortOrder) The error occurred while processing an element with a general identifier of (CFSET), occupying document position (39:1) to (39:76). --- Ideas why ? Uwe SD Solutions Lange Zeile 27 85435 Erding Fon: 08122/903791 Fax: 08122/903792 Mail: [EMAIL PROTECTED] Web: www.sdsolutions.de Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED] Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED] Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED]
RE: CF_QuerySort-problem (Was: SQL-SORT-Problem in a query !)
Well, you can't do a numeric search on non-numeric values. Umm... I think I'd add a field/column to my result set -- using the QueryAddColumn() -- and, for each row, set this new field to the 'housenumber' field's *numeric* equivalent -- using the QuerySetCell() and Val() functions. Then sort on this, now numeric, field in my cf_querysort. Courtney E. Payne, Developer Fig Leaf Software "We've got you covered" [EMAIL PROTECTED] www.figleaf.com -Original Message- From: CF-Talk [mailto:[EMAIL PROTECTED]] Sent: Thursday, October 26, 2000 5:21 PM To: CF-Talk Subject: CF_QuerySort-problem (Was: SQL-SORT-Problem in a query !) o.k. I had a sorting problem yesterday but with the help of the Custom Tag CF_QuerySort I am able to sort a varchar-column in a numeric way. (This morning I thought at least !) Although right now it doesn't seem to work anymore, since in my housenumber field there are most likely values like 93-94 and 101B and so on. I don't know why exactly but he (CF) is telling me nonstop when I order via the housenumber-field in a numeric manner the following: --- Error Diagnostic Information In function ArraySort the array element at position 4 is not a numeric value The error occurred while evaluating the expression: Temp = ArraySort(MyArray, Attributes.SortType, Attributes.SortOrder) The error occurred while processing an element with a general identifier of (CFSET), occupying document position (39:1) to (39:76). --- Ideas why ? Uwe SD Solutions Lange Zeile 27 85435 Erding Fon: 08122/903791 Fax: 08122/903792 Mail: [EMAIL PROTECTED] Web: www.sdsolutions.de Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED] Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED]
RE: CF_QuerySort-problem (Was: SQL-SORT-Problem in a query !)
You might see if one of the other sort tags available at allaire would handle your content. -Original Message- From: CF-Talk [mailto:[EMAIL PROTECTED]] Sent: Thursday, October 26, 2000 5:21 PM To: CF-Talk Subject: CF_QuerySort-problem (Was: SQL-SORT-Problem in a query !) o.k. I had a sorting problem yesterday but with the help of the Custom Tag CF_QuerySort I am able to sort a varchar-column in a numeric way. (This morning I thought at least !) Although right now it doesn't seem to work anymore, since in my housenumber field there are most likely values like 93-94 and 101B and so on. I don't know why exactly but he (CF) is telling me nonstop when I order via the housenumber-field in a numeric manner the following: --- Error Diagnostic Information In function ArraySort the array element at position 4 is not a numeric value The error occurred while evaluating the expression: Temp = ArraySort(MyArray, Attributes.SortType, Attributes.SortOrder) The error occurred while processing an element with a general identifier of (CFSET), occupying document position (39:1) to (39:76). --- Ideas why ? Uwe SD Solutions Lange Zeile 27 85435 Erding Fon: 08122/903791 Fax: 08122/903792 Mail: [EMAIL PROTECTED] Web: www.sdsolutions.de Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED] Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED]
RE: CF_QuerySort-problem (Was: SQL-SORT-Problem in a query !)
The reason is that 93-94 and 101B can not be turned into numbers. The tag is probably suppose to take VARCHAR columns and make them into numeric columns only if the value of the column is a number(i.e. no A-Z or special characters. Pete -Original Message- From: CF-Talk [mailto:[EMAIL PROTECTED]] Sent: Thursday, October 26, 2000 17:21 To: CF-Talk Subject:CF_QuerySort-problem (Was: SQL-SORT-Problem in a query !) o.k. I had a sorting problem yesterday but with the help of the Custom Tag CF_QuerySort I am able to sort a varchar-column in a numeric way. (This morning I thought at least !) Although right now it doesn't seem to work anymore, since in my housenumber field there are most likely values like 93-94 and 101B and so on. I don't know why exactly but he (CF) is telling me nonstop when I order via the housenumber-field in a numeric manner the following: --- Error Diagnostic Information In function ArraySort the array element at position 4 is not a numeric value The error occurred while evaluating the expression: Temp = ArraySort(MyArray, Attributes.SortType, Attributes.SortOrder) The error occurred while processing an element with a general identifier of (CFSET), occupying document position (39:1) to (39:76). --- Ideas why ? Uwe SD Solutions Lange Zeile 27 85435 Erding Fon: 08122/903791 Fax: 08122/903792 Mail: [EMAIL PROTECTED] Web: www.sdsolutions.de Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED] Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED]
Re: SQL-SORT-Problem in a query
Thank you all, who proposed to take a custom tag. Works great with N. Weiss' tag: CF_QuerySort Uwe From: [EMAIL PROTECTED] To: "CF-Talk" [EMAIL PROTECTED] Sent: Thursday, October 26, 2000 12:53 AM Subject: RE: SQL-SORT-Problem in a query There are some custom tags that might here here: http://devex.allaire.com/developer/gallery/SearchResults.cfm?keywords=querys ortsearch=search -Original Message- From: CF-Talk [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 25, 2000 4:45 PM To: CF-Talk Subject: SQL-SORT-Problem in a query Hi, I want to order a query where housenumber (numbers of houses in streets) is the parameter. It is a text-field instead of a number because sometimes there are housenumbers like 121B etc. But when I do an order/sort like cfquery name="... ... ORDER BY housenumber desc /cfquery It is not ordering the way I want most likely because it is a text field. How can solve this ? Thanks. Uwe Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED] Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED] Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED]
CF_QuerySort-problem (Was: SQL-SORT-Problem in a query !)
o.k. I had a sorting problem yesterday but with the help of the Custom Tag CF_QuerySort I am able to sort a varchar-column in a numeric way. (This morning I thought at least !) Although right now it doesn't seem to work anymore, since in my housenumber field there are most likely values like 93-94 and 101B and so on. I don't know why exactly but he (CF) is telling me nonstop when I order via the housenumber-field in a numeric manner the following: --- Error Diagnostic Information In function ArraySort the array element at position 4 is not a numeric value The error occurred while evaluating the expression: Temp = ArraySort(MyArray, Attributes.SortType, Attributes.SortOrder) The error occurred while processing an element with a general identifier of (CFSET), occupying document position (39:1) to (39:76). --- Ideas why ? Uwe SD Solutions Lange Zeile 27 85435 Erding Fon: 08122/903791 Fax: 08122/903792 Mail: [EMAIL PROTECTED] Web: www.sdsolutions.de Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED]
SQL-SORT-Problem in a query
Hi, I want to order a query where housenumber (numbers of houses in streets) is the parameter. It is a text-field instead of a number because sometimes there are housenumbers like 121B etc. But when I do an order/sort like cfquery name="... ... ORDER BY housenumber desc /cfquery It is not ordering the way I want most likely because it is a text field. How can solve this ? Thanks. Uwe Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED]
RE: SQL-SORT-Problem in a query
Well, that should sort it; what do you mean, it's not sorting it like you want? Give us an example. -Original Message- From: CF-Talk [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 25, 2000 3:45 PM To: CF-Talk Subject: SQL-SORT-Problem in a query Hi, I want to order a query where housenumber (numbers of houses in streets) is the parameter. It is a text-field instead of a number because sometimes there are housenumbers like 121B etc. But when I do an order/sort like cfquery name="... ... ORDER BY housenumber desc /cfquery It is not ordering the way I want most likely because it is a text field. How can solve this ? Thanks. Uwe Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED] Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED]
Re: SQL-SORT-Problem in a query
David I want to have this order for the housenumbers (desc): 62 58 50 7 ... Instead this I get s.th. like: 7 62 58 50 'Cause the first digit 7 is higher than 6. There should be s.th. like numeric sort of text fields. You know what I mean ? Uwe - Original Message - From: "Hayes, David" [EMAIL PROTECTED] To: "CF-Talk" [EMAIL PROTECTED] Sent: Mittwoch, 25. Oktober 2000 22:58 Subject: RE: SQL-SORT-Problem in a query Well, that should sort it; what do you mean, it's not sorting it like you want? Give us an example. -Original Message- From: CF-Talk [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 25, 2000 3:45 PM To: CF-Talk Subject: SQL-SORT-Problem in a query Hi, I want to order a query where housenumber (numbers of houses in streets) is the parameter. It is a text-field instead of a number because sometimes there are housenumbers like 121B etc. But when I do an order/sort like cfquery name="... ... ORDER BY housenumber desc /cfquery It is not ordering the way I want most likely because it is a text field. How can solve this ? Thanks. Uwe Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED] Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED] Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED]
RE: SQL-SORT-Problem in a query
OK, now I see your problem. CF has a Val function which converts the beginning of a string to a number; if your db has such a function, you're in good shape. Add the converted value of housenumber to your select statement and sort on that. If your db doesn't have such a function, you'll have to do your manipulation in CF; you can create an array of those house numbers, convert the values using VAL, then use QueryAddColumn (4.5) to add that column to your query object. For sorting the query in CF, I understand there are some custom tags around; check the Allaire site. -Original Message- From: CF-Talk [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 25, 2000 4:29 PM To: CF-Talk Subject: Re: SQL-SORT-Problem in a query David I want to have this order for the housenumbers (desc): 62 58 50 7 ... Instead this I get s.th. like: 7 62 58 50 'Cause the first digit 7 is higher than 6. There should be s.th. like numeric sort of text fields. You know what I mean ? Uwe - Original Message - From: "Hayes, David" [EMAIL PROTECTED] To: "CF-Talk" [EMAIL PROTECTED] Sent: Mittwoch, 25. Oktober 2000 22:58 Subject: RE: SQL-SORT-Problem in a query Well, that should sort it; what do you mean, it's not sorting it like you want? Give us an example. -Original Message- From: CF-Talk [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 25, 2000 3:45 PM To: CF-Talk Subject: SQL-SORT-Problem in a query Hi, I want to order a query where housenumber (numbers of houses in streets) is the parameter. It is a text-field instead of a number because sometimes there are housenumbers like 121B etc. But when I do an order/sort like cfquery name="... ... ORDER BY housenumber desc /cfquery It is not ordering the way I want most likely because it is a text field. How can solve this ? Thanks. Uwe Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED] Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED] Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED] Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED]
RE: SQL-SORT-Problem in a query
There are some custom tags that might here here: http://devex.allaire.com/developer/gallery/SearchResults.cfm?keywords=querys ortsearch=search -Original Message- From: CF-Talk [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 25, 2000 4:45 PM To: CF-Talk Subject: SQL-SORT-Problem in a query Hi, I want to order a query where housenumber (numbers of houses in streets) is the parameter. It is a text-field instead of a number because sometimes there are housenumbers like 121B etc. But when I do an order/sort like cfquery name="... ... ORDER BY housenumber desc /cfquery It is not ordering the way I want most likely because it is a text field. How can solve this ? Thanks. Uwe Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED] Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED]
RE: Group by problem in select query
I am selecting 8 fields (one is a count) and I do not want a group by clause. Kathy, You have to have a GROUP BY if you are using COUNT(). It's an aggregate function - that means it's meaningless without a GROUP BY. What are you trying to do? Nick ** Information in this email is confidential and may be privileged. It is intended for the addressee only. If you have received it in error, please notify the sender immediately and delete it from your system. You should not otherwise copy it, retransmit it or use or disclose its contents to anyone. Thank you for your co-operation. ** -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
Re: Group by problem in select query
By their very nature aggregate functions require a group by. If you have no group by, then the entire recordset is the group and location_city would make no sense unless your entire recordset has the same value for location_city - Original Message - From: "Kathy Bergman" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, August 23, 2000 9:19 AM Subject: Group by problem in select query Is there a way around this error message? I am selecting 8 fields (one is a count) and I do not want a group by clause. Without a group by clause or if I leave one of the fields out of the group by clause, I receive this error message: Column 'location_city' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause. -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=stsbody=sts/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
Second problem with my query
Hi all, after solving the problem with converting numeric fields to string fields (I actually didn't use the Str()-function but the Format()-function to avoid a leading space) I now wonder how to avoid that ColdFusion cuts the generated line (in the following Query the header_name) after 256 Characters. Is there any chance to convert the retrieved and generated line to a LONGCHAR already in SELECT-statement? cfquery name="quicksearch" datasource="ersatzteile" SELECT eintraege.id, eintraege.fzg_typ_id, eintraege.hersteller_id, eintraege.bereich_id, eintraege.datum, eintraege.eintragstext, eintraege.preis, 'a href="suche/hersteller.cfm?fzg_typ_id=' + Format(fzg_typ.fzg_typ_id) + '"' + fzg_typ.name + '/anbsp;nbsp;|nbsp;nbsp;a href="suche/teilebereich.cfm?fzg_typ_id=' + Format(fzg_typ.fzg_typ_id) + 'hersteller_id=' + Format(hersteller.hersteller_id) + '"' + hersteller.name + '/anbsp;nbsp;|nbsp;nbsp;a href="suche/ergebnis.cfm?fzg_typ_id=' + Format(fzg_typ.fzg_typ_id) + 'hersteller_id=' + Format(hersteller.hersteller_id) + 'bereich_id=' + Format(teilebereich.bereich_id) + '"' + teilebereich.name AS header_name FROM eintraege, fzg_typ, hersteller, teilebereich WHERE eintragstext LIKE '%#FORM.suchbegriff#%' AND fzg_typ.fzg_typ_id = eintraege.fzg_typ_id AND hersteller.hersteller_id = eintraege.hersteller_id AND teilebereich.bereich_id = eintraege.bereich_id ORDER BY fzg_typ.fzg_typ_id, hersteller.name, teilebereich.name, datum /cfquery TIA Patric -- Archives: http://www.eGroups.com/list/cf-talk To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
Problem with a query
Hi folks,
I have a problem with a query where i don't know how to group the retrieved
records.
There are four tables involved in this query (hope you can ignore the german
declarations):
1. table: eintraege (the important one)
- id (counter)
- fzg_typ_id (integer)
- hersteller_id (integer)
- bereich_id (integer)
- kunden_id (integer) (not involved in query)
- datum (datetime)
- eintragstext (memo)
- preis (text)
2. table: fzg_typ
- fzg_typ_id (counter)
- name (text)
3. table: hersteller
- hersteller_id (counter)
- name (text)
- fzg_typ_id (text) (not involved, just for storing a list)
4. table: teilebereich
- bereich_id (counter)
- name (text)
- fzg_typ_id (text) (not involved, just for storing a list)
Now I have the following query which perfectly gives me the records I need:
cfquery name="quicksearch" datasource="ersatzteile"
SELECTeintraege.id,
eintraege.fzg_typ_id,
eintraege.hersteller_id,
eintraege.bereich_id,
eintraege.datum,
eintraege.eintragstext,
eintraege.preis,
fzg_typ.name AS f_name,
hersteller.name AS h_name,
teilebereich.name AS t_name
FROM eintraege,
fzg_typ,
hersteller,
teilebereich
WHEREeintragstext LIKE '%#FORM.suchbegriff#%'
AND fzg_typ.fzg_typ_id = eintraege.fzg_typ_id
AND hersteller.hersteller_id = eintraege.hersteller_id
AND teilebereich.bereich_id = eintraege.bereich_id
ORDER BY fzg_typ.name,
hersteller.name,
teilebereich.name
/cfquery
My problem is that I don't really want the retrieved records to be ordered
like stated in the ORDER BY-clause but to be grouped by these fields so that
the output could look like this:
{Heading} f_name1 / h_name3 / t_name9
{entry 1}
{entry 2}
{entry 3}
{Heading} f_name1 / h_name3 / t_name11
{entry 1}
{entry 2}
{Heading} f_name2 / h_name1 / t_name4
{entry 1}
{entry 2}
{entry 3}
{entry 4}
...
...
...
could anyone please point me to the right direction. I searched the web and
the Allaire docs now for almost a week and couldn't really find something
that works.
TIA
Patric
--
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
Re: Problem with a query
Well Stephen, if you were here I would kiss your feet. Worked almost as you described but I had to alter the expression for combining the name-fields in the query like this: fzg_typ.name + ' | ' + hersteller.name + ' | ' + teilebereich.name AS header_name I think the next I post my questions earlier to the list. A big "Thank You" goes out to you! Patric -- Archives: http://www.eGroups.com/list/cf-talk To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
