Solved

Query with '

Posted on 2002-04-10
5
245 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

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…
Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

867 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now