INSERT: Text field with single- and double-quotes

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
 
J

John Vinson

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

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')
 
R

RSGinCA

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top