Search database table by record fields and display record on cxgrid

Question

What is the best way to allow a user to search for a record in a database table by typing text into a tedit box clicking a button then the results will then display onto a tcxgrid.

I have a tadquery/datasource that is looking at a table which contains various fields. It would be nice if the user was able to search for more than one field in the table.

Thanks.

Answer 1

You can use TDataSet.Locate for this, passing a semicolon delimited list of field names and an array of constant field values to match. Typically, this is easy:

DataSet.Locate('Field1;Field2', ['Value1', 'Value2'], [loPartialKey]);

However, as you don't know ahead of time how many columns, you'll need to handle the array differently, using VarArrayCreate and setting each array value separately.

This example takes the list of fields from Edit1 (separated by semicolon), the list of values to match from Edit2 (again, separated by semicolons, with string values surrounded by ' characters so they're properly included in the array). It splits the content of Edit1 into an array to find out how many elements, allocates a variant array of the proper size, populates it, and then passes both the field list and the array of values to TDataSet.Locate. It was tested with Edit1.Text := 'Customer_No;Name'; and Edit2.Text := '1;''Smith''';.

procedure TForm5.Button1Click(Sender: TObject);
var
  Temp: string;
  Fields: TArray<string>;
  Vals: TArray<string>;
  FieldValues: Variant;
  i: Integer;
begin
  // Grab a copy so we can split it into separate values
  Temp := Edit1.Text;
  Fields := Temp.Split([';']);

  // Create the array of variants to hold the field values    
  FieldValues := VarArrayCreate([0, High(Fields)], VarVariant);

  // Temporary copy to allow splitting into individual values      
  Temp := Edit2.Text;
  Vals := Temp.Split([';']);
  for i := 0 to High(Fields) do
    FieldValues[i] := Vals[i];

  // Use original field list from Edit1 for the Locate operation
  DataSet1.Locate(Edit1.Text, FieldValues, [loCaseInsensitive]);
end;

For versions of Delphi prior to the inclusion of TStringHelper (such as the XE2 you're using, just add Types and StrUtils to your uses clause and use SplitString and TStringDynArray instead:

procedure TForm5.Button1Click(Sender: TObject);
var
  Temp: string;
  Fields: TStringDynArray;
  Vals: TStringDynArray;
  FieldValues: Variant;
  i: Integer;

begin
  Temp := Edit1.Text;
  Fields := SplitString(Temp, ';');

  FieldValues := VarArrayCreate([0, Length(Fields)], VarVariant);
  Temp := Edit2.Text;
  Vals := SplitString(Temp, ';');
  for i := 0 to High(Fields) do
    FieldValues[i] := Vals[i];

  DataSet1.Locate(Temp, FieldValues, [loCaseInsensitive]);
end;