Using the ' in an web based access form

[Ken]

hi,

I have a web based form made using asp code.

Everything is awesome until you try to put
a name with an apostrophe in it like O'Neill
for example.

How do I tell the database that that character is
ok. I have tried obvious fixes like properties in the
table and form box but to no avail.
Thanks for any help in advance.
Ken

 

[Norman Yuan]

I just posted back to a similar question in another NG yesterday. Here is
the post:

You must have SQL statement in your code somewhere, like

"INSERT INTO ...(Field1,.. ) VALUES ('" & txtBox1 & "'," & ...")"

Or

"UPDATE ...SET Field1='" & txtBox1 & "'," & ....

Since SQL statement use character ' to indicate text value, if the text
value you use in SQL statement contains character ', database engine will
treat it as either beginning or ending of text value (they must be in pair),
thus causes your SQL statement out of syntex.

To overcome this problem, in ad hoc SQL statement in your code, you double
possible character ' in text value passed to SQL statement, using Replace()
function:

strSQL="INSERT INTO ...(Field1,.. ) VALUES ('" &
Replace(txtBox1,"'","''") & "'," & ...")"

You have to use Replace() function on all possible text value where
character ' could be appear.

Another approach is to use ADO.Command object's parameter to pass text value
to database, so that you do not have to give character ' a special treat.

HTH

 

[Ken]

-----Original Message-----
I just posted back to a similar question in another NG yesterday. Here is
the post:

You must have SQL statement in your code somewhere, like

"INSERT INTO ...(Field1,.. ) VALUES ('" & txtBox1 & "'," & ...")"

Or

"UPDATE ...SET Field1='" & txtBox1 & "'," & ....

Since SQL statement use character ' to indicate text value, if the text
value you use in SQL statement contains character ', database engine will
treat it as either beginning or ending of text value (they must be in pair),
thus causes your SQL statement out of syntex.

To overcome this problem, in ad hoc SQL statement in your code, you double
possible character ' in text value passed to SQL statement, using Replace()
function:

strSQL="INSERT INTO ...(Field1,.. ) VALUES ('" &
Replace(txtBox1,"'","''") & "'," & ...")"

You have to use Replace() function on all possible text value where
character ' could be appear.

Another approach is to use ADO.Command object's parameter to pass text value
to database, so that you do not have to give character ' a special treat.

HTH




.