kkhipple
asked on
CF Oracle date woes
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?
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
how do i have coldfusion conform the date entry into whats allowed on oracle db
thanks Hart for correcting me.
Regards,
---Pinal
Regards,
---Pinal
ASKER
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#</CFOU TPUT>
<CFOUTPUT>#dt_start#</CFOU TPUT>
<CFOUTPUT>#dt_end#</CFOUTP UT>
<CFQUERY DATASOURCE="lieu" NAME="addEvent">
INSERT INTO lieu_events
VALUES ('#event_id#','#SESSION.li eu_user_na me#',
<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?
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#</CFOU
<CFOUTPUT>#dt_start#</CFOU
<CFOUTPUT>#dt_end#</CFOUTP
<CFQUERY DATASOURCE="lieu" NAME="addEvent">
INSERT INTO lieu_events
VALUES ('#event_id#','#SESSION.li
<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?
ASKER
figured it out... for those that similiar problems....
<CFPARAM name="dtstamp" default="#dateformat(zDate ,'dd-mmm-y y')# #timeformat(zTime,'HH:mm:s s')#">
<CFOUTPUT>#event_id#</CFOU TPUT>
<CFOUTPUT>#dt_start#</CFOU TPUT>
<CFOUTPUT>#dt_end#</CFOUTP UT>
<CFQUERY DATASOURCE="lieu" NAME="addEvent">
INSERT INTO lieu_events
VALUES ('#event_id#','#SESSION.li eu_employe e_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.
<CFPARAM name="dtstamp" default="#dateformat(zDate
<CFOUTPUT>#event_id#</CFOU
<CFOUTPUT>#dt_start#</CFOU
<CFOUTPUT>#dt_end#</CFOUTP
<CFQUERY DATASOURCE="lieu" NAME="addEvent">
INSERT INTO lieu_events
VALUES ('#event_id#','#SESSION.li
to_date('#dtstamp#','YYYY-
to_date('#dtstamp#','YYYY-
'#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.
ASKER
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:s s')#">
<!--- insert into db --->
<CFQUERY DATASOURCE="lieu" NAME="addEvent">
INSERT INTO lieu_events
VALUES ('#event_id#','#SESSION.li eu_employe e_id#',
to_date('#dt_start#','YYYY -DD-MM HH24:MI:SS'),
to_date('#dt_end#','YYYY-D D-MM HH24:MI:SS'),
'#FORM.title#', '#FORM.comment#', 'PENDING')
</CFQUERY>
<!---- create the start and end date/time---->
<CFPARAM name="dt_start" default="#dateformat(zDate
<CFPARAM name="dt_end" default="#dateformat(zDate
<!--- insert into db --->
<CFQUERY DATASOURCE="lieu" NAME="addEvent">
INSERT INTO lieu_events
VALUES ('#event_id#','#SESSION.li
to_date('#dt_start#','YYYY
to_date('#dt_end#','YYYY-D
'#FORM.title#', '#FORM.comment#', 'PENDING')
</CFQUERY>
glad that it worked for you.
Good day,
Regards,
---Pinal
Good day,
Regards,
---Pinal
ASKER