Delphi ado Query syntax

[kyriakos70]

Hello,
I have an ado query with the IN operator, how can I call the query list of the operator? eg.
Select * From routes Where id Like id1) And num Like num1) Or num In ('1','2')

these are two parameters and the list I call the parameters like :
case combobox.itemindex of
0:
adoquery.parameters[0].value := edit1.text
1:
adoquery.parameters[1].value := edit1.text
2:
"here how can I call the In operator list? it has no parameter"

thank you
Kyriakos

 

[DjangMan]

In your query you have:

Or num In ('1','2')

There are no parameters listed there so your query only has two parameters: #0 - id1 and #1 - num1.

Can you describe what you need to accomplish in a little more detail.

 

[kyriakos70]

Hi,
What can I achieve is that I want to put a parameter after the In operator eg IN num1) I have done it but I couldn't pass a list in the parameter eg.
0:
adoquery.parameter[0].value :=edit1.text
1:
adoquery.parameters[1].value := edit1.text
2:
adoquery.parameter[2].value := ('1,2'){in parameter}
I tried this how can I pass a list to take 2-3 values as a list, is it possible or I have to put the ascii code for ,
('1'+#code+'2')?

kyriakos

 

[DjangMan]

I'm not sure. I've run into this problem in the past and ended up re-working my queries and generating the SQL command at run-time vs. using a parameterized query.

 

[whosrdaddy]

I'm doing this in an old D5 project.
Like Djangman said, you need to build the query ad runtime.

the parameter after IN will only work if you have one string.

I have something like this:

Qry.SQL.Clear;
    Qry.SQL.Add('SELECT aa.SerieNummer, aa.AankoopNummer ');
    Qry.SQL.Add(' FROM aankooplijst_artikelen aa         ');
    QuotedSn := QuotedStrings(Serienummers, ',');
    Qry.SQL.Add(Format(' WHERE (ArtikelNr = :ArtikelNummer) AND (SerieNummer IN (%s))', [QuotedSn]));
    Qry.Parameters.ParseSQL(Qry.SQL.Text, True);
    Qry.Parameters.ParamByName('ArtikelNummer').DataType := ftInteger;
    Qry.Parameters.ParamByName('ArtikelNummer').Value := ArtikelNummer;
    Qry.Open;
    while (not Qry.Eof) and (Aantal > 0) do

/Daddy

-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!

 

[whosrdaddy]

here is the quotedstrings function I wrote:

function QuotedStrings(Str : String; Separator : String) : String;

var Ps      : Integer;
    Sn      : String;

begin
 //change string "a,b,c" into "'a', 'b', 'c'"
 Str := Trim(Str);
 if Str <> '' then
  begin
   Result := '';
   while Str <> '' do
    begin
     Sn := '';
     Ps := Pos(Separator, Str);
     if Ps > 0 then
      begin
       Sn := Trim(Copy(Str, 1, Ps - 1));
       Str := Copy(Str, Ps + 1, MaxInt);
      end
     else
      begin
       Sn := Str;
       Str := '';
      end;
     if Sn <> '' then
      begin
       if Result <> '' then
        Result := Result + ', ';
       Result := Result + QuotedStr(Sn);
      end;
    end;
  end;
end;

/Daddy

-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!