[Last Call] Learn how to a build a cloud-first strategyRegister Now

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

CFqueury update date/time field problem

Our webhost recenly upgraded to Cold Fusion MX. In the past I had been lazy and used the CFUpdate tag with no problems at all. Now I am updating the code and am having problems with CFquery and date field. This update works fine until I add the date field to it and then I get an error. Below is the form page code (the part relating to the date) and the insert page code. Any hjelp would be appreciated. I tried a few different formats of the text for updating the databse with no luck.

<CFFORM name="form1" METHOD="POST" action="party_insert.cfm?ID=#ID#">
<CFOUTPUT QUERY="partylist">

 
    <td><div align="center">      <a href="javascript:cal1.popup();">
      <cfinput name="date" type="TEXT" onfocus="javascript:vDateType='1'" onkeyup="DateFormat(this,this.value,event,false,'1')" onblur="DateFormat(this,this.value,event,true,'1')" value="#DateFormat(date, 'mm/dd/yyy')#" size="10" maxlength="10" validate="DATE" required="YES" message="THE DATE CANNOT BE LEFT BLANK.">
      <img src="http://www.newtrendz.com/tigra_calendar/img/cal.gif" width="16" height="16" border="0" alt="Click Here to Pick up the date"></a><br>
                  
    </div></td>


 </cfoutput> </CFFORM>

(It is a javascript pop up box with a calendar to select the date, even from a basic text filed the same error occurs)


Here is the insert page code:

<CFQUERY name="cfupdatereplace" datasource="newtrendz">

UPDATE temppartylist2
     SET post_approve_flag = #form.post_approve_flag#,
                                 weekly_flag = #form.weekly_flag# ,
                                 location_id = '#form.location_id#',
              party = '#form.party#'  ,
              promoter = '#form.promoter#' ,
              link = '#form.link#' ,
              image = '#form.image#' ,
              Image_approve_flag = '#form.Image_approve_flag#',
              voucher = '#form.voucher#' ,  
              voucher_value = '#form.voucher_value#',
              vouchertime = '#form.vouchertime#',
              custom_text = '#form.custom_text#',
              bgcolor = '#form.bgcolor#',
              textcolor = '#form.textcolor#',
              description = '#form.description#',
              custom_text_flag = '#form.custom_text_flag#',
              DATE = '#form.date#'
                                   
          where ID=#id#
</cfquery>



This is the error I get:

Error Executing Database Query.  
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.  
 
The error occurred in \\NAWINFS04\home\users\web\b2420\rh.carlito\security\test\party_insert.cfm: line 24
 
22 :               DATE = '#form.date#'
23 :                                    
24 :           where ID=#id#
25 : </cfquery>
26 :

 

--------------------------------------------------------------------------------
 
SQL    UPDATE temppartylist2 SET post_approve_flag = 1, weekly_flag = 0 , location_id = '74', party = 'Charm Saturdays~' , promoter = 'Nite Life Entertainment and LeJon Presents' , link = 'http://www.getanitelife.com/emailer/art/charm_nov05.jpg' , image = 'http://www.getanitelife.com/images/flyers/charm_dec05_FR400.JPG' , Image_approve_flag = '1', voucher = '0' , voucher_value = '0', vouchertime = '10', custom_text = '', bgcolor = '5/20/2003', textcolor = '', description = 'CharM~~ December 17 @ Club Glo~~ ', custom_text_flag = 'Custom', DATE = '12/17/05' where ID=1445  
DATASOURCE   newtrendz
VENDORERRORCODE   -3503
SQLSTATE   42000


 
The data passes fine as I can output it on test page when I use cfouput to see what i sbeing passed, but will not go into the database MS Access database wihtout the error. The field is a Time/Date field.
0
goldylamont
Asked:
goldylamont
  • 4
  • 4
1 Solution
 
mrichmonCommented:
Dates in access must be passed inside # not inside '

That means to get them through ColdFusion you need

DATE = ###form.date###

However, you are even better off using <cfqueryparam which will handle all the database nuances such as this for you.  ALso if you later change to SQL from Access, it will still work, where as without it you will have to change back to ' from #

See http://livedocs.macromedia.com/coldfusion/6.1/htmldocs/tags-b20.htm
0
 
goldylamontAuthor Commented:
I tried the DATE = ###form.date### and got the following error....

in regards to the cfqueryparam, would this be the correct format?
<CFQUERYPARAM CFSQLTYPE="CF_SQL_DATE" VALUE=###Form.DATE###>


Errorr

Invalid CFML construct found on line 26 at column 17.  
ColdFusion was looking at the following text:
ID

The CFML compiler was processing:

An expression that began on line 24, column 41.
Your expression might be missing an ending "#" (it might look like #expr ).
The body of a CFQUERY tag beginning on line 1, column 2.
 
 
The error occurred in \\NAWINFS04\home\users\web\b2420\rh.carlito\security\test\party_insert.cfm: line 26
 
24 :               DATE = ###form.date####
25 :                                    
26 :           where ID=#id#
27 : </cfquery>

 
0
 
goldylamontAuthor Commented:
Sorry the code I posted was from the typo with #### not 3 but thye bot gave same error
0
Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

 
mrichmonCommented:
Cfqueryparm would look like

<CFQUERYPARAM CFSQLTYPE="CF_SQL_DATE" VALUE="#Form.DATE#">
0
 
mrichmonCommented:
It should work with only 3 # on each side of the date.  Two # tell CF to actually pass the # character and then the third one tells it to evaluate the inside portion.

But cfqueryparam is the better way to go anyway...
0
 
goldylamontAuthor Commented:
Thanks,  I gave that a try and now it gave me the following error message. I have commented out all of the fields except the date one to isolate the problem.


<CFQUERY name="cfupdatereplace" datasource="newtrendz">


UPDATE temppartylist2
     SET
       <!-----
       post_approve_flag = #form.post_approve_flag#,
                                 weekly_flag = #form.weekly_flag# ,
                                 location_id = '#form.location_id#',
              party = '#form.party#'  ,
              promoter = '#form.promoter#' ,
              link = '#form.link#' ,
              image = '#form.image#' ,
              Image_approve_flag = '#form.Image_approve_flag#',
              voucher = '#form.voucher#' ,              
              voucher_value = '#form.voucher_value#',
              vouchertime = '#form.vouchertime#',
              custom_text = '#form.custom_text#',
              bgcolor = '#form.bgcolor#',
              textcolor = '#form.textcolor#',
              description = '#form.description#',
              custom_text_flag = '#form.custom_text_flag#', ---->
              DATE = <CFQUERYPARAM CFSQLTYPE="CF_SQL_DATE" VALUE="#Form.DATE#">
                                   
          where ID=#id#
</cfquery>

Error Executing Database Query.  
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.  
 
The error occurred in \\NAWINFS04\home\users\web\b2420\rh.carlito\security\test\party_insert.cfm: line 26
 
24 :               DATE = <CFQUERYPARAM CFSQLTYPE="CF_SQL_DATE" VALUE="#Form.DATE#">
25 :                                    
26 :           where ID=#id#
27 : </cfquery>
28 : <!---- <CFQUERYPARAM CFSQLTYPE="CF_SQL_DATE" VALUE=###Form.DATE###> ---->

 

--------------------------------------------------------------------------------
 
SQL    UPDATE temppartylist2 SET DATE = (param 1) where ID=1517  
DATASOURCE   newtrendz
VENDORERRORCODE   -3503
SQLSTATE   42000
 
Thanks
0
 
goldylamontAuthor Commented:
Your suggestion was helpful, but it didn't resolve the issue I was having. What fixed the problem was the [] for keywords, I figuerd out that date is a keyword and needed to be

 [date] = <CFQUERYPARAM CFSQLTYPE="CF_SQL_DATE" VALUE="#form.DATE#">

Thanks for teaching me the cfqueryparam, I had built ths site a few years ago and hadn't done much in CF since.

I will leave the question open temporarily to see if anyone elses has any comments.

Thanks
0
 
mrichmonCommented:
Sorry I thought I already mentioned that date was a keyword - I must have done that in another thread yesterday - a couple people were having that problem.

You should also watch out that description is a keyword in many databases as well.
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

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