By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,360 Members | 2,961 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,360 IT Pros & Developers. It's quick & easy.

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

P: n/a
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.

Sep 28 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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
On 28 Sep 2005 06:04:52 -0700, dc*****@displayspecialties.com wrote:
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.


Sep 28 '05 #2

P: n/a
Thanks Jeff. That's just what I needed.

Sep 28 '05 #3

P: n/a

<dc*****@displayspecialties.com> wrote in message
news:11*********************@g14g2000cwa.googlegro ups.com...
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.


Sep 30 '05 #4

P: n/a

"Egbert Nierop (MVP for IIS)" <eg***********@nospam.invalid> wrote in
message news:uP**************@TK2MSFTNGP12.phx.gbl...

<dc*****@displayspecialties.com> wrote in message
news:11*********************@g14g2000cwa.googlegro ups.com...
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"))

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
Sep 30 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Browse more ASP / Active Server Pages Questions on Bytes