form field chars

Discussion in 'ASP General' started by Paul Malbon, Jan 19, 2006.

  1. Paul Malbon

    Paul Malbon Guest

    Hi,

    I have a form where the user enters a customer name, then clicks the submit
    button which then adds it to a database. This works absolutely fine untill
    the name has an apostrophe in it.

    eg when the name entered is O'Hanlon and its submitted, I get this
    error.....
    Microsoft JET Database Engine (0x80040E14)
    Syntax error (missing operator) in query expression ''O'Hanlon'

    any help would be great, and yes I'm a newbie!!
    thanks in advance

    Paul
     
    Paul Malbon, Jan 19, 2006
    #1
    1. Advertising

  2. Paul Malbon

    Tim Slattery Guest

    "Paul Malbon" <> wrote:

    >Hi,
    >
    >I have a form where the user enters a customer name, then clicks the submit
    >button which then adds it to a database. This works absolutely fine untill
    >the name has an apostrophe in it.
    >
    >eg when the name entered is O'Hanlon and its submitted, I get this
    >error.....
    >Microsoft JET Database Engine (0x80040E14)
    >Syntax error (missing operator) in query expression ''O'Hanlon'


    Most likely the apostrophe appears as an extra delimiter in the SQL
    statement that's used to update the DB. The solution is to double the
    apostrophe before presenting the field to the database.

    --
    Tim Slattery
    MS MVP(DTS)
     
    Tim Slattery, Jan 19, 2006
    #2
    1. Advertising

  3. Paul Malbon

    Paul Malbon Guest

    Thanks for the reply tim. I should have been a bit more clearer in my post.
    I realise that the error is caused by the apostrophe.

    You say double the apostrophe. Do you mind me asking how I would do that?
    This web based form will be used by people who generally dont use the
    internet and to ask them to 'double apostrophe' would confuse them no end!!

    Thanks again for your assitance


    "Tim Slattery" <> wrote in message
    news:...
    > "Paul Malbon" <> wrote:
    >
    >>Hi,
    >>
    >>I have a form where the user enters a customer name, then clicks the
    >>submit
    >>button which then adds it to a database. This works absolutely fine untill
    >>the name has an apostrophe in it.
    >>
    >>eg when the name entered is O'Hanlon and its submitted, I get this
    >>error.....
    >>Microsoft JET Database Engine (0x80040E14)
    >>Syntax error (missing operator) in query expression ''O'Hanlon'

    >
    > Most likely the apostrophe appears as an extra delimiter in the SQL
    > statement that's used to update the DB. The solution is to double the
    > apostrophe before presenting the field to the database.
    >
    > --
    > Tim Slattery
    > MS MVP(DTS)
    >
     
    Paul Malbon, Jan 19, 2006
    #3
  4. Paul Malbon wrote:
    > Hi,
    >
    > I have a form where the user enters a customer name, then clicks the
    > submit button which then adds it to a database. This works absolutely
    > fine untill the name has an apostrophe in it.
    >
    > eg when the name entered is O'Hanlon and its submitted, I get this
    > error.....
    > Microsoft JET Database Engine (0x80040E14)
    > Syntax error (missing operator) in query expression ''O'Hanlon'
    >
    > any help would be great, and yes I'm a newbie!!
    > thanks in advance
    >

    Another delimiter problem caused by the use of dynamic sql instead of
    parameters. Tim explained about escaping your apostrophe by doubling it, but
    there are other potential problems caused by the use of dynamic sql. Read:
    http://mvp.unixwiz.net/techtips/sql-injection.html
    http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=

    http://groups.google.com/groups?hl=...=1&selm=

    Using Command object to parameterize CommandText:
    http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e

    HTH,
    Bob Barrows

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows [MVP], Jan 19, 2006
    #4
  5. No no no. He's telling you to do it in your code that builds the sql
    statement that inserts the value into your database. See
    http://groups.google.com/group/micr.../713f592513bf333c?hl=en&lr=&ie=UTF-8&oe=UTF-8

    dim s, sql
    s=request.form("form_field")
    s=replace(s,"'","''")
    sql="insert into ... values ('" & s & "', ...)"


    Paul Malbon wrote:
    > Thanks for the reply tim. I should have been a bit more clearer in my
    > post. I realise that the error is caused by the apostrophe.
    >
    > You say double the apostrophe. Do you mind me asking how I would do
    > that? This web based form will be used by people who generally dont
    > use the internet and to ask them to 'double apostrophe' would confuse
    > them no end!!
    > Thanks again for your assitance
    >
    >
    > "Tim Slattery" <> wrote in message
    > news:...
    >> "Paul Malbon" <> wrote:
    >>
    >>> Hi,
    >>>
    >>> I have a form where the user enters a customer name, then clicks the
    >>> submit
    >>> button which then adds it to a database. This works absolutely fine
    >>> untill the name has an apostrophe in it.
    >>>
    >>> eg when the name entered is O'Hanlon and its submitted, I get this
    >>> error.....
    >>> Microsoft JET Database Engine (0x80040E14)
    >>> Syntax error (missing operator) in query expression ''O'Hanlon'

    >>
    >> Most likely the apostrophe appears as an extra delimiter in the SQL
    >> statement that's used to update the DB. The solution is to double the
    >> apostrophe before presenting the field to the database.
    >>
    >> --
    >> Tim Slattery
    >> MS MVP(DTS)
    >>


    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows [MVP], Jan 19, 2006
    #5
  6. Paul Malbon

    Paul Malbon Guest

    Reading and digesting now. Thanks for your time
    "Bob Barrows [MVP]" <> wrote in message
    news:...
    > Paul Malbon wrote:
    >> Hi,
    >>
    >> I have a form where the user enters a customer name, then clicks the
    >> submit button which then adds it to a database. This works absolutely
    >> fine untill the name has an apostrophe in it.
    >>
    >> eg when the name entered is O'Hanlon and its submitted, I get this
    >> error.....
    >> Microsoft JET Database Engine (0x80040E14)
    >> Syntax error (missing operator) in query expression ''O'Hanlon'
    >>
    >> any help would be great, and yes I'm a newbie!!
    >> thanks in advance
    >>

    > Another delimiter problem caused by the use of dynamic sql instead of
    > parameters. Tim explained about escaping your apostrophe by doubling it,
    > but there are other potential problems caused by the use of dynamic sql.
    > Read:
    > http://mvp.unixwiz.net/techtips/sql-injection.html
    > http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=
    >
    > http://groups.google.com/groups?hl=...=1&selm=
    >
    > Using Command object to parameterize CommandText:
    > http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e
    >
    > HTH,
    > Bob Barrows
    >
    > --
    > Microsoft MVP - ASP/ASP.NET
    > Please reply to the newsgroup. This email account is my spam trap so I
    > don't check it very often. If you must reply off-line, then remove the
    > "NO SPAM"
    >
     
    Paul Malbon, Jan 19, 2006
    #6
    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.
Similar Threads
  1. Kosio

    Floats to chars and chars to floats

    Kosio, Sep 16, 2005, in forum: C Programming
    Replies:
    44
    Views:
    1,295
    Tim Rentsch
    Sep 23, 2005
  2. Hongyu
    Replies:
    9
    Views:
    915
    James Kanze
    Aug 8, 2008
  3. M.Posseth

    receiving ??? chars instead of "special" chars

    M.Posseth, Nov 15, 2004, in forum: ASP .Net Web Services
    Replies:
    3
    Views:
    233
    Dan Rogers
    Nov 16, 2004
  4. NotGiven
    Replies:
    3
    Views:
    354
    Michael D. Kersey
    May 13, 2004
  5. GavMc
    Replies:
    4
    Views:
    342
    Evertjan.
    Sep 22, 2005
Loading...

Share This Page