#1
  1. Business Analyst
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2004
    Location
    The 'Ville
    Posts
    1,087
    Rep Power
    22

    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/g...5/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/c...rderSearch.cfm

    Can anyone help? Thanks in advance!!! I've searched forever to no avail.... probably just a stupid error as always
    Discontent is the first necessity of progress. - Edison
  2. #2
  3. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,265
    Rep Power
    968
    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.
  4. #3
  5. Business Analyst
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2004
    Location
    The 'Ville
    Posts
    1,087
    Rep Power
    22
    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
    Discontent is the first necessity of progress. - Edison
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,179
    Rep Power
    4279
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,265
    Rep Power
    968
    Originally Posted by mateoc15
    It irks me that ColdFusion doesn't actually give you the EXACT line where the error occurs, but I understand why.
    Also I should note that the reason CF can't give an exact line number in this case is because the error is coming from the SQL Server, which CF has no knowledge of the internals of. Overall, CF has by far the best debugging and error information of any application server I have ever seen.
  10. #6
  11. Business Analyst
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2004
    Location
    The 'Ville
    Posts
    1,087
    Rep Power
    22

    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
    Discontent is the first necessity of progress. - Edison
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,179
    Rep Power
    4279
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,179
    Rep Power
    4279
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  16. #9
  17. Business Analyst
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2004
    Location
    The 'Ville
    Posts
    1,087
    Rep Power
    22
    Originally Posted by r937
    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?
    Because in the last CFIF, there is a possible where statement involving the Merchandise table (M).
    Discontent is the first necessity of progress. - Edison
  18. #10
  19. Business Analyst
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2004
    Location
    The 'Ville
    Posts
    1,087
    Rep Power
    22
    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...
    Discontent is the first necessity of progress. - Edison
  20. #11
  21. Business Analyst
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2004
    Location
    The 'Ville
    Posts
    1,087
    Rep Power
    22
    Originally Posted by kiteless
    Also I should note that the reason CF can't give an exact line number in this case is because the error is coming from the SQL Server, which CF has no knowledge of the internals of. Overall, CF has by far the best debugging and error information of any application server I have ever seen.
    This is true kiteless, I have used PHP, which has DECENT error reporting, but NOTHING like this... quite nice.
    Discontent is the first necessity of progress. - Edison

IMN logo majestic logo threadwatch logo seochat tools logo