Apostrophe is screwing up database query.

[sarver311]

Hey Everyone,

I know the issue I am just seeing if I might be able to get suggestions for a workaround to a problem I discovered. Here is an example of the issue.

I have a voicemail utility that lets you enter some basic stuff in edit boxes, gathers that data then send it to an ms sql database. What happens is if there is an ' in the any of the fields it throws off my insert query. So here is an example of what happens

i pull data from an dialog and assign it to a variable varwithdescription
let>varwithdescription=can't install driver

then i send the data with the sql insert line with something like this.

insert into dbo.voicemails (vmaildescription) values ('%varwithdescription%')

When it goes to send the data Can't install driver to the "vmaildescription" field it tries to send "can" and then has nowhere to send the rest of the sentence because the apostrophe in can't is making it think there is a new value to send.

I was contemplating some sort of method where i replace ' with some sort of word of character and i can just substitute the apostrophe for something more sql friendly before sending the data then substitute the it back to an apostrophe when retrieving the data but I was curious if there was a more elegant workaround.

Thanks in advance!

[JRL]

You've hit on the only solution I know about. Elegance comes into play when you select the text to replace the apostrophe. Though it seems obvious I'm going to mention this anyway.

Be sure the substitution text won't accidentally be duplicated in a normal string. You don't want to add an apostrophe when you retrieve your data from the database.

In other words: If you were to decide to substitute "ll" for an apostrophe, your phrase "can't install driver" would go into the database as "canllt install driver". Unfortunately it would come out of the database as "can't insta' driver".

[gdyvig]

Hi sarver311,

I found this tip on another site:

...replace each occurrence of single quote by a pair of single quotes. SQL will accept this syntax and when encountering the single quotes pair, will drop one and include the other in the string itself. You still need starting and ending single quotes to enclose the whole string.

Gale

[sarver311]

That worked! EXACTLY what I was looking for. That saves me some banging my head on the keyboard.

Thanks to both of you I really appreciate your quick and helpful responses.