Solved

(cfif) check query for date, not working

Posted on 2004-09-26
9
243 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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

Plug and play, no additional software required!

The ATEN UE3310 USB3.1 Gen1 Extender Cable allows users to extend the distance between the computer and USB devices up to 10 m (33 ft). The UE3310 is a high-quality, cost-effective solution for professional environments such as hospitals, factories and business facilities.

Question has a verified solution.

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

A web service (http://en.wikipedia.org/wiki/Web_service) is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

617 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