Skip to main content
June 3, 2008
Question

Master-detail page with SQL querty

  • June 3, 2008
  • 4 replies
  • 533 views
This is the hyperlink I use on my master page to link my database to the detail page. A visitor to my site clicks on a record entry on the master page. This causes a detail page to open which displays further details about this record.

<td><a href="Education - Bulletin Board - Replies Table of Contents.cfm?ReplyRecordID=#DisplayTable.ID#">View replies</a></td>

The code on the detail page is:

cfparam name="PageNum_EducationForumRS" default="1">
<cfparam name="URL.ReplyRecordID" default="1">
<cfquery name="CountOfResponses" datasource="Education Forum">
SELECT ParentID, Count(ParentID) as CountOfResponses
FROM Messages
GROUP BY PARENTID
HAVING ParentID <> 0
ORDER BY ParentID DESC
</cfquery>
<cfquery name="MessagesWithResponses" datasource="Education Forum">
SELECT Messages.ID, Messages.ParentID, Messages.Subject, Messages.Author, Messages.Message, Messages.Email, Messages.Posted
FROM Messages
WHERE ParentID=#URL.ReplyRecordID#
</cfquery>

When this code runs I get the following error: "Syntax error (missing operator) in query expression". The error references the "WHERE ParentID=#URL.ReplyRecordID#" line.

The ParentID field is a numerical field. If I replace #URL.ReplyRecordID# with a numerical value the query will run.

I would be very grateful for suggestions.
    This topic has been closed for replies.

    4 replies

    June 7, 2008
    Thanks for the reply. Since posting this I've tried using a session variable instead of a master/detail page. Unfortunately, I'm now having a problem with a WHERE clause (I've just posted the problem).

    If I can't slove the WHERE problem, I may well go back to the master/detail page and take up your suggestion on the coding.

    Thanks very much for youre advice.
    Inspiring
    June 4, 2008
    It is a good coding practice if you use cfqueryparam instead of direct variable name as bellow.

    WHERE ParentID=<cfqueryparam cfsqltype="cf_sql_integer" value="#URL.ReplyRecordIDl#">
    Make sure the URL param been passed to the page.
    Inspiring
    June 4, 2008
    The error should show you the sql as well. Does it have a number where you expect to see the variable?
    Inspiring
    June 4, 2008
    Is the link on the master page enclosed in cfoutput tags ?

    Does the url parameter in the detail page contain a value ?

    Ken