|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
Stop making mediocre tutorials.The best tutorials are video! Camtasia Studio makes it easy to create engaging, buzz-building screen videos at any size, in any popular format. Download the free trial!
|
|
#1
April 12th, 2004, 10:09 PM
|
||||
|
||||
|
Query not executing properly, can't find error...
It irks me that ColdFusion doesn't actually give you the EXACT line where the error occurs, but I understand why. However, this is making my error very difficult to find. Here's the situation:
ERD for the database: http://cislab1.cbpa.louisville.edu/...t5/petstore.jpg Code:
<CFINCLUDE template="includes/header.cfm">
<HTML>
<BODY>
<!-- execute search query -->
<CFQUERY datasource="#DataSource#" name="merchandiseOrderSearch">
<CFIF len(trim(#form.itemID#)) GT 0 OR
len(trim(#form.description#)) GT 0 OR
len(trim(#form.category#)) GT 0>
SELECT MO.PONumber, MO.OrderDate, E.LastName, S.Name, MO.ShippingCost
FROM Supplier S, Employee E, MerchandiseOrder MO, Merchandise M
WHERE
<!-- inter-table requirements -->
MO.SupplierID = S.SupplierID AND
MO.EmployeeID = E.EmployeeID
<!-- search requirements -->
<CFIF len(trim(#form.itemID#)) GT 0>
AND M.ItemID = #form.itemID#
</CFIF>
<CFIF len(trim(#form.description#)) GT 0>
AND M.Description LIKE '%#form.description#%'
</CFIF>
<CFIF len(trim(#form.category#)) GT 0>
AND M.Category IN(#ListQualify(form.Category,"'",",","ALL")#)
</CFIF>
ORDER BY #form.sortby# #form.sorttype#
<CFELSEIF len(trim(#form.itemID#)) LTE 0 AND
len(trim(#form.description#)) LTE 0 AND
len(trim(#form.category#)) LTE 0>
SELECT MO.PONumber, MO.OrderDate, E.LastName, S.Name, MO.ShippingCost
FROM Supplier S, Employee E, MerchandiseOrder MO
WHERE MO.SupplierID = S.SupplierID AND
MO.EmployeeID = E.EmployeeID
ORDER BY #form.sortby# #form.sorttype#
</CFIF>
</CFQUERY>
<TABLE width="700">
<TR>
<TH>P.O. Number</TH>
<TH>Order Date</TH>
<TH>Employee Name</TH>
<TH>Supplier Name</TH>
<TH>Shipping Cost</TH>
</TR>
<CFOUTPUT query="merchandiseOrderSearch">
<TR>
<TD>#PONumber#</TD>
<TD>#OrderDate#</TD>
<TD>#LastName#</TD>
<TD>#Name#</TD>
<TD>#LSNumberFormat(ShippingCost, "$___,___.__")#</TD>
</TR>
</CFOUTPUT>
</TABLE>
</BODY>
</HTML>
And the error message: Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]Line 4: Incorrect syntax near '<'. The Error Occurred in D:\Inetpub\wwwroot\cmschu04\a7\merchandiseOrderSearch2.cfm: line 57 55 : <TH>Supplier Name</TH> 56 : <TH>Shipping Cost</TH> 57 : </TR> 58 : <CFOUTPUT query="merchandiseOrderSearch"> 59 : <TR> -------------------------------------------------------------------------------- SQL SELECT MO.PONumber, MO.OrderDate, E.LastName, S.Name, MO.ShippingCost FROM Supplier S, Employee E, MerchandiseOrder MO, Merchandise M WHERE MO.SupplierID = S.SupplierID AND MO.EmployeeID = E.EmployeeID AND M.Category IN('Dog') ORDER BY MO.PONumber ASC DATASOURCE CIS310A7 VENDORERRORCODE 170 SQLSTATE HY000 The page can be found here... part one does fine, but part 2 does not... try it here: http://cislab2.cbpa.louisville.edu/...OrderSearch.cfm Can anyone help? Thanks in advance!!! I've searched forever to no avail.... probably just a stupid error as always 
__________________
Reinventing the wheel again
|
|
#2
April 12th, 2004, 10:45 PM
|
|||
|
|||
|
Can you take the same SQL and run it directly against the SQL Server database using the SQL Query Analyzer in the SQL Management Console?
Also, while it doesn't throw an error, all the extra pound signs just serve to make things less clear. Everywhere you've got <CFIF len(trim(#form.itemID#)) GT 0> You only need: <CFIF len(trim(form.itemID)) GT 0> As a general rule, you only need pound signs when outputting a value inside a cfoutput tag, or when evaluating a dynamic expression. Just FYI. Post back if the same SQL statement also fails directly against the SQL Server.
|
|
#3
April 13th, 2004, 01:41 AM
|
||||
|
||||
|
Thanks for the info about the ## stuff. I'm still kinda new to CF as I'm sure you can tell.
SQL Server 2000 had no problems executing the query when only category 'Dog' was selected from the form (no value inserted for either of the other two fields). Results are as follows: 4 2004-04-18 00:00:00 James Harrison 42.0751 7 2004-06-07 00:00:00 Farris Harrison 14.2196 17 2004-08-09 00:00:00 Farris Harrison 42.2475 18 2004-03-27 00:00:00 James Harrison 60.1296 19 2004-02-04 00:00:00 Gibson Harrison 29.9436 6 2004-04-22 00:00:00 O'Connor Hughes 21.8741 10 2004-05-04 00:00:00 Reasoner Hughes 31.0438 12 2004-10-06 00:00:00 Carpenter Hughes 33.7617 20 2004-08-11 00:00:00 Carpenter Hughes 21.6762 23 2004-09-19 00:00:00 Reasoner Hughes 43.6357 13 2004-10-06 00:00:00 Farris Love 40.3896 14 2004-05-20 00:00:00 James Love 29.2682 16 2004-02-27 00:00:00 James Love 32.4214 22 2004-02-25 00:00:00 James Love 32.5530 3 2004-08-19 00:00:00 Hopkins Osborne 39.0153 21 2004-06-29 00:00:00 Carpenter Osborne 46.9106 2 2004-05-28 00:00:00 Reasoner Parrish 28.6399 8 2004-07-01 00:00:00 Reeves Parrish 39.2962 9 2004-04-27 00:00:00 Reasoner Parrish 33.8920 11 2004-06-18 00:00:00 James Parrish 25.3818 24 2004-10-27 00:00:00 O'Connor Parrish 18.6879 25 2004-08-31 00:00:00 Eaton Parrish 28.3478 1 2004-03-06 00:00:00 Hopkins Rhodes 33.5449 5 2004-08-18 00:00:00 Carpenter Rhodes 32.3296 15 2004-09-30 00:00:00 O'Connor Rhodes 37.9814 Don't see any problem with it... ??? So the question arises, if SQL Server can execute it, why does CF disagree with it so badly? Why is the error on line 58 at the </TR> tag? Seems really odd to me 
|
|
#4
April 13th, 2004, 04:17 AM
|
||||
|
||||
|
sql server can execute some of the generated queries, but not all of them, and i suspect it is because of bad column names hardcoded into your CFQUERY and/or form
for example, if you leave the form exactly as is, it runs change the Order by radio button on the form to Employee Name and it dies why? because it's trying to sort by E.Name when the column is actually called E.LastName change the Order by radio button to ORder Date and it dies why? because it's trying to sort by S.OrderDate when the S table doesn't even have an OrderDate column et cetera i think the other errors are probably similar
|
|
#5
April 13th, 2004, 08:18 AM
|
|||
|
|||
|
Quote:
|
|
#6
April 13th, 2004, 08:53 PM
|
||||
|
||||
|
Can you do CFIF's within a CFQUERY?
Already fixed the incorrect column references, thanks R. Still giving me error though.
Can you do CFIF's within a CFQUERY? I've got... Code:
<CFQUERY datasource="#DataSource#" name="merchandiseOrderSearch"> SELECT MO.PONumber, MO.OrderDate, E.LastName, S.Name, MO.ShippingCost FROM Supplier S, Employee E, MerchandiseOrder MO, Merchandise M WHERE <!-- inter-table requirements --> MO.SupplierID = S.SupplierID AND MO.EmployeeID = E.EmployeeID <!-- search requirements --> <CFIF len(trim(#form.itemID#)) GT 0> AND M.ItemID = #form.itemID# </CFIF> <CFIF len(trim(#form.description#)) GT 0> AND M.Description LIKE '%#form.description#%' </CFIF> <CFIF len(trim(#form.category#)) GT 0> AND M.Category IN(#ListQualify(form.Category,"'",",","ALL")#) </CFIF> ORDER BY #form.sortby# #form.sorttype# </CFQUERY> That's where my problem seems to be... having a problem executing database query at line 39, which is the </CFQUERY> tag... this is really starting to get to me now. Thanks everyone
|
|
#7
April 13th, 2004, 09:06 PM
|
||||
|
||||
|
of course you can do CFIFs within a CFQUERY
not sure about the html comments, though try making them CF comments and see if that helps are you still getting the dump of the query string? when you say a query "doesn't work" it helps to show us the query string
|
|
#8
April 13th, 2004, 09:08 PM
|
||||
|
||||
|
one thing that still puzzles me is that you aren't joining the M table to any other table
this means it is cross-joined to all the other rows but the really weird thing is, you don't SELECT any columns from M so, um, why is it even involved in the query?
|
|
#9
April 13th, 2004, 11:04 PM
|
||||
|
||||
|
Quote:
Because in the last CFIF, there is a possible where statement involving the Merchandise table (M).
|
|
#10
April 13th, 2004, 11:08 PM
|
||||
|
||||
|
AHA!!! It's the HTML comments that did it. Took them out and now everything works. I have way too many rows, as you explained about the "cross join", but that's an easy fix. GOOD LORD that was a hellacious fix. Simple, but took forever to find! UGH!
Thanks everyone...
|
|
#11
April 13th, 2004, 11:23 PM
|
||||
|
||||
|
Quote:
This is true kiteless, I have used PHP, which has DECENT error reporting, but NOTHING like this... quite nice. 
|
|
| Viewing: Dev Shed Forums > Programming Languages - More > ColdFusion Development > Query not executing properly, can't find error... |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
