Solved

(cfif) check query for date, not working

Posted on 2004-09-26
9
240 Views
Last Modified: 2013-12-24
Ok, I've racked my brain trying just about every combination except the right one!:) and its really starting to get to me...

Basicly what I am trying to do, I have a form where the user selects a project, which onchange submits itself, and does a query based on that selection.
the next field is a calendar, the user selects a date. now when the form is submitted, I want to check the db to see if there is already an instance of that date, if there is, only insert part of the form, if there isn't an instance in the db of that specific date, insert the whole form. the code is as follows:

<cfquery datasource="intranet" name="chk_sl">
select sl_id, date
from shiftlog sl
where date = form.date;
</cfquery>
<cfif chk_sl.date eq form.date>
<cfquery datasource="intranet">
INSERT INTO shiftlog_details
(sl_id, proj_num, supervisor, stime, etime, ts)
VALUES
('#chk_sl.sl_id#','#form.proj_num#','#form.supervisor#','0000-00-00 #form.stime#:00','0000-00-00 #form.etime#:00',#now()#)
</cfquery>

...

<cfelseif chk_sl.date neq form.date>
<cfquery datasource="intranet">
INSERT INTO shiftlog
(date, status, createdby, ts)
VALUES
(#form.date#,'In Progress','#request.factory.getUsers(filter='userID=#session.user.userID#').firstName# #request.factory.getUsers(filter='userID=#session.user.userID#').lastName#',#now()#)
</cfquery>
<cfquery datasource="intranet" name="get_last_sl">
select max(sl_id) as sl_id
from shiftlog sl;
</cfquery>
<cfquery datasource="intranet">
INSERT INTO shiftlog_details
(sl_id, proj_num, supervisor, stime, etime, ts)
VALUES
('#get_last_sl.sl_id#','#form.proj_num#','#form.supervisor#','0000-00-00 #form.stime#:00','0000-00-00 #form.etime#:00',#now()#)
</cfquery>


now with this I keep getting multiple entries of the same date, its asif the cfif isn't working for some reason, and inserts no matter what.
sorry for the messy code, I appologize if its hard to read/follow.
0
Comment
Question by:urbanc
  • 4
9 Comments
 
LVL 17

Expert Comment

by:Tacobell777
ID: 12157011
you might want to change this
<cfif chk_sl.date eq form.date>
to this

<cfif NOT chk_sl.recordCount>

also

select sl_id, date
from shiftlog sl
where date = form.date;

select sl_id, date
from shiftlog sl
where year(date) = year(form.date)
and month(date) = month(form.date)
and day(date) = day(form.date)
0
 
LVL 14

Expert Comment

by:Renante Entera
ID: 12157564
Yah! I agree with Tacobell777 but I have some corrections.  

Instead of : <cfif NOT chk_sl.recordCount>  

The correct is : <cfif chk_sl.recordCount>  <!--- which is the replacement of "<cfif chk_sl.date eq form.date>" --->

And : <cfelse> <!--- which is the replacement of "<cfelseif chk_sl.date neq form.date>", since it is already understood that no record is found. --->

With the conditional statement "<cfelseif chk_sl.date neq form.date>", this will always return true which causes you inserting multiple entries with the same date.

In addition to that, I have another workaround.  

Change your code :
   <cfquery datasource="intranet" name="chk_sl">
   select sl_id, date
   from shiftlog sl
   where date = form.date;
   </cfquery>

With this :
   <cfquery datasource="intranet" name="chk_sl">
   select sl_id, date
   from shiftlog sl
   where date = '#DateFormat(form.date,"yyyy-mm-dd")#';
   </cfquery>

Hope this helps you.


Goodluck!
eNTRANCE2002 :-)
0
 
LVL 14

Accepted Solution

by:
Renante Entera earned 250 total points
ID: 12157593
So, most probably.  You will have your final code like this :

<cfquery datasource="intranet" name="chk_sl">
select sl_id, date
from shiftlog sl
where date = '#DateFormat(form.date,"yyyy-mm-dd")#';
</cfquery>

<cfif chk_sl.recordcount>
<cfquery datasource="intranet">
INSERT INTO shiftlog_details
(sl_id, proj_num, supervisor, stime, etime, ts)
VALUES
('#chk_sl.sl_id#','#form.proj_num#','#form.supervisor#','0000-00-00 #form.stime#:00','0000-00-00 #form.etime#:00',#now()#)
</cfquery>

...

<cfelseif not chk_sl.recordcount> <!--- OR "<cfelse>" alone --->
<cfquery datasource="intranet">
INSERT INTO shiftlog
(date, status, createdby, ts)
VALUES
(#form.date#,'In Progress','#request.factory.getUsers(filter='userID=#session.user.userID#').firstName# #request.factory.getUsers(filter='userID=#session.user.userID#').lastName#',#now()#)
</cfquery>
<cfquery datasource="intranet" name="get_last_sl">
select max(sl_id) as sl_id
from shiftlog sl;
</cfquery>
<cfquery datasource="intranet">
INSERT INTO shiftlog_details
(sl_id, proj_num, supervisor, stime, etime, ts)
VALUES
('#get_last_sl.sl_id#','#form.proj_num#','#form.supervisor#','0000-00-00 #form.stime#:00','0000-00-00 #form.etime#:00',#now()#)
</cfquery>


Regards,
eNTRANCE2002 :-)
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:urbanc
ID: 12160183
Thanks eNTRANCE,
I always get confused with some variables if they need quotes, or if they don't, etc.

Do you have a link which explains query.recordcount, I know its just returning a number based on the query, but I'm wondering if there are other functions like this that I don't know about. Its very handy, thanks again!

0
 
LVL 14

Expert Comment

by:Renante Entera
ID: 12165681
Hi urbanc!

Actually, don't be confused regarding variables need quotes.  Just consider the datatype of the value for your specific variable.  If it's a string or datetime then it needs to be quoted else no need.  But I would suggest to use the "<cfqueryparam>" tag.  It is embedded within the SQL of a "<cfquery>" tag.  It enables you to define query parameters and their data types.  By using it, you will not worry about using quotes.

Consider this example :

<cfquery datasource="DSN">
  INSERT INTO TableName(theString, theDate, theTime, theNumber)
  VALUES
  (
    <cfqueryparam cfsqltype="cf_sql_varchar" value="#stringValue#">,
    <cfqueryparam cfsqltype="cf_sql_date" value="#dateValue#">,
    <cfqueryparam cfsqltype="cf_sql_time" value="#timeValue#">,
    <cfqueryparam cfsqltype="cf_sql_integer" value="#numberValue#">
  )
</cfquery>

For additional information on "<cfqueryparam>" visit the site http://livedocs.macromedia.com/coldfusion/6.1/htmldocs/tags-b20.htm#wp1102474

Lastly, regarding your question about query.recordcount.  Just visit the site http://livedocs.macromedia.com/coldfusion/6.1/htmldocs/tags-b19.htm#wp1102316 for some information on "<cfquery>".


Best Regards,
eNTRANCE2002 :-)
0
 
LVL 14

Expert Comment

by:Renante Entera
ID: 12406535
Hello!

I think he must have to review the posted comments in here.  In fact, I'm guiding him into the right track.

In addition to that, I have provided some links regarding his concerns.


Regards!
eNTRANCE2002 :-)
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question