(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.
urbancAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Renante EnteraConnect With a Mentor Senior 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
 
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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.