p2p.wrox.com: Archives - access

access_asp thread: Single Quotation Marks in INPUT - TEXT Field

Message #1 by "Eric Levine" <eric@d...> on Mon, 20 May 2002 13:47:48 -0400

This is a multi-part message in MIME format.

------=_NextPart_000_0003_01C20004.F17AEEF0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit

Good afternoon,

 

I am hoping someone can help me with this rather simple task that my
brain cannot grasp.  Whenever a user enters a name that contains a
single quotation mark in an INPUT - TEXT box, the system is crapping
out.  I know that it has to do with the fact that ASP is reading the
single quotation as code.  Can someone please help me with how to
correct this.  I have tried to Server.URL/HTMLEncode it, and also
Replace(txtString,"'","''") to no avail.

 

The field is being written to an Access DB.

 

Thanks in advance.

 

Regards,

Eric

Message #2 by Karri Peterson <KPeterson@C...> on Mon, 20 May 2002 13:13:05 -0500

This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.

------_=_NextPart_001_01C20029.FCE17B90
Content-Type: text/plain

in general, I have found that Microsoft IIS doesn't like the ' sign and
that's what happens.  Have you tried the urlencode(') thing ?
 
Karri

-----Original Message-----
From: Eric Levine [mailto:eric@d...]
Sent: Monday, May 20, 2002 12:48 PM
To: Access ASP
Subject: [access_asp] Single Quotation Marks in INPUT - TEXT Field



Good afternoon,

 

I am hoping someone can help me with this rather simple task that my brain
cannot grasp.  Whenever a user enters a name that contains a single
quotation mark in an INPUT - TEXT box, the system is crapping out.  I know
that it has to do with the fact that ASP is reading the single quotation as
code.  Can someone please help me with how to correct this.  I have tried to
Server.URL/HTMLEncode it, and also Replace(txtString,"'","''") to no avail.

 

The field is being written to an Access DB.

 

Thanks in advance.

 

Regards,

Eric

 

---

Message #3 by "Eric Levine" <eric@d...> on Mon, 20 May 2002 15:08:52 -0400

This is a multi-part message in MIME format.

------=_NextPart_000_0013_01C20010.44B7FB20
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit

Yep, tried to URLEncode it already.. Doesn't help.

 

 

-----Original Message-----
From: Karri Peterson [mailto:KPeterson@C...] 
Sent: Monday, May 20, 2002 2:13 PM
To: Access ASP
Subject: [access_asp] RE: Single Quotation Marks in INPUT - TEXT Field

 

in general, I have found that Microsoft IIS doesn't like the ' sign and
that's what happens.  Have you tried the urlencode(') thing ?

 

Karri

-----Original Message-----
From: Eric Levine [mailto:eric@d...]
Sent: Monday, May 20, 2002 12:48 PM
To: Access ASP
Subject: [access_asp] Single Quotation Marks in INPUT - TEXT Field

Good afternoon,

 

I am hoping someone can help me with this rather simple task that my
brain cannot grasp.  Whenever a user enters a name that contains a
single quotation mark in an INPUT - TEXT box, the system is crapping
out.  I know that it has to do with the fact that ASP is reading the
single quotation as code.  Can someone please help me with how to
correct this.  I have tried to Server.URL/HTMLEncode it, and also
Replace(txtString,"'","''") to no avail.

 

The field is being written to an Access DB.

 

Thanks in advance.

 

Regards,

Eric

 

--- 

---

Message #4 by Karri Peterson <KPeterson@C...> on Mon, 20 May 2002 14:21:18 -0500

This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.

------_=_NextPart_001_01C20033.848B9EF0
Content-Type: text/plain

Eric,
 
What does the html look like in the form--are you using single or double
quotes there?
 
Karri

-----Original Message-----
From: Eric Levine [mailto:eric@d...]
Sent: Monday, May 20, 2002 2:09 PM
To: Access ASP
Subject: [access_asp] RE: Single Quotation Marks in INPUT - TEXT Field



Yep, tried to URLEncode it already.... Doesn't help.

 

 

-----Original Message-----
From: Karri Peterson [mailto:KPeterson@C...] 
Sent: Monday, May 20, 2002 2:13 PM
To: Access ASP
Subject: [access_asp] RE: Single Quotation Marks in INPUT - TEXT Field

 

in general, I have found that Microsoft IIS doesn't like the ' sign and
that's what happens.  Have you tried the urlencode(') thing ?

 

Karri

-----Original Message-----
From: Eric Levine [mailto:eric@d...]
Sent: Monday, May 20, 2002 12:48 PM
To: Access ASP
Subject: [access_asp] Single Quotation Marks in INPUT - TEXT Field

Good afternoon,

 

I am hoping someone can help me with this rather simple task that my brain
cannot grasp.  Whenever a user enters a name that contains a single
quotation mark in an INPUT - TEXT box, the system is crapping out.  I know
that it has to do with the fact that ASP is reading the single quotation as
code.  Can someone please help me with how to correct this.  I have tried to
Server.URL/HTMLEncode it, and also Replace(txtString,"'","''") to no avail.

 

The field is being written to an Access DB.

 

Thanks in advance.

 

Regards,

Eric

 

--- 

--- 

---

Message #5 by Karri Peterson <KPeterson@C...> on Mon, 20 May 2002 14:27:01 -0500

This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.

------_=_NextPart_001_01C20034.50F3CE90
Content-Type: text/plain

http://support.microsoft.com/search/preview.aspx?scid=kb;en-us;Q190742
<http://support.microsoft.com/search/preview.aspx?scid=kb;en-us;Q190742> 
 
Here's what Microsoft is saying--it seems to be saying to parse for it.  I
usually use like a split function 
off of like the ' and then add the two strings together again to use in your
program:
 
 
sArray=split(sString, "'")
sNewString = sArray(0) & "'" & sArray(1)
 
Does this make sense?
Am I helping at all?
 
Karri

-----Original Message-----
From: Eric Levine [mailto:eric@d...]
Sent: Monday, May 20, 2002 2:09 PM
To: Access ASP
Subject: [access_asp] RE: Single Quotation Marks in INPUT - TEXT Field



Yep, tried to URLEncode it already.... Doesn't help.

 

 

-----Original Message-----
From: Karri Peterson [mailto:KPeterson@C...] 
Sent: Monday, May 20, 2002 2:13 PM
To: Access ASP
Subject: [access_asp] RE: Single Quotation Marks in INPUT - TEXT Field

 

in general, I have found that Microsoft IIS doesn't like the ' sign and
that's what happens.  Have you tried the urlencode(') thing ?

 

Karri

-----Original Message-----
From: Eric Levine [mailto:eric@d...]
Sent: Monday, May 20, 2002 12:48 PM
To: Access ASP
Subject: [access_asp] Single Quotation Marks in INPUT - TEXT Field

Good afternoon,

 

I am hoping someone can help me with this rather simple task that my brain
cannot grasp.  Whenever a user enters a name that contains a single
quotation mark in an INPUT - TEXT box, the system is crapping out.  I know
that it has to do with the fact that ASP is reading the single quotation as
code.  Can someone please help me with how to correct this.  I have tried to
Server.URL/HTMLEncode it, and also Replace(txtString,"'","''") to no avail.

 

The field is being written to an Access DB.

 

Thanks in advance.

 

Regards,

Eric

 

--- 

--- 

---

Message #6 by "Eric Levine" <eric@d...> on Mon, 20 May 2002 15:30:08 -0400

This is a multi-part message in MIME format.

------=_NextPart_000_0022_01C20013.3D91A910
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit

Karri,

 

I appreciate your help.  I am a bit dumbfounded being that even though
the code craps out, the changes are being written to the DB, with the
single quotation marks.  I need to put in some stop gaps to see where in
the code the error is occurring.  Once I narrow it down, I will
hopefully be able to rectify the problem.

 

Thanks again.

 

Eric

 

-----Original Message-----
From: Karri Peterson [mailto:KPeterson@C...] 
Sent: Monday, May 20, 2002 3:27 PM
To: Access ASP
Subject: [access_asp] RE: Single Quotation Marks in INPUT - TEXT Field

 

http://support.microsoft.com/search/preview.aspx?scid=kb;en-us;Q190742

 

Here's what Microsoft is saying--it seems to be saying to parse for it.
I usually use like a split function 

off of like the ' and then add the two strings together again to use in
your program:

 

 

sArray=split(sString, "'")

sNewString = sArray(0) & "'" & sArray(1)

 

Does this make sense?

Am I helping at all?

 

Karri

-----Original Message-----
From: Eric Levine [mailto:eric@d...]
Sent: Monday, May 20, 2002 2:09 PM
To: Access ASP
Subject: [access_asp] RE: Single Quotation Marks in INPUT - TEXT Field

Yep, tried to URLEncode it already.. Doesn't help.

 

 

-----Original Message-----
From: Karri Peterson [mailto:KPeterson@C...] 
Sent: Monday, May 20, 2002 2:13 PM
To: Access ASP
Subject: [access_asp] RE: Single Quotation Marks in INPUT - TEXT Field

 

in general, I have found that Microsoft IIS doesn't like the ' sign and
that's what happens.  Have you tried the urlencode(') thing ?

 

Karri

-----Original Message-----
From: Eric Levine [mailto:eric@d...]
Sent: Monday, May 20, 2002 12:48 PM
To: Access ASP
Subject: [access_asp] Single Quotation Marks in INPUT - TEXT Field

Good afternoon,

 

I am hoping someone can help me with this rather simple task that my
brain cannot grasp.  Whenever a user enters a name that contains a
single quotation mark in an INPUT - TEXT box, the system is crapping
out.  I know that it has to do with the fact that ASP is reading the
single quotation as code.  Can someone please help me with how to
correct this.  I have tried to Server.URL/HTMLEncode it, and also
Replace(txtString,"'","''") to no avail.

 

The field is being written to an Access DB.

 

Thanks in advance.

 

Regards,

Eric

 

--- 

--- 

--- 

---

Message #7 by "Ken Schaefer" <ken@a...> on Tue, 21 May 2002 11:45:17 +1000

This is a multi-part message in MIME format.

------=_NextPart_000_0178_01C200BC.FA327E60
Content-Type: text/plain;
	charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Karri Peterson" <KPeterson@C...>
Subject: [access_asp] RE: Single Quotation Marks in INPUT - TEXT Field

: in general, I have found that Microsoft IIS doesn't like the ' sign 
and
: that's what happens.  Have you tried the urlencode(') thing ?

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

This has *nothing*  to do with IIS.

Single quote marks are string delimiters in SQL statements - they are 
used to mark the beginning and the end of a string literal. When you 
send an SQL statement to a database for processing, if you embed a quote 
mark into a string literal, the database doesn't know what to do with 
the rest of the string:

SELECT field1 FROM table1 WHERE Name =3D 'O'Conner'
-----------------------------------------^

The database thinks that you want to do WHERE Name =3D '0' and it 
doesn't know what to do with Conner' so it throws an exception.

If you want to send a string to database that contains a single quote 
mark, you need to "escape" the quote mark, by doubling it, so:

SELECT field1 FROM table1 WHERE Name =3D 'O''Conner'

will work fine, because the database knows that two quote marks in a row 
means you want a literal quote mark, not a string delimiter.
You can use a function like:
http://www.adopenstatic.com/resources/code/SafeSQL.asp
to handle single quote marks

If this is *not* the problem, you will need to post the exact error 
message you are receiving, and the line(s) of code that are generating 
the error.

Cheers
Ken

: -----Original Message-----
: From: Eric Levine [mailto:eric@d...]
: Sent: Monday, May 20, 2002 12:48 PM
: To: Access ASP
: Subject: [access_asp] Single Quotation Marks in INPUT - TEXT Field
:
:
:
: Good afternoon,
:
: 
:
: I am hoping someone can help me with this rather simple task that my 
brain
: cannot grasp.  Whenever a user enters a name that contains a single
: quotation mark in an INPUT - TEXT box, the system is crapping out.  I 
know
: that it has to do with the fact that ASP is reading the single 
quotation as
: code.  Can someone please help me with how to correct this.  I have 
tried to
: Server.URL/HTMLEncode it, and also Replace(txtString,"'","''") to no 
avail.
:
: 
:
: The field is being written to an Access DB.
:
: 
:
: Thanks in advance.
:
: 
:
: Regards,
:
: Eric
:
: 
:
:
:
:
:
:

Message #8 by "Ken Schaefer" <ken@a...> on Tue, 21 May 2002 13:11:40 +1000

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Karri Peterson" <KPeterson@C...>
Subject: [access_asp] RE: Single Quotation Marks in INPUT - TEXT Field

: http://support.microsoft.com/search/preview.aspx?scid=kb;en-us;Q190742
: <http://support.microsoft.com/search/preview.aspx?scid=kb;en-us;Q190742>
:
: Here's what Microsoft is saying--it seems to be saying to parse for it.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

That's a slow of doing:

strText = Replace(strText, "'", "''")

     -or-

strText = Replace(strText, Chr(39), Chr(39) & Chr(39))

All the Microsoft code is doing is looping through the string 1 character at
a time and inserting an extra single quote everytime it finds one - Chr(39)
is a the ASCII code for a single quote. IMHO, It's slow because (a) the
looping construct and (b) because of all the crappy string concatenation
problems you run into with many concatenations & VB/VBScript.

Cheers
Ken

Message #9 by Karri Peterson <KPeterson@C...> on Tue, 21 May 2002 09:02:27 -0500

This probably IS faster now that you mention it--probably faster than
the split thing also that I mentioned.  Would the database not have trouble
parsing
the ' ' as well, tho?

Ken, you're pretty sharp.  vbscript is clunky as it is--it really takes some
precision to maximize it.


Karri

-----Original Message-----
From: Ken Schaefer [mailto:ken@a...]
Sent: Monday, May 20, 2002 10:12 PM
To: Access ASP
Subject: [access_asp] RE: Single Quotation Marks in INPUT - TEXT Field


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Karri Peterson" <KPeterson@C...>
Subject: [access_asp] RE: Single Quotation Marks in INPUT - TEXT Field


: http://support.microsoft.com/search/preview.aspx?scid=kb;en-us;Q190742
: <http://support.microsoft.com/search/preview.aspx?scid=kb;en-us;Q190742>
:
: Here's what Microsoft is saying--it seems to be saying to parse for it.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

That's a slow of doing:

strText = Replace(strText, "'", "''")

     -or-

strText = Replace(strText, Chr(39), Chr(39) & Chr(39))

All the Microsoft code is doing is looping through the string 1 character at
a time and inserting an extra single quote everytime it finds one - Chr(39)
is a the ASCII code for a single quote. IMHO, It's slow because (a) the
looping construct and (b) because of all the crappy string concatenation
problems you run into with many concatenations & VB/VBScript.

Cheers
Ken

Message #10 by "Ken Schaefer" <ken@a...> on Thu, 23 May 2002 13:31:21 +1000

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Karri Peterson" <KPeterson@C...>
Subject: [access_asp] RE: Single Quotation Marks in INPUT - TEXT Field

: This probably IS faster now that you mention it--probably faster than
: the split thing also that I mentioned.  Would the database not have
trouble
: parsing
: the ' ' as well, tho?

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

It depends on what you're trying to do :-)

SELECT * FROM table1 WHERE UserName = 'O''Conner'

will mean that the database searches the UserName column for the value
O'Conner - the database treats the two single quotes as a literal single
quote - that is part of the SQL standard: it's the way you tell a database
to search for a literal single quote.

If you do:

SELECT * FROM table1 WHERE UserName = 'O'Conner'

then you're telling the database to search for UserNames that equal O, but
the database doesn't know what to do with the Conner' part, and so an error
is generated.

Cheers
Ken

Return to Index

P2P Archives