Solved

CF Oracle date woes

Posted on 2004-09-05
10
229 Views
Last Modified: 2013-12-24
I am creating an event calendar where users will be able to enter in an event title, comments and then that events start and end date/time.

I need help creating a form that accepts the following submission information:
title                     VARCHAR
comments           VARCHAR
dt_start               DATE
dt_end                DATE


When the form submits I'd like it to check if there is already an event within dt_start and dt_end. If so, do not submit it and give the user an error.   If not, then insert into the database.

How do I do this?   I'm completely lost and would appreciate a simple walkthrough.  Anyone?
0
Comment
Question by:kkhipple
  • 5
  • 3
  • 2
10 Comments
 
LVL 21

Assisted Solution

by:pinaldave
pinaldave earned 50 total points
ID: 11984595


<form action="action page" post="post">
<input type="text" name="" value="">
<input type="text" name="" value="">
<input type="text" name="" value="">
<input type="text" name="" value="">
<input type="submit" type="submit" value="submit">
</form>

now on your action page
<cfquery name="select_q" datasource="dsn">
select * from your table where dt_start <= #form.dt_start# and dt_end >= #form.dt_end#
</cfquery>

<cfif select_q.recordcount > 0 >
you can not insert your query
</cfelse>
<cfquery name-"insert_q" datasrouce="dsn">
your insert query
</cfquery>
0
 
LVL 11

Accepted Solution

by:
hart earned 200 total points
ID: 11988209
some corrections pinal

he wants to check events in between the two dates...

and why use select * in a query where u r checking count...

Select * should be avoided if possible...

<cfquery name="Qry_CheckEvent" datasource="dsn">
    select count(title) from your table
    where dt_start >= <CFQUERYPARAM CFSQLTYPE="CF_SQL_DATE" VALUE="#form.dt_start#"> and dt_end <= <CFQUERYPARAM CFSQLTYPE="CF_SQL_DATE" VALUE="#form.dt_end#">
</cfquery>

now this will check if theres any event that is scheduled between the start and end date..
but if u r checking for events that just start between these dates then change the query a little...

<cfquery name="Qry_CheckEvent" datasource="dsn">
    select count(title) from your table
    where dt_start >= <CFQUERYPARAM CFSQLTYPE="CF_SQL_DATE" VALUE="#form.dt_start#"> and dt_start <= <CFQUERYPARAM CFSQLTYPE="CF_SQL_DATE" VALUE="#form.dt_end#">
</cfquery>



Regards
Hart
0
 
LVL 5

Author Comment

by:kkhipple
ID: 11990070
how do i also verify that they are entering the right date/time format?
0
 
LVL 11

Expert Comment

by:hart
ID: 11994977
u can do it when the form is submitted...

you can use javascript to check if the data entered is proper or not...

One of the way is give the calender pop ups...
Other way would be to do a server side check and see if the data that has been sent in is as per the format....

Also you could give drop downs for dd mm and yyyy in the form which will make the user only select an appropriate date

Regards
Hart
0
 
LVL 5

Author Comment

by:kkhipple
ID: 11996227
how do i have coldfusion conform the date entry into whats allowed on oracle db
0
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)

 
LVL 21

Expert Comment

by:pinaldave
ID: 11998085
thanks Hart for correcting me.
Regards,
---Pinal
0
 
LVL 5

Author Comment

by:kkhipple
ID: 12000821
here's where im having my problem....

here is the code when the submit happens:

<CFQUERY DATASOURCE="lieu" NAME="getMaxId">
      SELECT max(id) as max_id FROM lieu_events
</CFQUERY>

<CFSET event_id = getMaxId.max_id + 1>
<CFSET dt_start = 'DATEFORMAT(zDate, "mm/dd/yyyy") TIMEFORMAT(zTime, "hh:mm:ss")'>
<CFSET dt_end = 'DATEFORMAT(zDate, "mm/dd/yyyy") TIMEFORMAT(zTime, "hh:mm:ss")'>

<CFOUTPUT>#event_id#</CFOUTPUT>
<CFOUTPUT>#dt_start#</CFOUTPUT>
<CFOUTPUT>#dt_end#</CFOUTPUT>

<CFQUERY DATASOURCE="lieu" NAME="addEvent">
      INSERT INTO lieu_events
      VALUES ('#event_id#','#SESSION.lieu_user_name#',
            <CFQUERYPARAM VALUE = "dt_start" CFSQLType = "CF_SQL_DATE">,
            <CFQUERYPARAM VALUE = "dt_end" CFSQLType = "CF_SQL_DATE">,
            '#FORM.title#', '#FORM.comment#', 'PENDING')
</CFQUERY>



However, this produces the following error:

Error Occurred While Processing Request
Error Diagnostic Information
CFQUERYPARAM data conversion error


There is a data conversion error for CFQUERYPARAM #1.

SQL = "INSERT INTO lieu_events VALUES ('8','khippl-k', _CF_:?_, _CF_:?_, '12312', 'asdfasdf', 'PENDING')"

Query Parameter Value(s) -

Parameter #1 = dt_start

Parameter #2 = dt_end
 

Anyone can point out what's wrong with code?
0
 
LVL 5

Author Comment

by:kkhipple
ID: 12000925
figured it out... for those that similiar problems....

<CFPARAM name="dtstamp" default="#dateformat(zDate,'dd-mmm-yy')# #timeformat(zTime,'HH:mm:ss')#">

<CFOUTPUT>#event_id#</CFOUTPUT>
<CFOUTPUT>#dt_start#</CFOUTPUT>
<CFOUTPUT>#dt_end#</CFOUTPUT>

<CFQUERY DATASOURCE="lieu" NAME="addEvent">
      INSERT INTO lieu_events
      VALUES ('#event_id#','#SESSION.lieu_employee_id#',
            to_date('#dtstamp#','YYYY-MM-DD HH24:MI:SS'),
            to_date('#dtstamp#','YYYY-MM-DD HH24:MI:SS'),
            '#FORM.title#', '#FORM.comment#', 'PENDING')
</CFQUERY>


this atleast does the insert. now im trying to figure out how to add a duration (FORM.duration) to the start time.
0
 
LVL 5

Author Comment

by:kkhipple
ID: 12001265
finally after some date insert and retrieval issues... here's what works for me. thanks pinaldave and hart.  sometimes we all just need a little direction.....  here's another snippet, with similiar issues.


<!---- create the start and end date/time---->
<CFPARAM name="dt_start" default="#dateformat(zDate,'YYYY-DD-MM')# #timeformat(t_start,'HH:mm:ss')#">
<CFPARAM name="dt_end" default="#dateformat(zDate,'YYYY-DD-MM')# #timeformat(t_end,'HH:mm:ss')#">

<!--- insert into db --->
<CFQUERY DATASOURCE="lieu" NAME="addEvent">
      INSERT INTO lieu_events
      VALUES ('#event_id#','#SESSION.lieu_employee_id#',
            to_date('#dt_start#','YYYY-DD-MM HH24:MI:SS'),
            to_date('#dt_end#','YYYY-DD-MM HH24:MI:SS'),
            '#FORM.title#', '#FORM.comment#', 'PENDING')
</CFQUERY>
0
 
LVL 21

Expert Comment

by:pinaldave
ID: 12001271
glad that it worked for you.
Good day,
Regards,
---Pinal
0

Featured Post

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.

Question has a verified solution.

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

Suggested Solutions

This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

910 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

25 Experts available now in Live!

Get 1:1 Help Now