form field chars

[Paul Malbon]

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

 

[Tim Slattery]

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.

 

[Paul Malbon]

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

 

[Bob Barrows [MVP]]

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&[email protected]

http://groups.google.com/groups?hl=...=1&[email protected]

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

HTH,
Bob Barrows

 

[Bob Barrows [MVP]]

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]

Reading and digesting now. Thanks for your time

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&[email protected]

http://groups.google.com/groups?hl=...=1&[email protected]

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"