Re: updating my date_added from a form fails
try wrapping Form.date_added in a createODBCDateTime() function (or just a createODBCDate() if you don't need time). On Mon, 20 Dec 2004 09:03:27 -0500, Daniel Kessler [EMAIL PROTECTED] wrote: I have an Oracle database with a record where when I update it with now() it works. From that point on, for editing, I carry the date_added through a hidden form field which has the source of INPUT TYPE='Hidden' NAME='date_added' VALUE='2004-12-20 08:50:51.0' When I try and use that hidden form field with the date to update the record, I get the following error: [Macromedia][Oracle JDBC Driver][Oracle]ORA-00933: SQL command not properly ended :Error Executing Database Query. update giving_leavesset name = 'daniel', date_added = 2004-12-20 08:50:51.0, recipients_fname = 'Robins', recipients_lname = 'Ventura', recipients_mi = 'b', address= '10472 outter_space ct', city = 'scaggsville', state = 'md', zip= '20723', phone= '301-776-3926', email= '[EMAIL PROTECTED]', charge_card= 'VISA/MC', charge_number= '12345679', charge_month= 02, charge_year= 2006, check_information= '', leaf_choices= '5,3,4', line_1= 'We love your mustache', line_2= 'Mr. Ventura', line_3= 'and your fashion sense.', line_4= 'Love dad', line_5= '2004', fund= 'Health and Society Visiting Professorship', fund_other= '', status= 'Reserved' where leaf_number = 5 As I said, this works if I insert Now() and this date started as a Now() update. This is an Oracle database and the field is of type DATE. Without that field in the update the query works fine. Here's the query that's failing with that above error: CFQUERY NAME=makeEdit DATASOURCE=dpch update giving_leaves set name = '#Form.name#', date_added = #Form.date_added#, recipients_fname = '#Form.recipients_fname#', recipients_lname = '#Form.recipients_lname#', recipients_mi = '#Form.recipients_mi#', address= '#Form.address#', city = '#Form.city#', state = '#Form.state#', zip= '#Form.zip#', phone= '#Form.phone#', email= '#Form.email#', charge_card= '#Form.charge_card#', charge_number= '#Form.charge_number#', charge_month= #Form.charge_month#, charge_year= #Form.charge_year#, check_information= '#Form.check_information#', leaf_choices= '#leaf_choices#', line_1= '#Form.line_1#', line_2= '#Form.line_2#', line_3= '#Form.line_3#', line_4= '#Form.line_4#', line_5= '#Form.line_5#', fund= '#Form.fund#', fund_other= '#Form.fund_other#', status= '#Form.status#' where leaf_number = #Form.leaf_number# /CFQUERY I've looked at this for awhile and hope I'm missing something obvious. Thanks for any comments. -- Daniel Kessler Department of Public and Community Health University of Maryland Suite 2387 Valley Drive College Park, MD 20742-2611 301-405-2545 Phone www.phi.umd.edu ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:188233 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: updating my date_added from a form fails
Thanks Charlie, that worked great. I guess I assumed that since form value number works fine as a number, date would do the same. Guess it works this way since date is more complex. I appreciate the help. May your code go smoothly today. try wrapping Form.date_added in a createODBCDateTime() function (or just a createODBCDate() if you don't need time). On Mon, 20 Dec 2004 09:03:27 -0500, Daniel Kessler [EMAIL PROTECTED] wrote: ~| Special thanks to the CF Community Suite Silver Sponsor - RUWebby http://www.ruwebby.com Message: http://www.houseoffusion.com/lists.cfm/link=i:4:188236 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: updating my date_added from a form fails
Or even better, use cfqueryparam -Original Message- From: Charlie Griefer [mailto:[EMAIL PROTECTED] Sent: 20 December 2004 15:09 To: CF-Talk Subject: Re: updating my date_added from a form fails try wrapping Form.date_added in a createODBCDateTime() function (or just a createODBCDate() if you don't need time). On Mon, 20 Dec 2004 09:03:27 -0500, Daniel Kessler [EMAIL PROTECTED] wrote: I have an Oracle database with a record where when I update it with now() it works. From that point on, for editing, I carry the date_added through a hidden form field which has the source of INPUT TYPE='Hidden' NAME='date_added' VALUE='2004-12-20 08:50:51.0' When I try and use that hidden form field with the date to update the record, I get the following error: ~| Special thanks to the CF Community Suite Silver Sponsor - RUWebby http://www.ruwebby.com Message: http://www.houseoffusion.com/lists.cfm/link=i:4:188249 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: updating my date_added from a form fails
i still wrap date/time values in createODBCDate() functions inside of cfqueryparam tags. probably overkill, but old habits and all... :) On Mon, 20 Dec 2004 16:11:47 +0100, Pascal Peters [EMAIL PROTECTED] wrote: Or even better, use cfqueryparam -Original Message- From: Charlie Griefer [mailto:[EMAIL PROTECTED] Sent: 20 December 2004 15:09 To: CF-Talk Subject: Re: updating my date_added from a form fails try wrapping Form.date_added in a createODBCDateTime() function (or just a createODBCDate() if you don't need time). On Mon, 20 Dec 2004 09:03:27 -0500, Daniel Kessler [EMAIL PROTECTED] wrote: I have an Oracle database with a record where when I update it with now() it works. From that point on, for editing, I carry the date_added through a hidden form field which has the source of INPUT TYPE='Hidden' NAME='date_added' VALUE='2004-12-20 08:50:51.0' When I try and use that hidden form field with the date to update the record, I get the following error: ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:188251 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: updating my date_added from a form fails
Why are you even updating it - if you're just setting it back to the same created date? Also, you can use oracles default sysdate right in your table definition so that you don't have to add a date at all - if you are really just tracking the added date and not the updated date. - Original Message - From: daniel kessler [EMAIL PROTECTED] To: CF-Talk cf-talk@houseoffusion.com Sent: Monday, December 20, 2004 7:19 AM Subject: Re: updating my date_added from a form fails Thanks Charlie, that worked great. I guess I assumed that since form value number works fine as a number, date would do the same. Guess it works this way since date is more complex. I appreciate the help. May your code go smoothly today. try wrapping Form.date_added in a createODBCDateTime() function (or just a createODBCDate() if you don't need time). On Mon, 20 Dec 2004 09:03:27 -0500, Daniel Kessler [EMAIL PROTECTED] wrote: ~| Special thanks to the CF Community Suite Silver Sponsor - New Atlanta http://www.newatlanta.com Message: http://www.houseoffusion.com/lists.cfm/link=i:4:188253 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: updating my date_added from a form fails
Why are you even updating it - if you're just setting it back to the same created date? Also, you can use oracles default sysdate right in your table definition so that you don't have to add a date at all - if you are really just tracking the added date and not the updated date. I'm updating it because initially, the 603 records (giving tree leaves) that I need are previously entered as blank. I then do an insert through an update. In this case though, they can change their leaf number, so I update the previous one to blank and update another to have the new date, so I'm basically carrying the info over to a different record.. So it's not the same date, but really overwriting the null value of the new record (wow lotsa quotes in this reply). And right, I should've used sysdate instead of now(), but I didn't think of it as I did the writing. ~| Special thanks to the CF Community Suite Silver Sponsor - CFDynamics http://www.cfdynamics.com Message: http://www.houseoffusion.com/lists.cfm/link=i:4:188286 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
