|
Subject:
|
The Great ' issue (fixquotes)
|
|
Posted By:
|
enterbase
|
Post Date:
|
1/31/2004 3:33:23 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?
|
|
Reply By:
|
Braxis
|
Reply Date:
|
1/31/2004 6:58:12 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:
INSERT INTO imported(Song)
VALUES ('" & replace(variable_song,"'","''") & "')
Brian Skelton
Braxis Computer Services Ltd.
|
|
Reply By:
|
enterbase
|
Reply Date:
|
1/31/2004 9:29:27 AM
|
Would it not be possible to just set variable_song to "it''s my song" to do the same?
|
|
Reply By:
|
Braxis
|
Reply Date:
|
1/31/2004 9:47:14 AM
|
That's exactly what the Replace function does. If you prefer to do it on a seperate line:
variable_song=replace(variable_song,"'","''")
INSERT INTO imported(Song)
VALUES ('" & variable_song & "')
Brian Skelton
Braxis Computer Services Ltd.
|
|
Reply By:
|
enterbase
|
Reply Date:
|
1/31/2004 11:36:48 AM
|
Great! cooking on gas now......
|
