Ask a Question related to Coldfusion Database Access, Design and Development.
-
Snowball2050 #1
Syntax error in INSERT INTO and UPDATE statements
maybe this is a noob question and i've been stairing at my screen too long
trying to find the error but I'm having problems using the Insert into and
update statements. I have 3 diffrent forms (insert into, update, and delete) on
the first page and depending on which form is used, a sql responce on the
second. strangely enough the delete part works fine. any help would be
appreciated.
[[[[[[[[[[[[[[[[ news.cfm (form page) ]]]]]]]]]]]]]]]]]]]
<html>
<head>
<title>IMAC Administrator - News</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body bgcolor="000066" text="#FFFFFF" link="#FFFFFF" vlink="#FFFFFF"
alink="#FFFFFF">
<cfif "#URL.ACTION#" EQ "ADD">
<cfoutput>
<h2>Add News Story</h2>
<cfform action="news2.cfm" method="post">
<table width="740">
<tr>
<td width="39">Date:</td><td width="397"><cfinput name="dmy"
value="M/D/YYYY" maxlength="10"></td>
</tr>
<tr>
<td>News:</td><td><textarea name="text" cols="100" rows="5"
wrap="OFF"></textarea></td>
</tr>
<tr>
<td><input type="hidden" name="action"
value="#url.action#"></td><td><input name="submit" type="submit" value="Add
News"></td>
</tr>
</table>
</cfform>
</cfoutput>
</cfif>
<cfif "#url.ACTION#" EQ "Modify">
<cfquery name="modify" datasource="imac">
SELECT *
From news
WHERE #URL.id# = id
</cfquery>
<cfoutput>
<h2>Modify News Story</h2>
<cfform name="modify" action="news2.cfm" method="post">
<table width="740">
<tr>
<td width="39">Date:</td><td width="397"><cfinput name="dmy"
value="#modify.dmy#" maxlength="10"></td>
</tr>
<tr>
<td>News:</td><td><textarea name="text" cols="100" rows="5"
wrap="OFF">#modify.text#</textarea></td>
</tr>
<tr>
<td><input type="hidden" name="id" value="#modify.id#"><input
type="hidden" name="action" value="#url.action#"></td><td><input name="submit"
type="submit" value="Modify News"></td>
</tr>
</table>
</cfform>
</cfoutput>
</cfif>
<cfif "#url.ACTION#" EQ "Delete">
<cfquery name="delete" datasource="imac">
SELECT *
From news
WHERE #URL.id# = id
</cfquery>
<cfoutput>
<h2>Delete News Story</h2>
<cfform name="delete" action="news2.cfm" method="post">
<table width="740">
<tr>
<td width="39">Date:</td><td width="397"><input type="text" name="dmy"
value="#delete.dmy#" maxlength="10" readonly="true"></td>
</tr>
<tr>
<td>News:</td><td><textarea name="text" cols="100" rows="5" wrap="OFF"
readonly="true">#delete.text#</textarea></td>
</tr>
<tr>
<td><input type="hidden" name="id" value="#delete.id#"><input
type="hidden" name="action" value="#url.action#"></td><td><input name="submit"
type="submit" value="Delete News" onClick="return confirm('Do you really want
to delete?')"></td>
</tr>
</table>
</cfform>
</cfoutput>
</cfif>
</body>
</html>
[[[[[[[[[[[[[[[[[[[[[ news2.cfm (sql page) ]]]]]]]]]]]]]]]]]]
<html>
<head>
<title>IMAC Administrator</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body bgcolor="#000066" text="#FFFFFF" link="#FFFFFF" vlink="#FFFFFF"
alink="#FFFFFF">
<cfif "#form.action#" EQ "add">
<cfquery datasource="imac" name="add">
INSERT INTO news (dmy, text)
VALUES (#form.dmy#, #form.text#)
</cfquery>
<cfoutput>News Added Sucessfully. <a href="home.cfm">Click here to return to
IMAC Administrator Home</a></cfoutput>
</cfif>
<cfif '#Form.action#' EQ "modify">
<cfquery datasource="imac" name="modify">
UPDATE news
SET dmy = '#form.dmy#', text = '#form.text#'
WHERE id = #form.id#
</cfquery>
<cfoutput>News Modified Sucessfully. <a href="home.cfm">Click here to return
to IMAC Administrator Home</a></cfoutput>
</cfif>
<cfif '#Form.action#' EQ "delete">
<cfquery datasource="imac" name="delete">
DELETE FROM news
WHERE id = #form.id#
</cfquery>
<cfoutput>News Deleted Sucessfully. <a href="home.cfm">Click here to return
to IMAC Administrator Home</a></cfoutput>
</cfif>
</body>
</html>
If i use the insert into form i get :
Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft
Access Driver] Syntax error in INSERT INTO statement.
The error occurred in
D:\Inetpub\Customers\viuxhosting\imac\imac-nyc.com\www\admin\news2.cfm: line 11
9 : <cfquery datasource="imac" name="add">
10 : INSERT INTO news (dmy, text)
11 : VALUES (#form.dmy#, #form.text#)
12 : </cfquery>
13 : <cfoutput>News Added Sucessfully. <a href="home.cfm">Click here to
return to IMAC Administrator Home</a></cfoutput>
SQL INSERT INTO news (dmy, text) VALUES (M/D/YYYY, )
DATASOURCE imac
VENDORERRORCODE -3502
SQLSTATE 42000
Please try the following:
* Check the ColdFusion documentation to verify that you are using the
correct syntax.
* Search the Knowledge Base to find a solution to your problem.
Browser Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.7.10)
Gecko/20050716 Firefox/1.0.6
Remote Address 24.161.80.163
Referrer [url]http://imac-nyc.com/admin/news.cfm?ACTION=add[/url]
Date/Time 11-Sep-05 12:43 PM
Stack Trace
at
cfnews22ecfm1438369281.runPage(D:\Inetpub\Customer s\viuxhosting\imac\imac-nyc.co
m\www\admin\news2.cfm:11) at
cfnews22ecfm1438369281.runPage(D:\Inetpub\Customer s\viuxhosting\imac\imac-nyc.co
m\www\admin\news2.cfm:11)
if i use the update form 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
D:\Inetpub\Customers\viuxhosting\imac\imac-nyc.com\www\admin\news2.cfm: line 19
17 : UPDATE news
18 : SET dmy = '#form.dmy#', text = '#form.text#'
19 : WHERE id = #form.id#
20 : </cfquery>
21 : <cfoutput>News Modified Sucessfully. <a href="home.cfm">Click here to
return to IMAC Administrator Home</a></cfoutput>
SQL UPDATE news SET dmy = '4/11/2005', text = ' Good News! For all those
early risers out there, morning beginner classes have been added. The beginner
schedule is as follows:' WHERE id = 3
DATASOURCE imac
VENDORERRORCODE -3503
SQLSTATE 42000
Please try the following:
* Check the ColdFusion documentation to verify that you are using the
correct syntax.
* Search the Knowledge Base to find a solution to your problem.
Browser Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.7.10)
Gecko/20050716 Firefox/1.0.6
Remote Address 24.161.80.163
Referrer [url]http://imac-nyc.com/admin/news.cfm?ACTION=Modify&id=3[/url]
Date/Time 11-Sep-05 01:06 PM
Stack Trace
at
cfnews22ecfm1438369281.runPage(D:\Inetpub\Customer s\viuxhosting\imac\imac-nyc.co
m\www\admin\news2.cfm:19) at
cfnews22ecfm1438369281.runPage(D:\Inetpub\Customer s\viuxhosting\imac\imac-nyc.co
m\www\admin\news2.cfm:19)
-Snowball2050
Snowball2050 Guest
-
Error in Insert into syntax
I donīt understand why this error is being flagged when trying to insert a new entry from a db. (Why is the flash form so tempremental? I try... -
Syntax error in INSERT INTO statement
I have a two table setup in fact I am doing the blog tutorial on Macromedia but with Access and asp I have two tables topic and articles I have a... -
Syntax error in update
I'm using the update record form wizard in Dreamweaver, and am getting this error when I try to submit changes. <cfparam name='FORM.buildnumber'... -
Getting a SQL Update syntax error...really shouldn't be?
Try putting round the password column in the UPDATE statement. I think that SQL might be seeing that as a reserved word. Hope it helps... ... -
syntax error on update
ugh, thanx for the help guess there's more than one way this syntax works as well rs("firstname")=blahblah i'm just learning sql so i tend to... -
Mountain Lover #2 Re: Syntax error in INSERT INTO and UPDATE statements
text is a reserved word. rename the field or escape it [text]
HTH
--
Tim Carley
[url]www.recfusion.com[/url]
[email]info@NOSPAMINGrecfusion.com[/email]
Mountain Lover Guest
-
mxstu #3 Re: Syntax error in INSERT INTO and UPDATE statements
"Text" is the name of an Access datatype, so the error is probably caused by
the fact that you are using a reserved word as a column name. Try changing the
the column name to something else like "NewsText".
If "dmy" is a datetime column, consider using the createODBCDate() function or
cfqueryparam to ensure that the date value is passed to the database correctly.
You should also consider using cfquerparam for your where clause to prevent sql
injection.
... WHERE id = #form.id# ...
mxstu Guest
-
Snowball2050 #4 Re: Syntax error in INSERT INTO and UPDATE statements
many thanks tim and mxstu. forgot about the reserved words. I'll give that a try
Snowball2050 Guest
Reply With QuoteSimilar Questions and Discussions
Posting Permissions
- You may not post new threads
- You may post replies
- You may not post attachments
- You may not edit your posts
