RE: weird CFQuery problem
replace strSQLwhere with preserveSingleQuotes(strSQLwhere) +---+ Bryan Love Macromedia Certified Professional Internet Application Developer Database Analyst Telecommunication Systems [EMAIL PROTECTED] +---+ ...'If there must be trouble, let it be in my day, that my child may have peace'... - Thomas Paine, The American Crisis -Original Message- From: Snyder, Jason [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 15, 2002 9:57 AM To: CF-Talk Subject: weird CFQuery problem I am generating an SQL statement partially inside of a CFQuery tag and partially in CFScript. When I try to execute the query w/ a WHERE clause, it bombs out. If I try w/o a WHERE clause it is happy. If I try w/o a WHERE clause and w/ an ORDER BY clause it is happy. The error message I get when it bombs is: - Error Diagnostic Information ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC Microsoft Access Driver] Syntax error in query expression '((qryShipSearch.SalesNumber LIKE ''%01A778%''))'. The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (101:1) to (101:73). - If I use CFOutput and copy/paste to print the whole SQL statement to the screen, go into source view of the output, copy that SQL statement into another query and then run it, it works. Here is the query that bombs: - cfquery name=GetResults datasource=#session.DSN_Ship# dbtype=ODBC SELECT #scrTable#.ActShipDate, #scrTable#.RPO_Num, #scrTable#.CustPartNum, #scrTable#.TRW_PartNum, #scrTable#.SerialNum, #scrTable#.SalesNumber, #scrTable#.ShippingRequestNum FROM #scrTable# #strSQLWhere# #strSQLOrder#; /cfquery - Here is the one that works: - cfquery name=GetResults datasource=#session.DSN_Ship# dbtype=ODBC SELECT qryShipSearch.ActShipDate, qryShipSearch.RPO_Num, qryShipSearch.CustPartNum, qryShipSearch.TRW_PartNum, qryShipSearch.SerialNum, qryShipSearch.SalesNumber, qryShipSearch.ShippingRequestNum FROM qryShipSearch WHERE ((qryShipSearch.SalesNumber LIKE '%01A778%')) ; /cfquery - __ Get Your Own Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb 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: weird CFQuery problem
You don't need those double parens around your where clause. Try this. cfquery name=GetResults datasource=#session.DSN_Ship# dbtype=ODBC SELECT qryShipSearch.ActShipDate, qryShipSearch.RPO_Num, qryShipSearch.CustPartNum, qryShipSearch.TRW_PartNum, qryShipSearch.SerialNum, qryShipSearch.SalesNumber, qryShipSearch.ShippingRequestNum FROM qryShipSearch WHERE qryShipSearch.SalesNumber LIKE '%01A778%' /cfquery -Original Message- From: Snyder, Jason [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 15, 2002 10:57 AM To: CF-Talk Subject: weird CFQuery problem I am generating an SQL statement partially inside of a CFQuery tag and partially in CFScript. When I try to execute the query w/ a WHERE clause, it bombs out. If I try w/o a WHERE clause it is happy. If I try w/o a WHERE clause and w/ an ORDER BY clause it is happy. The error message I get when it bombs is: - Error Diagnostic Information ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC Microsoft Access Driver] Syntax error in query expression '((qryShipSearch.SalesNumber LIKE ''%01A778%''))'. The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (101:1) to (101:73). - If I use CFOutput and copy/paste to print the whole SQL statement to the screen, go into source view of the output, copy that SQL statement into another query and then run it, it works. Here is the query that bombs: - cfquery name=GetResults datasource=#session.DSN_Ship# dbtype=ODBC SELECT #scrTable#.ActShipDate, #scrTable#.RPO_Num, #scrTable#.CustPartNum, #scrTable#.TRW_PartNum, #scrTable#.SerialNum, #scrTable#.SalesNumber, #scrTable#.ShippingRequestNum FROM #scrTable# #strSQLWhere# #strSQLOrder#; /cfquery - Here is the one that works: - cfquery name=GetResults datasource=#session.DSN_Ship# dbtype=ODBC SELECT qryShipSearch.ActShipDate, qryShipSearch.RPO_Num, qryShipSearch.CustPartNum, qryShipSearch.TRW_PartNum, qryShipSearch.SerialNum, qryShipSearch.SalesNumber, qryShipSearch.ShippingRequestNum FROM qryShipSearch WHERE ((qryShipSearch.SalesNumber LIKE '%01A778%')) ; /cfquery - __ Why Share? Dedicated Win 2000 Server · PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionc 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: weird CFQuery problem
Bingo! -Original Message- From: Bryan Love [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 15, 2002 10:11 AM To: CF-Talk Subject: RE: weird CFQuery problem replace strSQLwhere with preserveSingleQuotes(strSQLwhere) +---+ Bryan Love Macromedia Certified Professional Internet Application Developer Database Analyst Telecommunication Systems [EMAIL PROTECTED] +---+ ...'If there must be trouble, let it be in my day, that my child may have peace'... - Thomas Paine, The American Crisis -Original Message- From: Snyder, Jason [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 15, 2002 9:57 AM To: CF-Talk Subject: weird CFQuery problem I am generating an SQL statement partially inside of a CFQuery tag and partially in CFScript. When I try to execute the query w/ a WHERE clause, it bombs out. If I try w/o a WHERE clause it is happy. If I try w/o a WHERE clause and w/ an ORDER BY clause it is happy. The error message I get when it bombs is: - Error Diagnostic Information ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC Microsoft Access Driver] Syntax error in query expression '((qryShipSearch.SalesNumber LIKE ''%01A778%''))'. The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (101:1) to (101:73). - If I use CFOutput and copy/paste to print the whole SQL statement to the screen, go into source view of the output, copy that SQL statement into another query and then run it, it works. Here is the query that bombs: - cfquery name=GetResults datasource=#session.DSN_Ship# dbtype=ODBC SELECT #scrTable#.ActShipDate, #scrTable#.RPO_Num, #scrTable#.CustPartNum, #scrTable#.TRW_PartNum, #scrTable#.SerialNum, #scrTable#.SalesNumber, #scrTable#.ShippingRequestNum FROM #scrTable# #strSQLWhere# #strSQLOrder#; /cfquery - Here is the one that works: - cfquery name=GetResults datasource=#session.DSN_Ship# dbtype=ODBC SELECT qryShipSearch.ActShipDate, qryShipSearch.RPO_Num, qryShipSearch.CustPartNum, qryShipSearch.TRW_PartNum, qryShipSearch.SerialNum, qryShipSearch.SalesNumber, qryShipSearch.ShippingRequestNum FROM qryShipSearch WHERE ((qryShipSearch.SalesNumber LIKE '%01A778%')) ; /cfquery - __ Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusiona 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: A cfquery problem.
Vin There isn't an easy way of doing this in SQL. The usual way AFAIK is to grab the entire query each time and then navigate to the right row of the query results. If you cache the CFQUERY it's not a problem preformance-wise. Otherwise it is! Nick -Original Message- From: Vincent [mailto:[EMAIL PROTECTED]] Sent: Friday, September 29, 2000 6:36 AM To: CF-Talk Subject: A cfquery problem. hi, I guess this is more of an SQL problem... what I wanted was for me to be able to select a group of rows in my cfquery statement. eg: cfquery name="get_items" datasource="users" maxrows = 10 SELECT itemnum FROM items WHERE user_id = 12345 /cfquery Now this will get me the top 10 rows, yes! Now what Im looking at is rows 10-20, then 20-30etc Is this possible. I know of one way, but its kinda a long cut. thanks regards, VIN ** 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: A cfquery problem.
I suggest trying to utilise the STARTROW and MAXROWS with your query. CFOUTPUT QUERY="query_name" GROUP="query_column" GROUPCASESENSITIVE="yes/no" STARTROW="start_row" MAXROWS="max_rows_output" -Original Message- From: Vincent [SMTP:[EMAIL PROTECTED]] Sent: 29 September 2000 06:36 To: CF-Talk Subject: A cfquery problem. hi, I guess this is more of an SQL problem... what I wanted was for me to be able to select a group of rows in my cfquery statement. eg: cfquery name="get_items" datasource="users" maxrows = 10 SELECT itemnum FROM items WHERE user_id = 12345 /cfquery Now this will get me the top 10 rows, yes! Now what Im looking at is rows 10-20, then 20-30etc Is this possible. I know of one way, but its kinda a long cut. thanks regards, VIN -- 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. --- Any opinions expressed in this message are those of the individual and not necessarily the company. This message and any files transmitted with it are confidential and solely for the use of the intended recipient. If you are not the intended recipient or the person responsible for delivering to the intended recipient, be advised that you have received this message in error and that any use is strictly prohibited. Sapphire Technologies Ltd http://www.sapphire.net -- 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: A cfquery problem.
Vincent I don't know of any way you can do this in SQL but just a simple query and a post works great. This should work for you. Adjust the cache time span to meet your needs. -Mark :o) Learning = (Asking + Teaching) CFQUERY NAME="get_items" DATASOURCE="users" CACHEDWITHIN="#CreateTimeSpan(0,0,5,0)#" SELECT itemnum, user_id FROM items WHERE user_id = 12345 /CFQUERY CFSET MaxRows = 10 CFPARAM NAME="Begin" DEFAULT="1" CFPARAM NAME="itemnum" DEFAULT="" CFPARAM NAME="user_id " DEFAULT="" CFOUTPUT QUERY="get_items" STARTROW=#Begin# MAXROWS=#MaxRows# #itemnum#br /CFOUTPUT CFSET NextTen = Begin + MaxRows CFOUTPUT CFIF NextTen LTE get_items.RecordCount FORM ACTION="get_items.cfm" METHOD="post" INPUT TYPE="hidden" NAME="ItemNumber" VALUE="#itemnum#" INPUT TYPE="hidden" NAME="Begin" VALUE="#NextTen#" INPUT TYPE="submit" VALUE="Next #MaxRows#" /FORM /CFIF /CFOUTPUT - Original Message - From: Vincent [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Thursday, September 28, 2000 10:36 PM Subject: A cfquery problem. hi, I guess this is more of an SQL problem... what I wanted was for me to be able to select a group of rows in my cfquery statement. eg: cfquery name="get_items" datasource="users" maxrows = 10 SELECT itemnum FROM items WHERE user_id = 12345 /cfquery Now this will get me the top 10 rows, yes! Now what Im looking at is rows 10-20, then 20-30etc Is this possible. I know of one way, but its kinda a long cut. thanks regards, VIN -- 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. -- 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: A cfquery problem.
On 9/29/00, Vincent penned: cfquery name="get_items" datasource="users" maxrows = 10 SELECT itemnum FROM items WHERE user_id = 12345 /cfquery Now this will get me the top 10 rows, yes! Now what Im looking at is rows 10-20, then 20-30etc Is this possible. I know of one way, but its kinda a long cut. Hi Vin. Maxrows will go in the cfoutput portion of your query. cfoutput query="get_items" maxrows="#maxrows#" startrow="#startrow#" Startrow will be the dynamic variable. On the initial search, you will pass a value of "1". Or you can pass nothing and use cfparam to set a default of "1". CFPARAM NAME="startrow" DEFAULT="1" CFPARAM NAME="maxrows" DEFAULT="10" Then to do your next and previous buttons, you will generally use 2 forms and place this before them: cfset PrevStart = StartRow - MaxRows cfset NextStart = StartRow + MaxRows cfif PrevStart GTE 1 form action="samepage.cfm" method="post" (form stuff) input type="hidden" name="startrow" value="#PrevStart#" input type="hidden" name="maxrows" value="#maxrows#" input type="Submit" value="Previous #maxrows# Records" /form /cfif cfif NextStart LTE Get_Items.RecordCount form action="samepage.cfm" method="post" (form stuff) input type="hidden" name="startrow" value="#NextStart#" input type="hidden" name="maxrows" value="#maxrows#" input type="Submit" value="Next #maxrows# Records" /form /cfif You can also do some neat stuff like checking if there are less than maxrows records left and show the Next button accordingly. cfset remaining = Get_Items.RecordCount - startrow + 1 This will go AFTER the cfset PrevStart/NextStart code. In a search returning 28 records, when you return records 11 thru 20, the value of remaining would be 8. 28 - 21 (the value of startrow) = 7 + 1 = 8 Then you could set accordingly. cfif remaining GT maxrows input type="Submit" value="Next #maxrows# Records" cfelseif remaining LTE maxrows and remaining GT "1" input type="Submit" value="Final #remaining# Records" cfelseif remaining is "1" input type="Submit" value="Final Record" /cfif You can use the same parameters at the top of the search results: CFIF Get_Items.Recordcount IS "1" Displaying 1 item found CFELSE Displaying records #startrow# thru cfif Get_Items.Recordcount LTE (startrow + maxrows - 1) #Get_Items.Recordcount#cfelse #Abs(startrow + maxrows - 1)#/cfif of #Get_Items.Recordcount# records found /CFIF HTH -- Bud Schneehagen - Tropical Web Creations _/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/ ColdFusion Solutions / eCommerce Development [EMAIL PROTECTED] http://www.twcreations.com/ 954.721.3452 -- 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: A cfquery problem.
In SQL Server you can do something like this: -- creates empty table / must be fast since it locks sys tables SELECT itemnum, IDENTITY(int) AS rownumber INTO #TempItems FROM items WHERE 1 = 2 INSERT #TempItems SELECT itemnum FROM items WHERE user_id = 12345 Then to get a particular set of data - say rcords btw 100 and 200 - you can write: SELECT items.* FROM items INNER JOIN #TempItems ON #TempItems.itemnumber = items.itemnumber WHERE rownumber = 100 AND rownumber = 200 This avoids transferring of large results between the db and CF. You can put all this in a stored procedure and just pass a few paramaters @user_id, @startrow, @endrow.. Oracle has better support for this - you can use rowid directly. P. -Original Message- From: Bud [mailto:[EMAIL PROTECTED]] Sent: Friday, September 29, 2000 5:09 AM To: CF-Talk Subject: Re: A cfquery problem. On 9/29/00, Vincent penned: cfquery name="get_items" datasource="users" maxrows = 10 SELECT itemnum FROM items WHERE user_id = 12345 /cfquery Now this will get me the top 10 rows, yes! Now what Im looking at is rows 10-20, then 20-30etc Is this possible. I know of one way, but its kinda a long cut. Hi Vin. Maxrows will go in the cfoutput portion of your query. cfoutput query="get_items" maxrows="#maxrows#" startrow="#startrow#" Startrow will be the dynamic variable. On the initial search, you will pass a value of "1". Or you can pass nothing and use cfparam to set a default of "1". CFPARAM NAME="startrow" DEFAULT="1" CFPARAM NAME="maxrows" DEFAULT="10" Then to do your next and previous buttons, you will generally use 2 forms and place this before them: cfset PrevStart = StartRow - MaxRows cfset NextStart = StartRow + MaxRows cfif PrevStart GTE 1 form action="samepage.cfm" method="post" (form stuff) input type="hidden" name="startrow" value="#PrevStart#" input type="hidden" name="maxrows" value="#maxrows#" input type="Submit" value="Previous #maxrows# Records" /form /cfif cfif NextStart LTE Get_Items.RecordCount form action="samepage.cfm" method="post" (form stuff) input type="hidden" name="startrow" value="#NextStart#" input type="hidden" name="maxrows" value="#maxrows#" input type="Submit" value="Next #maxrows# Records" /form /cfif You can also do some neat stuff like checking if there are less than maxrows records left and show the Next button accordingly. cfset remaining = Get_Items.RecordCount - startrow + 1 This will go AFTER the cfset PrevStart/NextStart code. In a search returning 28 records, when you return records 11 thru 20, the value of remaining would be 8. 28 - 21 (the value of startrow) = 7 + 1 = 8 Then you could set accordingly. cfif remaining GT maxrows input type="Submit" value="Next #maxrows# Records" cfelseif remaining LTE maxrows and remaining GT "1" input type="Submit" value="Final #remaining# Records" cfelseif remaining is "1" input type="Submit" value="Final Record" /cfif You can use the same parameters at the top of the search results: CFIF Get_Items.Recordcount IS "1" Displaying 1 item found CFELSE Displaying records #startrow# thru cfif Get_Items.Recordcount LTE (startrow + maxrows - 1) #Get_Items.Recordcount#cfelse #Abs(startrow + maxrows - 1)#/cfif of #Get_Items.Recordcount# records found /CFIF HTH -- Bud Schneehagen - Tropical Web Creations _/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/ ColdFusion Solutions / eCommerce Development [EMAIL PROTECTED] http://www.twcreations.com/ 954.721.3452 -- 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. -- 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.
