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]

"Paul Malbon" <no****@all.com> 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)
Sl********@bls.gov

[Bob Barrows [MVP]]

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&l...TNGP12.phx.gbl

http://groups.google.com/groups?hl=e...tngp13.phx.gbl

Using Command object to parameterize CommandText:
http://groups-beta.google.com/group/...e36562fee7804e

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]

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" <Sl********@bls.gov> wrote in message
news:9v********************************@4ax.com...

"Paul Malbon" <no****@all.com> 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)
Sl********@bls.gov

[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/micro...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" <Sl********@bls.gov> wrote in message
news:9v********************************@4ax.com...

"Paul Malbon" <no****@all.com> 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)
Sl********@bls.gov

--
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]

Reading and digesting now. Thanks for your time
"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:uV**************@TK2MSFTNGP14.phx.gbl...

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&l...TNGP12.phx.gbl

http://groups.google.com/groups?hl=e...tngp13.phx.gbl

Using Command object to parameterize CommandText:
http://groups-beta.google.com/group/...e36562fee7804e

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"