Quotes and Apostrophes Question

[Guest]

On 5/02/2006 Sylvain Lafontaine wrote:

First, just to be sure, you must delete all the blank spaces in the call to
the replace function.

Str2 = Replace("'" & Str1 & "'", "'", "''")

Second, you don't show us how you are building your sql string; however, the
above is probably an error because you are adding and doubling the
delimiting single quotes (or apostrophes) at the same. I think that you
need is:

Str2 = Replace(Str1, "'", "''")

or maybe:

Str2 = "'" & Replace(Str1, "'", "''") & "'"

Third, you don't need to replace ' or " if you are using a parameter query
or a stored procedure or anything else that is not a « dynamically build sql
string ».

Fourth, usually, you don't have to replace both ' and ": it's either one of
these two and the one that you must replace is the one that you have used as
the string delimiter and doubling the other one will lead to error; for
example, storing O''Brien instead of O'Brien.

Of course, if you have a string delimiter embedded into another dynamically
build sql string, then you must replace these two but you usually see this
only in some complex code.

 

[Guest]

Sylvain:

The spaces were added, for effect, and ease of reading. I understand the
formatting. Although, like you, I'm unsure of the " around the variable name.

My string, in this case, is not really being built. It is simply coming
from data entered into a text box, and is to be inserted/Updated to an
existing table.

Because the field, in the table, is a Memo I have found it is necessary to
move the textbox value to a string, before attempting the insert. Afterward,
we began having issues with the ' and ".

I don't have an exact copy, of the error, though it is a Syntax error, and
references the piece of text that includes the ' or ". Once the offending
character(s) are removed, the text saves fine.

So, I think Replace will be necessary, but in this case it is a pertinent
question as to whether or not I can run back-to-back Replace statements,
without creating a problem.

Thanks.

Sharkbyte