Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2002
    Location
    Washington D.C.
    Posts
    164

    Unanswered: '80040e14' , I know the error can't fix it

    Hi,
    I have a ASP form which is inserted into an Access 2000 DB.
    I was testing it and kept on getting this error:

    =========================

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

    [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression '''','','','','','','','','3','','','','');'.

    /QA_Submit.asp, line 145


    ======================
    I finally figured what the problem is:
    If I use the ' simple any where on the fields that are inserted, it will cause the error, cause the sql statment gets messed up. Here is my SQL Statment:

    strSQL="INSERT INTO Survey (QAID,Suite,Fname,Lname,Title,Phone,Fax,Email,Comm ent,SatRate,Suggest,DoContact,DoNotify,HowNotify) VALUES

    ('"&QAID&"','"&Suite&"','"&FName&"','"&LName&"','" &Title&"','"&Phone&"','"&Fax&"','"&Email&"','"&Com ment&"','"&SatRate&"','"&Suggest&"','"&DoContact&" ','"&DoNotify&"','"&HowNotify&"');"


    ===============

    Since I'm using VBscript I need to seperate the values by ', there fore if the ' is typed into any text box it will cause the error.
    Do you know how I should fix this?

  2. #2
    Join Date
    Jan 2003
    Location
    de/ro
    Posts
    12

    Re: '80040e14' , I know the error can't fix it

    replace the quotes or double quotes before constructing the sql query

    u can use the folowing functions:

    FUNCTION escapeQuotes(str)
    DIM str_
    IF str="" OR IsEmpty(str) OR IsNull(str) THEN
    str_ = ""
    ELSE
    str_ = Replace(str, chr(34), """)
    str_ = Replace(str_, chr(39), "'")
    str_ = Replace(str_, vbCr, "<br>")
    'str = Replace(str, " ", "&nbsp;")
    END IF
    escapeQuotes = str_
    END FUNCTION

    '************************************************* **********
    FUNCTION unEscapeQuotes(str)
    DIM str_
    IF IsEmpty(str) OR IsNull(str) THEN
    str_ = ""
    ELSE
    str_ = Replace(str, "<br>", vbCr)
    str_ = Replace(str_, "&#39;", chr(39))
    str_ = Replace(str_, "&quot;", chr(34))
    END IF
    unEscapeQuotes = str_
    END FUNCTION

    use escapeQuotes before constructing the sql query on the values that must be part of the query and when you retreive the content from the db you can apply the unEscapeQuotes

    strSQL="INSERT INTO Survey (QAID,Suite,Fname,Lname,Title,Phone,Fax,Email,Comm
    ent,SatRate,Suggest,DoContact,DoNotify,HowNotify) VALUES

    ('"& escapeQuotes(QAID) &"','"& escapeQuotes(Suite) &"','"& escapeQuotes(FName) &"','"& escapeQuotes(LName) &"','"& escapeQuotes(Title) &"','"&escapeQuotes(Phone)&"','"& escapeQuotes(Fax) &"','"& escapeQuotes(Email) &"','"& escapeQuotes(Comment) &"','"& escapeQuotes(SatRate) &"','"& escapeQuotes(Suggest)&"','"& escapeQuotes(DoContact) &"','"& escapeQuotes(DoNotify) &"','"& escapeQuotes(HowNotify) &"');"


    have fun

  3. #3
    Join Date
    Dec 2002
    Location
    Washington D.C.
    Posts
    164
    Thanks

  4. #4
    Join Date
    Feb 2002
    Location
    North Wales, UK
    Posts
    114
    You can also use the "addnew" method of the Recordset object. This does not require you to remove " ' " from data you are inserting into your database.
    J^ - web | email
    newsASP Developer

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •