The Great ' issue (fixquotes)

enterbase · January 31st, 2004, 04:33 AM

Struggling to find a workaround for this sql failing.

Try inserting this to access using sql:

INSERT INTO imported(Song)
VALUES ('" & variable_song & "')

Now assume variable_song = "I don't know"

The single quote causes an error.
Now I can create a function to search and replace the offending single quote with a different character but this then affects the viewing of data in the underlying table.

Anyone know of a workaround that will let me insert this single quote?

Braxis · January 31st, 2004, 07:58 AM

To force SQL to recognise that you really, really want to put a single quote into a table, replace the single quote with two single quotes:

Code:

INSERT INTO imported(Song)
VALUES ('" & replace(variable_song,"'","''") & "')

Brian Skelton
Braxis Computer Services Ltd.

enterbase · January 31st, 2004, 10:29 AM

Would it not be possible to just set variable_song to "it''s my song" to do the same?

Braxis · January 31st, 2004, 10:47 AM

That's exactly what the Replace function does. If you prefer to do it on a seperate line:

Code:

variable_song=replace(variable_song,"'","''")

INSERT INTO imported(Song)
VALUES ('" & variable_song & "')

Brian Skelton
Braxis Computer Services Ltd.

enterbase · January 31st, 2004, 12:36 PM

Great! cooking on gas now......