Saving long text field to Access - Syntax Issue Problem

[Drifter]

The quote below is part of the information i want to save to a MS Access
database - or update, as the case may be.

The original database was built a long time ago, and looking at the code for
add/update on the original site-it does not compensate for odd syntax like
the quotes. Problem i am hitting now is syntax issue in the SQL when I go to
save/update. The syntax present causes failures every time.

User said she had no problems adding product before now - but i cannot spot
any code that avoids the issues of odd punctuation when i go to create the
SQL.



---"
It presents strategies for including humor and a positive attitude into the
lives of healthcare workers.
.............................................................................
................................. Click ''Buy Me'' below to purchase &
download immediately (PDF version). To buy the HARD COPY or DISK versions,
Click ''Ordering Information'' above & download an Order Form for the US or
Canada.
.............................................................................
............................
---"

Any help appreciated.

D

 

[Aaron Bertrand [MVP]]

the quotes. Problem i am hitting now is syntax issue in the SQL when I go
to

save/update. The syntax present causes failures every time.

We can't offer much assistance unless you SHOW US that part.

 

[Aaron Bertrand - MVP]

It is taking the single quotes on the String

VFAQ.
http://www.aspfaq.com/2035

these are 2 single quotes in a row btw

That wasn't clear initially.

 

[Drifter]

Thanks for that link.

Just noticed a very odd... oddity. When I am drawing the info from the
database, the data is putting it into the Text field as if it were Single
Quotes, Even though the original Data was a double quote (''). To make this
clear - if I save the string (''save this'') It gets placed into the
database as (''save this''), but when I populate the Text field (for an easy
edit) on the ASP page - the text field populates like this ('save this')
which means that when I go to save this again, it messes with the SQL. The
..replace will work, it is just a very odd way for the text box to populate -
interpreting the ('') as ('). I never knew it did that.

D

 

[Aaron Bertrand - MVP]

When you do the replace, ' becomes two of them '' (not to be confused with a
double quote, ").

This replacement is passed this way to the database. The reason we do this
at all is so that the database understands which single quotes delimit the
string, and which single quotes are embedded within it. When it finds two
'' embedded within a string, it treats the first as an escape character, but
only one of them ends up in the database.

Make sense? The textarea isn't doing anything funny... go look at the data
in the database after the insert, and you will see only one ' ...

Always "encode" the data on the way in. Presentation is a different story.

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/