Lazarus

Programming => Databases => Topic started by: daveinhull on February 27, 2019, 02:31:04 pm

Title: Lookup question
Post by: daveinhull on February 27, 2019, 02:31:04 pm
Hi,

I'm trying to use the
Code: Pascal  [Select]
  1.  function Lookup(const KeyFields: string; const KeyValues: Variant; const ResultFields: string): Variant;
function with 2 fields. According to the help file:
Quote
KeyFields can be a single field name or a semicolon-separated list of fields.

KeyValue can be a variant or a variant array and the number of items must match the number of fields specified in KeyFields.

My KeyField is already 2 DB fields separated by a ';', but I just can't work out how to pass a variant array of two values for the KeyValue parameter (in ?

I tried
Code: Pascal  [Select]
  1.   with TRxDBLookupCombo(Sender) do
  2.    if ((Text <> '') and (LookupSource.DataSet.lookup(LookupDisplay,[Text,Text],'ID') = Null)) then

Could someone point me in the right direction.

Thanks
Title: Re: Lookup question
Post by: daveinhull on February 27, 2019, 02:37:19 pm
HI,

Ok, I found it in the help file for the Locate funciton, all sorted.

Thanks
Title: Re: Lookup question
Post by: wp on February 27, 2019, 02:57:17 pm
In "Locate" you have the same problem: How to assign an array to a variant? Use VarArrayOf from unit variants. Assuming you have a DB with fields "Name" and "FirstName" and you want to find a record of a person named John Smith you would call:
Code: Pascal  [Select]
  1.   Dataset.Locate('Name;FirstName', VarArrayOf(['Smith', 'John']), [loCaseInsensitive]);
Title: Re: Lookup question
Post by: madref on March 04, 2019, 09:31:54 pm
I wrote this function once for databases
Code: Pascal  [Select]
  1. unction MLookup(Veld, Tabel, Voorwaarde: string): variant;
  2. var mySql: TSQLQuery;
  3. begin
  4.   mySQl := TSQLQuery.Create(nil);
  5.   mySQl.DataBase := Form_RefereeMain.Connect_RefereeDB;
  6.   try
  7.     mySql.SQL.Text := Format('SELECT %s FROM %s WHERE %s', [veld, tabel, voorwaarde]);
  8.     mySql.Open;
  9. //    Result := mySQL.Fields[0].AsVariant;
  10.     Result := MySQL.Fields[0].Value;
  11.   finally
  12.     MySql.Free;
  13.   end;
  14. end;     // MLookup
  15.  
Title: Re: Lookup question
Post by: Soner on March 04, 2019, 10:03:23 pm
I wrote this function once for databases
Code: Pascal  [Select]
  1. unction MLookup(Veld, Tabel, Voorwaarde: string): variant;
  2. var mySql: TSQLQuery;
  3. begin
  4.   mySQl := TSQLQuery.Create(nil);
  5.   mySQl.DataBase := Form_RefereeMain.Connect_RefereeDB;
  6.   try
  7.     mySql.SQL.Text := Format('SELECT %s FROM %s WHERE %s', [veld, tabel, voorwaarde]);
  8.     mySql.Open;
  9. //    Result := mySQL.Fields[0].AsVariant;
  10.     Result := MySQL.Fields[0].Value;
  11.   finally
  12.     MySql.Free;
  13.   end;
  14. end;     // MLookup
  15.  

This is not lookup.
Lookup searchs in existing dataset(=resultset), you are sending new query to database.
Title: Re: Lookup question
Post by: madref on March 05, 2019, 01:04:07 pm
This is the equivalent from the DLookUp in MS Access