Queried variable 'undefined'

[crux_online]

Hello All,

There seems to be a problem with the following query: If I remove the WHERE
clause, all records are returned and properly formatted later in the page.
However, with the WHERE clause present, CF returns an error stating that
EventSchedDate is undefined.

The terribly convoluted equation is for the creation of a sort of numerical
'absolute' date (like a Julian Date) and may very well be what is causing the
problem, but I can't see how.

Furthermore, the same equation is being used in the output of the query later
in the page for testing purposes as the results are correct.

Could there be an issue with actually performing certain functions within the
WHERE clause?

I'll get the coffee ready...

Thanks,
Robert Nicholas



<cfquery name="getEvents" datasource="#DataSource#">
SELECT EventName, EventSchedDate, EventDesc, EventFee
FROM tblEventSchedule
WHERE '#dFirst#' <=
'#(w-2)*7+dayofweek(EventSchedDate)+3+(year(EventSchedD ate)-2001)*365#'
AND '#(w-2)*7+dayofweek(EventSchedDate)+3+(year(EventSchedD ate)-2001)*365#'
<= '#dLast#'
</cfquery>

[philh]

You're using the table field "EventSchedDate" in a ColdFusion output section
(delimited with the pound signs). This doesn't work. You have to use a value
that CF understands in the context of the template in which you're running this
query. That is, "EventSchedDate" must be a ColdFusion variable in any of the
scopes available to the template for you to employ it between the pound signs.

Or, you can use native database functions and syntax to replace what you're
doing with ColdFusion functions in the WHERE clause.

[jdeline]

You need to get rid of the single quotes around your field names in your WHERE
clause. And take the calculations out of the WHERE clause and create a
variable in its place. See code below.

Do the variables dFirst and dLast contain a field name? Are the literal
strings dFirst and dLast field names? Whatever, the proper syntax for your
WHERE clause is :

fieldname1 op value1 AND fieldname2 op value2

<CFSET whereValue =
(w-2)*7+dayofweek(EventSchedDate)+3+(year(EventSchedD ate)-2001)*365>

WHERE fieldname1<= '#whereValue#' AND fieldname2 <= '#whereValue#'

[crux_online]

Originally posted by: jdeline
You need to get rid of the single quotes around your field names in your WHERE
clause. And take the calculations out of the WHERE clause and create a
variable in its place. See code below.

Do the variables dFirst and dLast contain a field name? Are the literal
strings dFirst and dLast field names? Whatever, the proper syntax for your
WHERE clause is :

fieldname1 op value1 AND fieldname2 op value2

**********************

That being the case, I've tried to rework it.

dFirst and dLast are variables that are set using CFSET at the beginning of
the page and represent the Julian-type Dates I wrote of earlier.

Given is the new code fragment containing the query, but now I get a 'missing
operator' error. Now, when I think 'operator', I think '=', '>', etc., and
I've tried those as well (I'm now attempting to use LT, GT, and so on).
Clearly that is not the problem; there is an underlying failure on my part to
understand the required syntax.

Thanks again,

Robert Nicholas


THE ERROR:

[MERANT][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access
Driver] Syntax error (missing operator) in query expression 'EventSchedDate GT
'{ts '2005-11-06 00:00:00'}' AND EventSchedDate LT '{ts '2005-12-10
00:00:00'}''.

THE CODE:

<cfquery name="getEvents" datasource="#DataSource#">
SELECT EventName, EventSchedDate, EventDesc, EventFee
FROM tblEventSchedule
WHERE EventSchedDate GT '#CreateDate(y,m,day(dFirst))#'
AND EventSchedDate LT '#CreateDate(y,m+1,day(dLast))#'
</cfquery>

[Kronin555]

<cfquery name="getEvents" datasource="#DataSource#">
SELECT EventName, EventSchedDate, EventDesc, EventFee
FROM tblEventSchedule
WHERE EventSchedDate > '#CreateODBCDate(CreateDate(y,m,day(dFirst)))#'
AND EventSchedDate < '#CreateODBCDate(CreateDate(y,m+1,day(dLast)))#'
</cfquery>

[crux_online]

Originally posted by: Kronin555
<cfquery name="getEvents" datasource="#DataSource#">
SELECT EventName, EventSchedDate, EventDesc, EventFee
FROM tblEventSchedule
WHERE EventSchedDate > '#CreateODBCDate(CreateDate(y,m,day(dFirst)))#'
AND EventSchedDate < '#CreateODBCDate(CreateDate(y,m+1,day(dLast)))#'
</cfquery>

Nope, same error. It's really quite puzzling, don't you think?

Robert

[Kronin555]

Which "same error"?

Missing operand? or undefined variable?

[crux_online]

Missing Operator. Definitely a missing operator.

Under what condition might this show up, beyond the obvious?

Thanks
Robert Nichlas

[paross1]

You might want to remove the single quotes from around the
'#CreateODBCDate(CreateDate(y,m,day(dFirst)))#' statements, since I am assuming
that EventSchedDate is a date/time object and not varchar/character.

Phil

[crux_online]

Thanks to you all for your suggestions, they all got me incrementally to the
point where no errors occur. Too bad there are few 'affordable' references to
which one can quickly refer.

Thanks again, everyone. Now the real work begins.

Robert Nicholas