Syntax error in INSERT INTO statement

[Speegs]

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 form for
articles, to submit new articles to the articles table; which pulls the topics
in a dynamic drop down from the topics table. Obviously the values for the
topics is the topic id, and the tites are the titles. Everytime I try to
submit a new article I get this: Error Type: Microsoft JET Database Engine
(0x80040E14) . /blogger/administration/insert_article.asp, line 115 Browser
Type: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; .NET CLR 1.1.4322;
..NET CLR 2.0.40607) Page: POST 97 bytes to
/blogger/administration/insert_article.asp POST Data:
articletitle=Microsoft+%22The+True+Provider%22&amp ;select=1&articleshortdesc
rp=&Text=&MM_insert=form1 Time: Sunday, April 10, 2005, 7:52:51 AM

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="../Connections/conndb.asp" -->
<%
' *** Edit Operations: declare variables

Dim MM_editAction
Dim MM_abortEdit
Dim MM_editQuery
Dim MM_editCmd

Dim MM_editConnection
Dim MM_editTable
Dim MM_editRedirectUrl
Dim MM_editColumn
Dim MM_recordId

Dim MM_fieldsStr
Dim MM_columnsStr
Dim MM_fields
Dim MM_columns
Dim MM_typeArray
Dim MM_formVal
Dim MM_delim
Dim MM_altVal
Dim MM_emptyVal
Dim MM_i

MM_editAction = CStr(Request.ServerVariables("SCRIPT_NAME"))
If (Request.QueryString <> "") Then
MM_editAction = MM_editAction & "?" & Server.HTMLEncode(Request.QueryString)
End If

' boolean to abort record edit
MM_abortEdit = false

' query string to execute
MM_editQuery = ""
%>
<%
' *** Insert Record: set variables

If (CStr(Request("MM_insert")) = "form1") Then

MM_editConnection = MM_conndb_STRING
MM_editTable = "Articles"
MM_editRedirectUrl = "confirm_insert.asp"
MM_fieldsStr = "articletitle|value|articleshortdescrp|value|Text| value"
MM_columnsStr =
"articletitle|',none,''|articleshortdescrp|',none, ''|Text|',none,''"

' create the MM_fields and MM_columns arrays
MM_fields = Split(MM_fieldsStr, "|")
MM_columns = Split(MM_columnsStr, "|")

' set the form values
For MM_i = LBound(MM_fields) To UBound(MM_fields) Step 2
MM_fields(MM_i+1) = CStr(Request.Form(MM_fields(MM_i)))
Next

' append the query string to the redirect URL
If (MM_editRedirectUrl <> "" And Request.QueryString <> "") Then
If (InStr(1, MM_editRedirectUrl, "?", vbTextCompare) = 0 And
Request.QueryString <> "") Then
MM_editRedirectUrl = MM_editRedirectUrl & "?" & Request.QueryString
Else
MM_editRedirectUrl = MM_editRedirectUrl & "&" & Request.QueryString
End If
End If

End If
%>
<%
' *** Insert Record: construct a sql insert statement and execute it

Dim MM_tableValues
Dim MM_dbValues

If (CStr(Request("MM_insert")) <> "") Then

' create the sql insert statement
MM_tableValues = ""
MM_dbValues = ""
For MM_i = LBound(MM_fields) To UBound(MM_fields) Step 2
MM_formVal = MM_fields(MM_i+1)
MM_typeArray = Split(MM_columns(MM_i+1),",")
MM_delim = MM_typeArray(0)
If (MM_delim = "none") Then MM_delim = ""
MM_altVal = MM_typeArray(1)
If (MM_altVal = "none") Then MM_altVal = ""
MM_emptyVal = MM_typeArray(2)
If (MM_emptyVal = "none") Then MM_emptyVal = ""
If (MM_formVal = "") Then
MM_formVal = MM_emptyVal
Else
If (MM_altVal <> "") Then
MM_formVal = MM_altVal
ElseIf (MM_delim = "'") Then ' escape quotes
MM_formVal = "'" & Replace(MM_formVal,"'","''") & "'"
Else
MM_formVal = MM_delim + MM_formVal + MM_delim
End If
End If
If (MM_i <> LBound(MM_fields)) Then
MM_tableValues = MM_tableValues & ","
MM_dbValues = MM_dbValues & ","
End If
MM_tableValues = MM_tableValues & MM_columns(MM_i)
MM_dbValues = MM_dbValues & MM_formVal
Next
MM_editQuery = "insert into " & MM_editTable & " (" & MM_tableValues & ")
values (" & MM_dbValues & ")"

If (Not MM_abortEdit) Then
' execute the insert
Set MM_editCmd = Server.CreateObject("ADODB.Command")
MM_editCmd.ActiveConnection = MM_editConnection
MM_editCmd.CommandText = MM_editQuery
MM_editCmd.Execute
MM_editCmd.ActiveConnection.Close

If (MM_editRedirectUrl <> "") Then
Response.Redirect(MM_editRedirectUrl)
End If
End If

End If
%>
<%
Dim rsmenu
Dim rsmenu_numRows

Set rsmenu = Server.CreateObject("ADODB.Recordset")
rsmenu.ActiveConnection = MM_conndb_STRING
rsmenu.Source = "SELECT * FROM Topics"
rsmenu.CursorType = 0
rsmenu.CursorLocation = 2
rsmenu.LockType = 1
rsmenu.Open()

rsmenu_numRows = 0
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Blog</title>
</head>

<body>
<form method="post" action="<%=MM_editAction%>" name="form1">
<table align="center">
<tr valign="baseline">
<td nowrap align="right">Articletitle:</td>
<td><input type="text" name="articletitle" value="" size="32">
</td>
</tr>
<tr valign="baseline">
<td nowrap align="right">Topic</td>
<td><select name="select">
<%
While (NOT rsmenu.EOF)
%>
<option
value="<%=(rsmenu.Fields.Item("topicid").Value)%>" ><%=(rsmenu.Fields.Item("topic
title").Value)%></option>
<%
rsmenu.MoveNext()
Wend
If (rsmenu.CursorType > 0) Then
rsmenu.MoveFirst
Else
rsmenu.Requery
End If
%>
</select></td>
</tr>
<tr>
<td nowrap align="right" valign="top">Articleshortdescrp:</td>
<td valign="baseline"><textarea name="articleshortdescrp" cols="50"
rows="5"></textarea>
</td>
</tr>
<tr>
<td nowrap align="right" valign="top">Text:</td>
<td valign="baseline"><textarea name="Text" cols="50"
rows="5"></textarea>
</td>
</tr>
<tr valign="baseline">
<td nowrap align="right">&nbsp;</td>
<td><input type="submit" value="Insert record">
</td>
</tr>
</table>
<input type="hidden" name="MM_insert" value="form1">
</form>
<p>&nbsp;</p>
</body>
</html>
<%
rsmenu.Close()
Set rsmenu = Nothing
%>

[Nancy Gill]

That error is almost always using a reserved word in your query. In your
case, the word is TEXT .. which is reserved. Change that to MyText ..
ArticleText .. whatever .. and you should be fine.


--
Nancy Gill
Team Macromedia Volunteer for Dreamweaver MX/UltraDev
[url]http://www.macromedia.com/go/team/[/url]
Co-Author: Dreamweaver MX: Instant Troubleshooter (August, 2003)
Technical Editor: DMX 2004: The Complete Reference, DMX 2004: A Beginner's
Guide, Mastering Macromedia Contribute
Technical Reviewer: Dynamic Dreamweaver MX/DMX: Advanced PHP Web Development

"Speegs" <webforumsuser@macromedia.com> wrote in message
news:d3b466$j2i$1@forums.macromedia.com...

> 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 form

for

> articles, to submit new articles to the articles table; which pulls the

topics

> in a dynamic drop down from the topics table. Obviously the values for the
> topics is the topic id, and the tites are the titles. Everytime I try to
> submit a new article I get this: Error Type: Microsoft JET Database

Engine

> (0x80040E14) . /blogger/administration/insert_article.asp, line 115

Browser

> Type: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; .NET CLR

1.1.4322;

> .NET CLR 2.0.40607) Page: POST 97 bytes to
> /blogger/administration/insert_article.asp POST Data:
>

articletitle=Microsoft+%22The+True+Provider%22&amp ;select=1&amp;articleshort
desc

> rp=&amp;Text=&amp;MM_insert=form1 Time: Sunday, April 10, 2005, 7:52:51

AM

>
> <%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
> <!--#include file="../Connections/conndb.asp" -->
> <%
> ' *** Edit Operations: declare variables
>
> Dim MM_editAction
> Dim MM_abortEdit
> Dim MM_editQuery
> Dim MM_editCmd
>
> Dim MM_editConnection
> Dim MM_editTable
> Dim MM_editRedirectUrl
> Dim MM_editColumn
> Dim MM_recordId
>
> Dim MM_fieldsStr
> Dim MM_columnsStr
> Dim MM_fields
> Dim MM_columns
> Dim MM_typeArray
> Dim MM_formVal
> Dim MM_delim
> Dim MM_altVal
> Dim MM_emptyVal
> Dim MM_i
>
> MM_editAction = CStr(Request.ServerVariables("SCRIPT_NAME"))
> If (Request.QueryString <> "") Then
> MM_editAction = MM_editAction & "?" &

Server.HTMLEncode(Request.QueryString)

> End If
>
> ' boolean to abort record edit
> MM_abortEdit = false
>
> ' query string to execute
> MM_editQuery = ""
> %>
> <%
> ' *** Insert Record: set variables
>
> If (CStr(Request("MM_insert")) = "form1") Then
>
> MM_editConnection = MM_conndb_STRING
> MM_editTable = "Articles"
> MM_editRedirectUrl = "confirm_insert.asp"
> MM_fieldsStr =

"articletitle|value|articleshortdescrp|value|Text| value"

> MM_columnsStr =
> "articletitle|',none,''|articleshortdescrp|',none, ''|Text|',none,''"
>
> ' create the MM_fields and MM_columns arrays
> MM_fields = Split(MM_fieldsStr, "|")
> MM_columns = Split(MM_columnsStr, "|")
>
> ' set the form values
> For MM_i = LBound(MM_fields) To UBound(MM_fields) Step 2
> MM_fields(MM_i+1) = CStr(Request.Form(MM_fields(MM_i)))
> Next
>
> ' append the query string to the redirect URL
> If (MM_editRedirectUrl <> "" And Request.QueryString <> "") Then
> If (InStr(1, MM_editRedirectUrl, "?", vbTextCompare) = 0 And
> Request.QueryString <> "") Then
> MM_editRedirectUrl = MM_editRedirectUrl & "?" & Request.QueryString
> Else
> MM_editRedirectUrl = MM_editRedirectUrl & "&" & Request.QueryString
> End If
> End If
>
> End If
> %>
> <%
> ' *** Insert Record: construct a sql insert statement and execute it
>
> Dim MM_tableValues
> Dim MM_dbValues
>
> If (CStr(Request("MM_insert")) <> "") Then
>
> ' create the sql insert statement
> MM_tableValues = ""
> MM_dbValues = ""
> For MM_i = LBound(MM_fields) To UBound(MM_fields) Step 2
> MM_formVal = MM_fields(MM_i+1)
> MM_typeArray = Split(MM_columns(MM_i+1),",")
> MM_delim = MM_typeArray(0)
> If (MM_delim = "none") Then MM_delim = ""
> MM_altVal = MM_typeArray(1)
> If (MM_altVal = "none") Then MM_altVal = ""
> MM_emptyVal = MM_typeArray(2)
> If (MM_emptyVal = "none") Then MM_emptyVal = ""
> If (MM_formVal = "") Then
> MM_formVal = MM_emptyVal
> Else
> If (MM_altVal <> "") Then
> MM_formVal = MM_altVal
> ElseIf (MM_delim = "'") Then ' escape quotes
> MM_formVal = "'" & Replace(MM_formVal,"'","''") & "'"
> Else
> MM_formVal = MM_delim + MM_formVal + MM_delim
> End If
> End If
> If (MM_i <> LBound(MM_fields)) Then
> MM_tableValues = MM_tableValues & ","
> MM_dbValues = MM_dbValues & ","
> End If
> MM_tableValues = MM_tableValues & MM_columns(MM_i)
> MM_dbValues = MM_dbValues & MM_formVal
> Next
> MM_editQuery = "insert into " & MM_editTable & " (" & MM_tableValues &

")

> values (" & MM_dbValues & ")"
>
> If (Not MM_abortEdit) Then
> ' execute the insert
> Set MM_editCmd = Server.CreateObject("ADODB.Command")
> MM_editCmd.ActiveConnection = MM_editConnection
> MM_editCmd.CommandText = MM_editQuery
> MM_editCmd.Execute
> MM_editCmd.ActiveConnection.Close
>
> If (MM_editRedirectUrl <> "") Then
> Response.Redirect(MM_editRedirectUrl)
> End If
> End If
>
> End If
> %>
> <%
> Dim rsmenu
> Dim rsmenu_numRows
>
> Set rsmenu = Server.CreateObject("ADODB.Recordset")
> rsmenu.ActiveConnection = MM_conndb_STRING
> rsmenu.Source = "SELECT * FROM Topics"
> rsmenu.CursorType = 0
> rsmenu.CursorLocation = 2
> rsmenu.LockType = 1
> rsmenu.Open()
>
> rsmenu_numRows = 0
> %>
> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
> "http://www.w3.org/TR/html4/loose.dtd">
> <html>
> <head>
> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
> <title>Blog</title>
> </head>
>
> <body>
> <form method="post" action="<%=MM_editAction%>" name="form1">
> <table align="center">
> <tr valign="baseline">
> <td nowrap align="right">Articletitle:</td>
> <td><input type="text" name="articletitle" value="" size="32">
> </td>
> </tr>
> <tr valign="baseline">
> <td nowrap align="right">Topic</td>
> <td><select name="select">
> <%
> While (NOT rsmenu.EOF)
> %>
> <option
>

value="<%=(rsmenu.Fields.Item("topicid").Value)%>" ><%=(rsmenu.Fields.Item("t
opic

> title").Value)%></option>
> <%
> rsmenu.MoveNext()
> Wend
> If (rsmenu.CursorType > 0) Then
> rsmenu.MoveFirst
> Else
> rsmenu.Requery
> End If
> %>
> </select></td>
> </tr>
> <tr>
> <td nowrap align="right" valign="top">Articleshortdescrp:</td>
> <td valign="baseline"><textarea name="articleshortdescrp" cols="50"
> rows="5"></textarea>
> </td>
> </tr>
> <tr>
> <td nowrap align="right" valign="top">Text:</td>
> <td valign="baseline"><textarea name="Text" cols="50"
> rows="5"></textarea>
> </td>
> </tr>
> <tr valign="baseline">
> <td nowrap align="right">&nbsp;</td>
> <td><input type="submit" value="Insert record">
> </td>
> </tr>
> </table>
> <input type="hidden" name="MM_insert" value="form1">
> </form>
> <p>&nbsp;</p>
> </body>
> </html>
> <%
> rsmenu.Close()
> Set rsmenu = Nothing
> %>
>

[Marcus_in_Leeds]

I'm relatively new to CF and dbs, and am getting a "Syntax error in INSERT INTO
statement" error when I try to run the following code.

I want to insert a record into a db as an audit trail when someone amends the
main db. Only the time/date, IP address and the values of the fields amended
should be inserted into the audittrail db.

Prior to updating the main db, a CFQUERY called memberlogin is run to pull the
details from the main db and this code compares that data to the user-amended
data (from a form on the previous page).

<cfquery name="audittrail" datasource="audittrail">
INSERT INTO audittrail
SET MEMBERSHIPNUMBER = '#Session.membernumber#'
<cfif #form.newcompany# NEQ #memberlogin.COMPANY#>, COMPANY =
'#form.newcompany#'</cfif>
<cfif #form.newjobtitle# NEQ #memberlogin.JOBTITLE#>, JOBTITLE =
'#form.newjobtitle#'</cfif>
<cfif #form.newaddress1# NEQ #memberlogin.ADDRESS1#>, ADDRESS1 =
'#form.newaddress1#'</cfif>
<cfif #form.newaddress2# NEQ #memberlogin.ADDRESS2#>, ADDRESS2 =
'#form.newaddress2#'</cfif>
<cfif #form.newaddress3# NEQ #memberlogin.ADDRESS3#>, ADDRESS3 =
'#form.newaddress3#'</cfif>
<cfif #form.newaddress4# NEQ #memberlogin.ADDRESS4#>, ADDRESS4 =
'#form.newaddress4#'</cfif>
<cfif #form.newaddress5# NEQ #memberlogin.ADDRESS5#>, ADDRESS5 =
'#form.newaddress5#'</cfif>
<cfif #form.newaddress6# NEQ #memberlogin.ADDRESS6#>, ADDRESS5 =
'#form.newaddress6#'</cfif>
<cfif #form.newpostcode# NEQ #memberlogin.POSTCODE#>, POSTCODE =
'#form.newpostcode#'</cfif>
<cfif #form.newnetadd# NEQ #memberlogin.NETADD#>, NETADD =
'#form.newnetadd#'</cfif>
<cfif #form.newflag1# NEQ #memberlogin.FLAG_APP1#>, FLAG_APP1 =
'#form.newflag1#'</cfif>
<cfif #form.newflag2# NEQ #memberlogin.FLAG_RES2#>, FLAG_RES2 =
'#form.newflag2#'</cfif>
<cfif #form.newflag6# NEQ #memberlogin.FLAG_CAM6#>, FLAG_CAM6 =
'#form.newflag6#'</cfif>
<cfif #form.newflag7# NEQ #memberlogin.FLAG_SRAF7#>, FLAG_SRAF7 =
'#form.newflag7#'</cfif>
<cfif #form.newflag8# NEQ #memberlogin.FLAG_XRAF8#>, FLAG_XRAF8 =
'#form.newflag8#'</cfif>
<cfif isdefined("form.newflagni")>
<cfif #form.newflagni# NEQ #memberlogin.FLAG_NIRAF#>, FLAG_NIRAF =
'#form.newflagni#'</cfif>
</cfif>
<cfif #form.newflag10# NEQ #memberlogin.FLAG_CAT10#>, FLAG_CAT10 =
'#form.newflag10#'</cfif>,
IPADD = '#CGI.REMOTE_ADDR#', CUANDO = '#now()#'</cfquery>

However, when I run it, I get the following error:

"ODBC Error Code = 37000 (Syntax error or access violation)
"[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO
statement.
"The error occurred while processing an element with a general identifier of
(CFQUERY), occupying document position (87:1) to (87:69)."

Any advice on what I'm doing wrong?

Many thanks.

[jdeline]

The syntax of your INSERT statement is incorrect. See below for example.
Also, you really don't need all those CFIF tags. Just set the fields to the
form variables in all cases.

To insert (create a new record):
INSERT INTO auditTrail (a, b, c) VALUES (1, 2, 3)

To update (edit existing record):
UPDATE autitTrail SET a = 1, b = 2, c = 3 WHERE ...

[Dan Bracuk]

I think you should change your table to have fewer fields and more records.
Something like
table audittrail
--------------
datetimechanged
fieldchanged
oldvalue
newvalue
ipaddress

It will be a bit trickier to get data in, but a lot easier to get information
out.

[jorgepino]

so the database is "audittrail"
and the name of the query is "audittrail"
and also the name of the table is "audittrail"

you may want to have some variations on you naming convention

[Jonnyshaw]

hey can anyone help me i keep gettin the message
"Syntax error in INSERT INTO statement"
when i try to insert users to my database using this code

<!--- insert new customer record --->
<CFSET NewID = CreateUUID()>
<cfquery name="SaveCustomer" datasource="#Application.datasource#">
INSERT INTO tblStoreCustomers
(CustomerID,
Firstname,
Lastname,
CompanyName,
Address1,
<CFIF IsDefined("Form.Address2")>
Address2,
</cfif>
City,
PostCode,
Phone,
Email,
Username,
Password)

VALUES
('#NewID#',
'#Form.FirstName#',
'#Form.LastName#',
'#Form.CompanyName#',
'#Form.Address1#',
<CFIF IsDefined("Form.Address2")>
'#Form.Address2#',
</cfif>
'#Form.City#',
'#Form.PostCode#',
'#Form.Phone#',
'#Form.Email#',
'#Form.Username#',
<cfif #Form.Password# EQ #Form.PswdConfirm#>
'#Form.Password#'
)
<cfelse>
<cflocation url="customerentry.cfm?password=1">
</cfif>
</cfquery>

[Dan Bracuk]

1. If CustomerID is numeric, the quotes around the value should go.

2. You are missing a closing cfif tag at the end of your values.

3. You can't have a cflocation tag inside a cfquery tag

4. If form.password does not equal form.pswdconfirm you will be attempting to
insert 11 values into 12 fields.