How to create a TDataSet Lookup Field at runtime?

Question

I'm using TADODataSet (D7).
I do not create persistent fields in design-mode with my 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 fileds" and then click "New filed" and define my lookup Field, everything is working fine. The resulting dsItems should contain: ID, ItemName, UserIDCreate, LoginName_Lookup

How can I avoid doing all this in design-time and add a Lookup Field after/or before (? not sure) the DataSet is opened.

In other words: How do I emulate "Add all fileds" and then "New filed" to add lookup filed in run-time?

---

NOTE: I get an Exception when running John's code from the IDE. The Exception occurs when trying to open the DataSet: EOleException with message 'An unknown error has occured'.

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

I'm accepting the answer becouse the complied program is working.
It would be nice if someone could verify getting (or not) an Exception when runing form inside the IDE.

Stines

Answer 1

The dataset cannot be opened to add a lookup field at runtime.

You'll also need to add whatever other fields you'll need to access as persistent fields as well, otherwise, they will be inaccessible. The procedures below should work. However, I recommend that if you can, use queries and join your tables - it's a lot less coding, and much cleaner in my opinion.

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;

Urceolate

Do you mean I first need to create "whatever other fields you'll need to access as persistent fields" in design-mode to run this code? – Stines

I get EOleException with message 'An unknown error has occurred when trying to open the DataSet. – Stines

You can add all persistent fields at runtime. Which part is throwing the exception and which line? – Urceolate

it seems to throw an exception only when I run the program from the IDE in AdoDataset1.Open;. the compiled program works fine. Do you have any idea why is that? – Stines

It shouldn't make a difference. You'll have to set some breakpoints and step through your code to see exactly what is going on... – Urceolate

The exception occurs in function TCustomADODataSet.LocateRecord (ADODB.pas) in line if FieldCount = 1 then FLookupCursor.Find.... anyway, I'm accepting the answer becouse it's working in the bottom line. – Stines

Regarding you recommendation: can I edit/insert on "queries and join your tables" DataSet? if yes I think that the main problem is when you append a new record on such DataSet you need to Requry it to reflect the lookup fields. no? – Stines

You are correct, you would need to open/close the query to reflect changes.. that is the one downside. You can write a refresh procedure, however, and as a parameter use a record identifier of some sort, and locate the updated record after the close/open. – Urceolate