Solved

(cfif) check query for date, not working

Posted on 2004-09-26
9
236 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
Give your grad a cloud of their own!

With up to 8TB of storage, give your favorite graduate their own personal cloud to centralize all their photos, videos and music in one safe place. They can save, sync and share all their stuff, and automatic photo backup helps free up space on their smartphone and tablet.

 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

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…
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…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

920 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now