INSERT: Text field with single- and double-quotes

  • Thread starter Thread starter RSGinCA
  • Start date Start date
R

RSGinCA

I've got a program that needs to add records to a table.

One of the fields on the record is a text field, and the data that is going to
be input will sometimes have single- and/or double-quotes. This screws up the
INSERT command.

I'm not dealing with the text directly, but rather with variables which contain
the text, however when the program executes, a command like the following might
be generated:

dbs.Execute "INSERT INTO tblBusiness (BusinessID, BusinessName, Comment) VALUES
('10', 'Fred's Diner', 'Fred's mother said, "Fred's a fine cook... a fine
cook!"');"

Note: this is an oversimplified example, to illustrate the problem. My
situation actually contains a much longer text field which can frequently
contain combinations of both single and double quotes.

Those combinations of quotes doesn't work.

Is there some way to get the INSERT command to work with unpredictable
combinations of single and double quotes in the VALUES clause? Or, does
somebody have an alternative way to suggest for my program to add these
records?

Rick
 
Is there some way to get the INSERT command to work with unpredictable
combinations of single and double quotes in the VALUES clause?
Click to expand...

This MIGHT work - it's version dependent:

- Use your choice of " or ' to delimit the string
- Double up every instance of the delimiter within the string. That
is, to insert Joe's Bar use

Values('Joe''s Bar')
 
That did the trick. I used the Replace function to accomplish the replacement:

strTemp = Replace(Me.CriteriumDesc, "'", "''") ' **Replace ' (1 single quote)
with '' (2 single quotes)

Then I 'inserted' strTemp into my INSERT command.

Thanks
Rick
 
Back
Top