How to create TDataSet search field at runtime?

Question

I am using TADODataSet (D7).
I do not create constant fields in design mode using a DataSet.

 dsItems.CommandText := 'select ID, ItemName, UserIDCreate from Items'; dsUsers.CommandText := 'select ID, LoginName from Users'; // lookup dataset 

If I double-click on dsItems and "Add all files", and then click "New Submission" and define the search field, everything works fine. The resulting dsItems should contain: ID, ItemName, UserIDCreate, LoginName_Lookup

How can I avoid doing all this during development and add a search box after / or earlier (? Not sure) that the DataSet is opening.

In other words: How can I emulate “Add all files” and then “New file” to add a search loaded at runtime?

---

Note: I get an exception when running John code from the IDE. An exception occurs when trying to open a DataSet: EOleException with message 'An unknown error has occured' .

function TCustomADODataSet.LocateRecord (ADODB.pas) in line if FieldCount = 1 then FLookupCursor.Find...

I accept the answer because the executed program is working.
It would be nice if someone could check the receipt (or not) of the Exception when running the form inside the IDE.

Answer 1

A dataset cannot be opened to add a search field at run time.

You will also need to add any other fields that you will need to access as permanent fields, otherwise they will not be available. The procedures below should work. However, I recommend that if you can, use queries and join your tables - this is much less coding and, in my opinion, much cleaner.

 procedure CreatePersistentFields(ADataset: TDataset); Var i: Integer; Begin ADataset.FieldDefs.Update; for I := 0 to ADataset.FieldDefs.Count - 1 do if ADataset.FindField(ADataset.FieldDefs[i].Name) = Nil then ADataset.FieldDefs.Items[i].CreateField(ADataset); End; Procedure CreateLookupField( ATable: TDataSet; AFieldName: String; ALookupDataset: TDataset; AKeyfields: String; ALookupKeyfields: String; ALookupResultField : String); Var I : Integer; NewField : TField; Begin with ATable do begin if FieldDefs.Updated = False then FieldDefs.Update; If FindField(AFieldName) = Nil then begin NewField := TStringField.Create(ATable); NewField.FieldName := AFieldName; NewField.KeyFields := AKeyFields; NewFIeld.LookupDataSet := ALookupDataset; NewField.LookupKeyFields := ALookupKeyFields; NewField.LookupResultField := ALookupResultField; NewField.FieldKind := fkLookup; NewField.Dataset := ATable; end; end; End; procedure TForm1.Button1Click(Sender: TObject); begin AdoDataset1.Close; CreatePersistentFields(TDataset(AdoDataset1)); CreateLookupField(TDataset(AdoDataset1), 'EmployeeNameLook', TDataset(EmployeeTable), 'EmployeeID', 'EmployeeID', 'EmployeeName'); end;