Solved

Query with '

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

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…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

746 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

13 Experts available now in Live!

Get 1:1 Help Now