Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

problem with updating date in cfquery using access 1

Status
Not open for further replies.
knightjb1

knightjb1

Programmer
Feb 12, 2008
27
0
0
US
Hi,
I have read some of the other posts on this and none of them have seemed to help me so I am hoping someone can tell me specifically what I am doing wrong in my own code.

First off I have a date field that I am pulling from a database by the ID of the table. Then I want the user to be able to change the date and re-submit it back to the database. Sounds simple...

This is my update query...

<cfquery datasource="DATES">
UPDATE DATES
SET DATE = #Form.DATE#>
WHERE ID = "#Form.ID#"
</cfquery>

This is my form....
input type="text" name="DATE" value="#DateFormat#" />

and this is what is generating the value in the form...

<cfquery name="getInfo" datasource="DATES">
SELECT * FROM DATES
WHERE ID = #URL.ID#
</cfquery>

It is pulling the date from the database in its format displaying it to the user allowing them to change it and putting it back in the database. I have tried every formatting method under the son to get this to work but All i ever get is "Syntax error in UPDATE statement. " but it sees the format just fine (SQL UPDATE DATES SET DATE = 2008-02-12 00:00:00.0> WHERE ID = "1")

Anyone know what could be wrong
 
Sort by date Sort by votes
oops the form is <input type="text" name="DATE" value="#DATE#" /> not dateformat.

When i say i have tried every formatting option I mean:

DateFormat in every combination
CreateODBCDate
CreateODBCDateTime
CreateDate
and i even tried using the left function to trim the end of the timestamp

Are there any more I can try?
 
Upvote 0 Downvote
The syntax error might be due to the double quotes around #Form.ID#. If the "ID" column is type varchar, use single quotes, not double quotes. If it is numeric, do not use single quotes.

That said, its generally better to use cfqueryparam, for several reasons. One reason is that it helps avoid sql injection risks. Another is that it provides some type checking.

Assuming #FORM.Date# was already validated as a valid date string, try something like this. Change the cfsqltype's as needed

Code: 
UPDATE DATES
SET DATE = <cfqueryparam value="#Form.DATE#" 
              cfsqltype="cf_sql_date">
WHERE ID = <cfqueryparam value="#Form.ID#" 
              cfsqltype="cf_sql_integer">

----------------------------------
 
Upvote 0 Downvote
By the way, DATE is often a restricted keyword in databases. Using a keyword as an object name can cause syntax errors and should be avoided. If your column really is named DATE, I would suggest changing it to something else. Example UpdatedDate or ModifiedDate

----------------------------------
 
Upvote 0 Downvote
oh yeah i noticed the quotes around the id and changed it and that didn't help anything and i tried changeing the variables to parameters and that didn't change anything either I still get the error in update statement =\

What is weird is I did an insert date on a different page and that worked just fine with the formatting and inserting it into the database but when i tried the update with the same formatting it didn't work. I also use the date in other pages as a filter in my sql statement and that also compares just fine.
 
Upvote 0 Downvote
Yeah i caught the > and fixed that too but i changed my table name and all code to diff names other than date and it worked...I should have thought that one through.

I appreciate the help i deffinitely will not do that again.
 
Upvote 0 Downvote
Hey do you know what cfparameter sql datatype to use for a boolean access datatype is. I am trying to update a checkbox using the "yes/no" option in access
 
Upvote 0 Downvote
Hey i tried sql_bit and had no success i assume it has to be input as 0 or 1 to the database when updating?
 
Upvote 0 Downvote
Heres the code

<cfquery datasource="DATES">
UPDATE DATES
SET EVENTDATE = <cfqueryparam value="#newdate#"
cfsqltype="cf_sql_date">,
TYPE = <cfqueryparam value="#Form.TYPE#"
cfsqltype="cf_sql_text">,
FULL = <cfqueryparam value="#Form.FULL#" cfsqltype="cf_sql_bit">
WHERE ID = <cfqueryparam value="#Form.ID#"
cfsqltype="cf_sql_integer">
</cfquery>

the field is FULL

Here is the form

<td>Full:</td>
<td>
<cfif #FULL# IS "Yes">
<input type="checkbox" name="FULL" checked="checked" value="1">Yes
<cfelse>
<input type="checkbox" name="FULL" value="0">Yes
</cfif>

</td>
 
Upvote 0 Downvote
Heres my new code which makes sense to me but im getting the error in sql update statement...god i hate this error lol

<td>
<input type="checkbox" name="FULL" <cfif "#FULL#" IS "YES">checked="checked" </cfif> value="#FULL#">Yes
</td>

<cfif not isdefined("Form.FULL")>
<cfset Form.Full = "No">
<cfelse>
<cfset Form.full = "Yes">
</cfif>
<cfdump var="#Form.FULL#">

<cfquery datasource="DATES">
UPDATE DATES
SET EVENTDATE = <cfqueryparam value="#newdate#"
cfsqltype="cf_sql_date">,
TYPE = <cfqueryparam value="#Form.TYPE#"
cfsqltype="cf_sql_text">,
FULL = <cfqueryparam value="#Form.FULL#" cfsqltype="cf_sql_bit">
WHERE ID = <cfqueryparam value="#Form.ID#"
cfsqltype="cf_sql_integer">
</cfquery>
 
Upvote 0 Downvote
You are probably doing this already, but you can use cfparam to set a default for the checkbox if it was not checked.

Code: 
FORM
<input type="checkbox" 
     name="FULL" 
    <cfif FULL IS "Yes">checked="checked"</cfif>   
    value="-1">  Yes


ACTION PAGE

<!--- if not checked, set default = no --->
<cfparam name="form.FULL" default="0">

----------------------------------
 
Upvote 0 Downvote
The same one as before
Syntax error in UPDATE statement.

The error occurred in C:\CFusionMX7\ line 16

14 : FULL = <cfqueryparam value="#Form.FULL#" cfsqltype="cf_sql_bit">
15 : WHERE ID = <cfqueryparam value="#Form.ID#"
16 : cfsqltype="cf_sql_integer">
17 : </cfquery>

I also tried changing it to integers, true/false, yes/no before trying to update the database with no prevail.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

mdvp
  • Locked
  • Question
Intermittent X-ref problem with figures in FM 12
Replies
0
Views
106
mdvp
mdvp
FALCONSEYE
  • Locked
  • Question
ColdFusion microservices with REST
Replies
0
Views
110
FALCONSEYE
FALCONSEYE
Mike Lewis
  • Locked
  • Question
Using Dreamweaver to maintain a site not created in Dreamweaver
Replies
2
Views
140
Mike Lewis
Mike Lewis
LyndonOHRC
  • Locked
  • Question
Need help using ImageCrop()
Replies
1
Views
43
LyndonOHRC
LyndonOHRC
jdhilljr
  • Locked
  • Question
Problem with cfquery
Replies
7
Views
63
cfSearching
cfSearching

Part and Inventory Search

Sponsor

Back
Top