evansj
asked on
Correctly passing strings containing punctuation marks
Alright folks,
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
begin
b:= listCl.Items[x];
a:= a + 'cdesc = ' + '''' + b + '''' + ' or ';
end;
sequel:= sql + a;
sequel2:= sql2 + a;
screen.Cursor:=crHourGlass ;
dmRep.query1.Close;
dmRep.query1.SQL.Clear;
dmRep.query1.SQL.Add(seque l);
dmRep.query1.Open;
dmRep.query3.Close;
dmRep.query3.SQL.Clear;
dmRep.query3.SQL.Add(seque l2);
dmRep.query3.Open;
dbgrid3.Visible:=true;
dbtext1.Visible:=true;
label22.Caption:=dmRep.Que ry3.fieldB yName('QTY ').AsStrin g;
screen.Cursor:=crDefault;
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:
Example choices
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.
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
begin
b:= listCl.Items[x];
a:= a + 'cdesc = ' + '''' + b + '''' + ' or ';
end;
sequel:= sql + a;
sequel2:= sql2 + a;
screen.Cursor:=crHourGlass
dmRep.query1.Close;
dmRep.query1.SQL.Clear;
dmRep.query1.SQL.Add(seque
dmRep.query1.Open;
dmRep.query3.Close;
dmRep.query3.SQL.Clear;
dmRep.query3.SQL.Add(seque
dmRep.query3.Open;
dbgrid3.Visible:=true;
dbtext1.Visible:=true;
label22.Caption:=dmRep.Que
screen.Cursor:=crDefault;
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:
Example choices
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.
Put double quotes around the entire string.
Cheers,
Raymond.
Cheers,
Raymond.
Not much left to say here, Ray... you've said it all.
hi evansj,
try this partchange
a:= a + 'cdesc = ' + QuotedStr( b ) + ' or ';
meikl
try this partchange
a:= a + 'cdesc = ' + QuotedStr( b ) + ' or ';
meikl
Actually, rwilson has *not* addressed the problem completely - which is unusual for him.
Things get more tangled when the string contains *both* embedded single and double quotes.
There are two possible approaches that work. You can either cop out of the whole problem by passing the string value to the query via a parameter, or you can write a (very elaborate) routine to correctly escape single quotes the way you would in Delphi code.
For example, the string
Can't get 'enough of quotes!'
has to become
Can''t get ''enough of quotes!''
Personally I favour passing the string value as a parameter of a TQuery.
Things get more tangled when the string contains *both* embedded single and double quotes.
There are two possible approaches that work. You can either cop out of the whole problem by passing the string value to the query via a parameter, or you can write a (very elaborate) routine to correctly escape single quotes the way you would in Delphi code.
For example, the string
Can't get 'enough of quotes!'
has to become
Can''t get ''enough of quotes!''
Personally I favour passing the string value as a parameter of a TQuery.
ASKER
rwilson:
Doesn't work.
Doesn't work.
ASKER
meikl:
Doesn't work.
Passing as a query parameter would work, but how would you implement this?
Doesn't work.
Passing as a query parameter would work, but how would you implement this?
ASKER
Meikl: resubmit your suggestion as an answer, it works:
a:= a + 'cdesc = ' + quotedstr(b) + ' or ';
Thanks.
BTW, I don't know why it did not work at first.
a:= a + 'cdesc = ' + quotedstr(b) + ' or ';
Thanks.
BTW, I don't know why it did not work at first.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Interestingly, QuotedStr does not appear to do anything with double-quote chars (at least according to the help...)
Cheers,
Raymond.
Cheers,
Raymond.
ASKER
a:=copy(a,1,((length(a)-3)
the code should read as follows:
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
begin
b:= listCl.Items[x];
a:= a + 'cdesc = ' + '''' + b + '''' + ' or ';
end;
a:=copy(a,1,((length(a)-3)
sequel:= sql + a;
sequel2:= sql2 + a;
screen.Cursor:=crHourGlass
dmRep.query1.Close;
dmRep.query1.SQL.Clear;
dmRep.query1.SQL.Add(seque
dmRep.query1.Open;
dmRep.query3.Close;
dmRep.query3.SQL.Clear;
dmRep.query3.SQL.Add(seque
dmRep.query3.Open;
dbgrid3.Visible:=true;
dbtext1.Visible:=true;
label22.Caption:=dmRep.Que
screen.Cursor:=crDefault;
and the string produced would be:
'and (cdesc = 'Can't get in touch' or cdesc = 'New 'HOLES' in prog.)'