Correctly passing strings containing punctuation marks
Posted on 1999-06-24
Here is my dilemma:
I have an app that allows the user to select multiple text items and query the database based on a string that I build. Here is the code:
sql:='select CE,FY,SO,PD from RS_' + copy(cboSite.Text,1,3) + '.FED WHERE PD IS NOT NULL';
sql2:='select count(*) "QTY" from RS_' + copy(cboSite.Text,1,3) + '.FED WHERE PD IS NOT NULL';
a:=' and (';
for x:= 0 to listCl.Items.Count -1 do
a:= a + 'cdesc = ' + '''' + b + '''' + ' or ';
sequel:= sql + a;
sequel2:= sql2 + a;
Now if the user can click a choice from a combo box. As long as he clicks a choice it will add it to a listbox.
Now when he clicks the query button, it will query the database with the query that's been built for his choices. The problem arises if the text has punctuation in it like single quotes or apostrophe's:
Can't get in touch
New 'HOLES' in prog.
with the above code would produce this string:
'and cdesc = 'Can't get in touch' or cdesc = 'New 'HOLES' in prog.'
when the string is fed to the query object it results in errors like 'Missing right quote' errors. How would you code around this to allow text with punctuation to be correctly passed to the query object? I can get around this if I restrict the choice to ONE item only by the parambyname property; this allows punctuation in the text.
Hope you can understand what I'm trying to do based on my code snippet and explanation.