Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 248
  • Last Modified:

(cfif) check query for date, not working

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
urbanc
Asked:
urbanc
  • 4
1 Solution
 
Tacobell777Commented:
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
 
Renante EnteraSenior PHP DeveloperCommented:
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
 
Renante EnteraSenior PHP DeveloperCommented:
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
[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

 
urbancAuthor Commented:
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
 
Renante EnteraSenior PHP DeveloperCommented:
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
 
Renante EnteraSenior PHP DeveloperCommented:
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now