Solved

Query with '

Posted on 2002-04-10
5
257 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …

695 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