Solved

Query with '

Posted on 2002-04-10
5
250 Views
Last Modified: 2012-05-04
Hello!
I have a problem with an SQL query done in Delphi on an Access97 database.

Here is the query:
(Ok, values are in french but I think you can figure out the SQL within it, don't you ? ;-)

update SOUMISSIONS
SET no_client = 3781799,
no_vendeur = 99,
titre_soumission = "Ceci est un titre",
message_accueil = "Voici la soumission telle que demandée.",
message_fin = "Si vous avez des questions, n'hésitez pas à nous contacter.",
date_soum = #2002-04-10#,
nom_vendeur = "Wayne Gretzky",
poste_vendeur = "Centre gaucher"
WHERE no_soumission = :Param1

There is a problem with the "message_fin = ..." part (6th line) since there is a ' mark (how do you name this mark in english ?).  The Delphi error says that it doesn't find the parameter Param1.  The error is raised when I try to initialise the parameter (...ParamByName('Param1').AsInteger := ...).

I thought at first that the problem was like a Delphi string where you have to double the ' mark so I managed to code a little function which does the job.  Here is the code:

function fxValidString(S: String): String;
var
   I:         Integer;
   sValid:    String;
begin
sValid := '';
for I := 1 to length(S) do
    begin
    if (S[I] = '''') then
       sValid := sValid + ''''''
    else
    if (S[I] = '"') then
       sValid := sValid + '""'
    else
        sValid := sValid + S[I];
    end;
fxValidString := sValid;
end;

The function workd fine and the error is passed out BUT there is still a little problem:  the ' mark are all doubled once stored in the database!!  Like if Delphi didn't see any problem with the ' mark and saw them as pure text or something.

---

So at last here is my Q:  How can I manage ' (and " once we're at it) in Delphi/SQL ?

I hand you the code that raises the exception:

qSoumission.sql.clear;
qSoumission.sql.add('update SOUMISSIONS');
qSoumission.sql.add('SET no_client = ' + txtNoClient.text + ', ');
qSoumission.sql.add('no_vendeur = ' + cbVendeurs.text + ', ');
qSoumission.sql.add('titre_soumission = "' + fxValidString(txtTitre.text) + '", ');
qSoumission.sql.add('message_accueil = "' + fxValidString(mMessageDebut.text) + '", ');
qSoumission.sql.add('message_fin = "' + fxValidString(mMessageFin.text) + '", ');
qSoumission.sql.add('date_soum = #' + DateToStr(dDateSoum) + '#, ');
qSoumission.sql.add('nom_vendeur = "' + txtNomVendeur.text + '", ');
qSoumission.sql.add('poste_vendeur = "' + txtPoste.text + '"');
qSoumission.sql.add('WHERE no_soumission = :Param1');

qSoumission.ParamByName('Param1').AsInteger := iNextNoSoum;
qSoumission.ExecSQL;


Any help ?
Am I any clear ??  Please ask if you need me to be more precise.
Thanks!
0
Comment
Question by:qas
  • 2
  • 2
5 Comments
 
LVL 1

Accepted Solution

by:
JDN earned 200 total points
ID: 6932313
You cannot use the single quote character ' (ascii character 39) in an sql query.

I solved this problem using a function that's replacing the single quote for a the ´ character. Perhaps there are other ways, but I always use the following function without any problem:


function Check_Quote(s : string) : string;
var i : integer;
begin
   do while pos(chr(39),s) > 0 begin
      i := pos(chr(39),s)
      s[i] := '´';
   end;
   result := s;
end;


For any string you're passing to a query, you must use this function.

Hope this helps,
JDN
0
 
LVL 1

Expert Comment

by:JDN
ID: 6932322
Sorry, first line below BEGIN must be:

while pos(chr(39),s) > 0 do begin

(all these different languages...)

JDN
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6932324
no, not clear,
but i guess you have problems with fieldcontents like
O'Brian,
which must be as pascal-string 'O''Brian'

there is a function quotedstr which does this for you,
if you need such doing at runtime
(building sql-statements for example)

see also ansiquotedstr

meikl ;-)
0
 
LVL 1

Expert Comment

by:trex_fire
ID: 6938495
try this:
qSoumission.sql.add('message_accueil = ''' + fxValidString(mMessageDebut.text) + ''', ');

T-Rex
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6945375
? helloooooo gas, did you run out of fuel
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

827 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question