1

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.

Ken White
  • 123,280
  • 14
  • 225
  • 444
Sharpie
  • 373
  • 2
  • 15
  • 34
  • You have to accept the multiple field names and values, and use `TDataSet.Locate`. How you design the interface to do so is up to you and your specific requirements. – Ken White Mar 17 '15 at 12:55
  • I have done the following code but get error message..'variant or safe array index out of bounds'. – Sharpie Mar 17 '15 at 13:46
  • You can't do it that way. You've said there are two columns ("Customer_Code" and "Name"), and so the array has to have two elements (index 0 = Customer_Code and Index 1 = Name). You're only putting one element in the array. – Ken White Mar 17 '15 at 13:47
  • Sorry to be a pain, could you give example. This is what I have so far... **Locate('customer_code;name',(editSearch.Text),[loPartialKey,loCaseInsensitive]);** – Sharpie Mar 17 '15 at 14:23
  • You can use the TcxFiltercontrol or TcxDbFilterControl – Ravaut123 Mar 17 '15 at 16:14

2 Answers2

0

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;
Ken White
  • 123,280
  • 14
  • 225
  • 444
0

I would use a query for the datasource and in the onclick event of the button, reload the query with a WHERE clause along the lines of

Query1.SQL.Add('WHERE Name LIKE :P1 OR Postcode LIKE :P2 OR Town LIKE :P3');

and add the parameters

Query1.SQL.Parameters.ParamByName('P1').Value := '%' + Edit1.Text + '%';
Query1.SQL.Parameters.ParamByName('P2').Value := '%' + Edit1.Text + '%';
Query1.SQL.Parameters.ParamByName('P3').Value := '%' + Edit1.Text + '%';

using '%' to allow searching anywhere in the string as an option.

Query1.Open;

I've used this technique many times.

penarthur66
  • 311
  • 2
  • 8