Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > form field chars

Reply
Thread Tools

form field chars

 
 
Paul Malbon
Guest
Posts: n/a
 
      01-19-2006
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


 
Reply With Quote
 
 
 
 
Tim Slattery
Guest
Posts: n/a
 
      01-19-2006
"Paul Malbon" <(E-Mail Removed)> 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)
http://www.velocityreviews.com/forums/(E-Mail Removed)
 
Reply With Quote
 
 
 
 
Paul Malbon
Guest
Posts: n/a
 
      01-19-2006
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> "Paul Malbon" <(E-Mail Removed)> 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)
> (E-Mail Removed)



 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      01-19-2006
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"


 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      01-19-2006
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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> "Paul Malbon" <(E-Mail Removed)> 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)
>> (E-Mail Removed)


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


 
Reply With Quote
 
Paul Malbon
Guest
Posts: n/a
 
      01-19-2006
Reading and digesting now. Thanks for your time
"Bob Barrows [MVP]" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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"
>



 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to truncate char string fromt beginning and replace chars instring by other chars in C or C++? Hongyu C++ 9 08-08-2008 12:18 PM
Floats to chars and chars to floats Kosio C Programming 44 09-23-2005 09:49 AM
Pass hidden form field value to another form field to insert in db GavMc ASP General 4 09-22-2005 06:33 PM
receiving ??? chars instead of "special" chars M.Posseth ASP .Net Web Services 3 11-16-2004 07:00 PM
copy and paste form RTF document into field in asp form cause it to bypass field length and javascript validation - how to overcome? NotGiven ASP General 3 05-13-2004 12:15 AM



Advertisments