?
Solved

Query with '

Posted on 2002-04-10
5
Medium Priority
?
260 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 600 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
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…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

752 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