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

Discussion in 'ASP General' started by dcarson@displayspecialties.com, Sep 28, 2005.

  1. Guest

    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, 2005
    #1
    1. Advertising

  2. Jeff Cochran Guest

    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, 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.
     
    Jeff Cochran, Sep 28, 2005
    #2
    1. Advertising

  3. Darryl Guest

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

    Thanks Jeff. That's just what I needed.
     
    Darryl, Sep 28, 2005
    #3
  4. <> wrote in message
    news:...
    > 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.
    >
     
    Egbert Nierop \(MVP for IIS\), Sep 30, 2005
    #4
  5. Bob Barrows Guest

    "Egbert Nierop (MVP for IIS)" <> wrote in
    message news:...
    >
    > <> wrote in message
    > news:...
    > > 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
     
    Bob Barrows, Sep 30, 2005
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.

Share This Page