[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression

[dcarson]

I've read about this error in several other discussions, but still
can't seem to pinpoint the problem with my code. Everything seemed to
be working fine for some time, but it now tends to bomb out on me for
some reason. The Summary field is pulled from a Rich Text Editor that
allows HTML formatting and appears to be the culprit, but I just can't
seem to figure out why. Any guidance is greatly appreciated.

I'm connecting to an Access database with an ASP page. The insert
statement is as follows:

Private Function fInsertRecord()
sSql = "INSERT INTO Events (Description, TheTime, Location, Event,
Download, DateOfEvent, Summary) VALUES ("
sSql = sSql & il("sDescription") & ", "
sSql = sSql & il("sTheTime") & ", "
sSql = sSql & il("sLocation") & ", "
sSql = sSql & il("sEvent") & ", "
if Request.form("sDownload") = "" then
StoreValue = "No"
else
StoreValue = Request.form("sDownload")
end if
sSql = sSql & StoreValue & ", "
sSql = sSql & "#" & Request.form("sDateOfEvent") & "#" & ", "

if Request.form("sSummary") = "" then
StoreValue1 = "-----"
else
StoreValue1 = Request.form("sSummary")
end if
InsertAp(StoreValue1)
sSql = sSql & "'" & StoreValue1 & "')"
response.write(sSql)
conn.execute(sSql)
end function

I have added the response.write to view the completed insert statement.
It looks like this:

INSERT INTO Events (Description, TheTime, Location, Event, Download,
DateOfEvent, Summary) VALUES ('Testing a Topic', '8:00 PM', 'Hosted at
a fictitious location', 'An Event Title Would Go Here', 'No',
#01/01/2010#, '
To comply with U.S. Treasury Regulations, we are required to inform you
that any tax advice contained in this message or in any attachment is
not intended to be relied upon, and cannot be relied upon, to avoid
penalties under the Internal Revenue Code.



')



And the error that is returned is:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing
operator) in query expression ''<P><STRONG> </STRONG></P> <P
class=MsoNormal style="MARGIN: 0in 0in 0pt"><SPAN style="FONT-SIZE:
10pt; FONT-FAMILY: Arial"><FONT color=#000000>To comply with U.S.
Treasury Regulations, we are required to inform you that any tax advice
contained in this mes'.

/EventEdit.asp, line 405

Line 405 is the conn.execute(sSql) from above.

 

[Jeff Cochran]

Double quotes, caused by your HTML code. Either don't insert HTML
code into the database (smart idea, separates presentation from data)
or HTMLEncode it.

Jeff

 

[Darryl]

Thanks Jeff. That's just what I needed.

 

[Egbert Nierop \(MVP for IIS\)]

I've read about this error in several other discussions, but still
can't seem to pinpoint the problem with my code. Everything seemed to
be working fine for some time, but it now tends to bomb out on me for
some reason. The Summary field is pulled from a Rich Text Editor that
allows HTML formatting and appears to be the culprit, but I just can't
seem to figure out why. Any guidance is greatly appreciated.

I'm connecting to an Access database with an ASP page. The insert
statement is as follows:

The safest way is to not encode each parameter yourselves, but to use the
ADODB.Command instead of the connection directly.

Dim cmd , description
set cmd =createobject("ADODB.Command")
const advarchar = 200, addbtimestamp = 135

cmd.commandtext = "INSERT INTO Events (Description, TheTime, Location,
Event, Download,
DateOfEvent, Summary) VALUES (?, ?, ?, ?, ?, ?, ? )"
set cmd.activeconnection = conn
cmd.commandtype = 1 'adcmdtext
cmd.parameters.append cmd.createparameter(, advarchar,,100,
il("sDescription"))
CreateParameter.append ... repeat this for each parameter

cmd.execute ,, 128 'adcmdexecnorecords

Private Function fInsertRecord()
sSql = "INSERT INTO Events (Description, TheTime, Location, Event,
Download, DateOfEvent, Summary) VALUES ("
sSql = sSql & il("sDescription") & ", "
sSql = sSql & il("sTheTime") & ", "
sSql = sSql & il("sLocation") & ", "
sSql = sSql & il("sEvent") & ", "
if Request.form("sDownload") = "" then
StoreValue = "No"
else
StoreValue = Request.form("sDownload")
end if
sSql = sSql & StoreValue & ", "
sSql = sSql & "#" & Request.form("sDateOfEvent") & "#" & ", "

if Request.form("sSummary") = "" then
StoreValue1 = "-----"
else
StoreValue1 = Request.form("sSummary")
end if
InsertAp(StoreValue1)
sSql = sSql & "'" & StoreValue1 & "')"
response.write(sSql)
conn.execute(sSql)
end function

I have added the response.write to view the completed insert statement.
It looks like this:

INSERT INTO Events (Description, TheTime, Location, Event, Download,
DateOfEvent, Summary) VALUES ('Testing a Topic', '8:00 PM', 'Hosted at
a fictitious location', 'An Event Title Would Go Here', 'No',
#01/01/2010#, '
To comply with U.S. Treasury Regulations, we are required to inform you
that any tax advice contained in this message or in any attachment is
not intended to be relied upon, and cannot be relied upon, to avoid
penalties under the Internal Revenue Code.



')



And the error that is returned is:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing
operator) in query expression ''<P><STRONG> </STRONG></P> <P
class=MsoNormal style="MARGIN: 0in 0in 0pt"><SPAN style="FONT-SIZE:
10pt; FONT-FAMILY: Arial"><FONT color=#000000>To comply with U.S.
Treasury Regulations, we are required to inform you that any tax advice
contained in this mes'.

/EventEdit.asp, line 405

Line 405 is the conn.execute(sSql) from above.

 

[Bob Barrows]

The safest way is to not encode each parameter yourselves, but to use the
ADODB.Command instead of the connection directly.

Dim cmd , description
set cmd =createobject("ADODB.Command")
const advarchar = 200, addbtimestamp = 135

cmd.commandtext = "INSERT INTO Events (Description, TheTime, Location,
Event, Download,
DateOfEvent, Summary) VALUES (?, ?, ?, ?, ?, ?, ? )"
set cmd.activeconnection = conn
cmd.commandtype = 1 'adcmdtext
cmd.parameters.append cmd.createparameter(, advarchar,,100,
il("sDescription"))

Or use a variant array to pass the parameters:
dim arParms
arParms=array(il("sDescription"),...)
cmd.execute ,arParms,129

When using this technique, you should be explicit about your datatypes: use
cdate(il("sTheTime")) instead of just il("sTheTime") (assuming TheTime is a
date/time field)

Bob Barrows