Thanks,
Mark Fuqua
ps notice that all the where clauses have 1>0 as a condition. This must be
some sort of default (i.e. all queries have a where clause and if nothing
else is provided, 1>0 is provided to keep from throwing an error.) If it is
a default, maybe there is just a typo somewhere in the code?! Or I guess
the where 1>0 is an error check...if 1>0 than the query was constructed as
expected, if it is 0>1, then the query was constructed in such a way as to
throw a unhandled exception so instead, it just kills the query???
queryResult (Datasource=mdfinish, Time=0ms, Records=4, Cached Query) in
C:\CFusionMX7\wwwroot\mdprofinish\components\DatabaseBlocks.cfc @
16:12:46.046
SELECT NewAppointment.NewAppointmentID AS
NewAppointment_NewAppointmentID,NewAppointment.AppointmentDate AS
NewAppointment_AppointmentDate,NewAppointment.AppointmentTime AS
NewAppointment_AppointmentTime,PlumUser.UserName AS
PlumUser_UserName,NewAppointment.AppointmentWith AS
NewAppointment_AppointmentWith
FROM NewAppointment LEFT OUTER JOIN PlumUser ON
NewAppointment.AppointmentWith = PlumUser.UserID
WHERE 1 > 0 AND appointmentFor = 2
ORDER BY NewAppointment.appointmentDate DESC
queryResult (Datasource=mdfinish, Time=0ms, Records=5, Cached Query) in
C:\CFusionMX7\wwwroot\mdprofinish\components\DatabaseBlocks.cfc @
16:12:46.046
SELECT tasks.TaskId AS tasks_TaskId,tasks.TaskDueDate AS
tasks_TaskDueDate,TaskStatus.TaskStatus AS
TaskStatus_TaskStatus,tasks.TaskStatus AS tasks_TaskStatus
FROM tasks LEFT OUTER JOIN TaskStatus ON tasks.TaskStatus =
TaskStatus.TaskStatus
WHERE 1 > 0 AND TaskAssignedTo = 2
ORDER BY tasks.TaskDueDate DESC
queryResult (Datasource=mdfinish, Time=0ms, Records=2, Cached Query) in
C:\CFusionMX7\wwwroot\mdprofinish\components\DatabaseBlocks.cfc @
16:12:47.047
SELECT Message.NewMessageId AS
Message_NewMessageId,Message.NewMessageDate
AS Message_NewMessageDate,PlumUser.UserName AS
PlumUser_UserName,Message.messageSubject AS
Message_messageSubject,Message.NewMessageFrom AS Message_NewMessageFrom
FROM Message LEFT OUTER JOIN PlumUser ON Message.NewMessageFrom
=
PlumUser.UserID
WHERE 1 > 0 AND Message.NewmessageTo = 2 AND
Message.newMessageStatus =
'new'
ORDER BY Message.NewMessageDate desc
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of Jeff
Fleitz
Sent: Thursday, August 18, 2005 3:11 PM
To: [email protected]
Subject: Re: [plum] date ranges and whereClauseExtension with AND
Well, I have spent most of the day trying to track this one down, and I have
not been able to resolve it yet, although I
believe the bug resides in the SearchFormFieldValidate include template;
specifically the block of code at about line
216. The code is trying to set the value of the _CriteriaUpper and
_CriteriaLower values, which refer to hidden form
fields passed in the DisplaySearchDateTimeControl custom tag, but it is not
working.
Another interesting tidbit is that I am setting the type attribute in the
DisplaySearchDateTimeControl control in the
search from to 'date' instead of 'datetime', the list page shows the type as
datetime, so it is getting converted in
there somewhere.
Adam, David, you guys got any suggestions?
Jeff
Mark Fuqua wrote:
Hey Jeff,
I did not try <cfdump> but I do have debuging enabled which shows the
following form and session variables from the appontmentList page which is
what the search page posts to: (ignor the area, job and time card session
stuff, it is left over from other recently visited pages within the same
application...It would seem, strictly from a common sence point of view,
that form.appointmentdate_criterialower and
form.appointmentdate_criteriaUpper should have a value). Will <cfdump>
show
me more than this.
Interestingly enough, it is obvious why the query (copied from debug stuff
and pasted at the end of this email) returns 0 records, it uses: WHERE 0>1
as the Where clause. Guess the chances of that returning records is
fairly
slim.
Form Fields:
APPOINTMENTDATE_CRITERIALOWER=
APPOINTMENTDATE_CRITERIALOWERDAY=01
APPOINTMENTDATE_CRITERIALOWERMONTH=8
APPOINTMENTDATE_CRITERIALOWERYEAR=2005
APPOINTMENTDATE_CRITERIAUPPER=
APPOINTMENTDATE_CRITERIAUPPERDAY=17
APPOINTMENTDATE_CRITERIAUPPERMONTH=8
APPOINTMENTDATE_CRITERIAUPPERYEAR=2005
APPOINTMENTDATE_OPERATOR=BETWEEN
APPOINTMENTDATE_TYPE=datetime
DISPLAYALL=0
FIELDNAMES=FROMSEARCH,RAND,DISPLAYALL,APPOINTMENTDATE_TYPE,APPOINTMENTDATE_O
PERATOR,APPOINTMENTDATE_CRITERIALOWER,APPOINTMENTDATE_CRITERIALOWERMONTH,APP
OINTMENTDATE_CRITERIALOWERDAY,APPOINTMENTDATE_CRITERIALOWERYEAR,APPOINTMENTD
ATE_CRITERIAUPPER,APPOINTMENTDATE_CRITERIAUPPERMONTH,APPOINTMENTDATE_CRITERI
AUPPERDAY,APPOINTMENTDATE_CRITERIAUPPERYEAR
FROMSEARCH=1
RAND=C75B34FC-B379-0F42-61240690D704C4C0
Session Variables:
areaid=19
areascriteria=areas.jobid = 20
areaslist=26~25~19~18~17
areaslist.cfm|search|areas.jobid=20;EQUAL;integer
areassortorder=Areas.AreaGroup ASC, Areas.AreaId DESC
assignedestimateitemscriteria=AssignedEstimateItems.AreaId = 19
assignedestimateitemslist=58~59~61
assignedestimateitemssortorder=
cachetimespan=0.0833333333333
cfid=2000
cftoken=84235458
fixedestimateitemscriteria=1 > 0
fixedestimateitemslist=13~14~15~21~19~18~17~16~9~20
fixedestimateitemssortorder=FinishTypes.JobType ASC,
FinishTypes.FinishType
ASC, FixedEstimateItems.ItemType ASC
jobid=20
jobscriteria=1 > 0
jobslist=20~17~19~18~21
jobssortorder=Jobs.JobStatus ASC, Jobs.Customer ASC
loginstatus=Ready
messagecriteria=1 > 0 AND NewmessageTo = 2
messagelist=16~15~14~12
messagesortorder=Message.NewMessageDate desc
nameofloggedinuser=Mark Fuqua
newappointmentcriteria=0 > 1 AND appointmentfor = 2
newappointmentlist=
newappointmentlist.cfm|search|appointmentdate|range=%20 ;%20;datetime
newappointmentsortorder=NewAppointment.AppointmentDate ASC
plumuserroles=CONTRIBUTOR,ADMIN
previousmodule=
sessionid=MDPROFINISH_2000_84235458
targetformvars=
targetlocation=
targeturlvars=
taskscriteria=1 > 0 AND TaskAssignedTo = 2
taskslist=7~6~5~4
taskssortorder=tasks.TaskDueDate DESC
timecardcriteria=timecard.job = 19
timecardlist=11~14
timecardlist.cfm|search|reportdate|range=%20 ;%20;datetime
timecardlist.cfm|search|timecard.associate=%20;EQUAL;integer
timecardlist.cfm|search|timecard.job=19;EQUAL;integer
timecardlist.cfm|search|timecard.timecardid=%20;EQUAL;integer
timecardsortorder=TimeCard.Associate ASC, TimeCard.ReportDate ASC
urltoken=CFID=2000&CFTOKEN=84235458
userid=2
queryResult (Datasource=mdfinish, Time=90ms, Records=0) in
C:\CFusionMX7\wwwroot\mdprofinish\components\DatabaseBlocks.cfc @
22:28:03.003
SELECT NewAppointment.NewAppointmentID AS
NewAppointment_NewAppointmentID,NewAppointment.AppointmentDate AS
NewAppointment_AppointmentDate,Associates.AssociateName AS
Associates_AssociateName,PlumUser.UserName AS
PlumUser_UserName,TimeDuration.NumberOfHours AS
TimeDuration_NumberOfHours,NewAppointment.AppointmentFor AS
NewAppointment_AppointmentFor,NewAppointment.AppointmentWith AS
NewAppointment_AppointmentWith,NewAppointment.AppointmentDuration AS
NewAppointment_AppointmentDuration
FROM ((NewAppointment LEFT OUTER JOIN Associates ON
NewAppointment.AppointmentFor = Associates.AssociateContactRefNo) LEFT
OUTER
JOIN PlumUser ON NewAppointment.AppointmentWith = PlumUser.UserID) LEFT
OUTER JOIN TimeDuration ON NewAppointment.AppointmentDuration =
TimeDuration.NumberOfHours
WHERE 0 > 1
ORDER BY NewAppointment.AppointmentDate ASC
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of Jeff
Fleitz
Sent: Wednesday, August 17, 2005 9:43 PM
To: [email protected]
Subject: Re: [plum] date ranges and whereClauseExtension with AND
Hi Mark,
Did you try using the cfdump suggestion I made in your post of 8/7 on this
topic?
I haven't had a chance to try a search form with a date range yet, but I
will build one tomorrow and see if I get the
same behavior.
Jeff
Mark Fuqua wrote:
Hey there,
I am having some troubles with date ranges on PLUM search forms and also
with trying to get multiple filters on a whereClauseExtension of the
<cf_displayList>.
When I use search by date range on a search form, the list posts with no
records and the message that "you didn't enter and search criteria".
When I use the whereClauseExtension of a <cf_displayList>, and I try and
use
AND, it doesn't seem to work. For Example:
whereClauseExtension="messageSentTo = #session.UserId# AND messageStatus =
'new'"
Chances are good that these problems are of my own making, however, I am
just wondering whether anyone else has had such problems. Searching for
solutions is almost becoming fun. I am just trying to make sure solutions
exist.
Thanks all,
Mark Fuqua
**********************************************************************
You can subscribe to and unsubscribe from lists, and you can change
your subscriptions between normal and digest modes here:
http://www.productivityenhancement.com/support/DiscussionListsForm.cfm
**********************************************************************
**********************************************************************
You can subscribe to and unsubscribe from lists, and you can change
your subscriptions between normal and digest modes here:
http://www.productivityenhancement.com/support/DiscussionListsForm.cfm
**********************************************************************
**********************************************************************
You can subscribe to and unsubscribe from lists, and you can change
your subscriptions between normal and digest modes here:
http://www.productivityenhancement.com/support/DiscussionListsForm.cfm
**********************************************************************
**********************************************************************
You can subscribe to and unsubscribe from lists, and you can change
your subscriptions between normal and digest modes here:
http://www.productivityenhancement.com/support/DiscussionListsForm.cfm
**********************************************************************
**********************************************************************
You can subscribe to and unsubscribe from lists, and you can change
your subscriptions between normal and digest modes here:
http://www.productivityenhancement.com/support/DiscussionListsForm.cfm
**********************************************************************
