How to add a field programatically to a TAdoTable in Delphi

Question

In my Delphi 2009 application I need to check if a field exists and if it doesn't add it during application execution.

I have figured out the test for the field, but cannot get a field to add. I tried this

var
  fld : TStringField;
begin
  if not ADOConnection1.Connected then
    ADOConnection1.Open;
  fld := TStringField.Create(tbl);
  fld.FieldName := 'test';
  tbl.Fields.Add(fld);
end;

But it doesn't work.

Answer 1

try this

 fld:= TStringField.Create(tbl);
    fld.FieldName := 'test';
    fld.DisplayLabel := 'test';
    fld.name := 'test';
    fld.DataSet := tbl;
    fld.Size := 30;
    tbl.Fields.Add(fld);

Bye.

Answer 2

If you're trying to get the functionality of the "add all fields" or "add fields" menu in the table designer it's easy enough to start with that code in the Embarcadero source and clean it up so it's usable outside the designer. The method to start with is this one in DSDesign.pas: function TFieldsEditor.DoAddFields(All: Boolean): TField; My code for adding all missing fields is this:

procedure AddAllFields(DataSet: TDataset);
var
   FieldsList: TStringList;
   FieldName: WideString;
   Field: TField;
   WasActive: boolean;
   FieldDef: TFieldDef;
   i: Integer;
begin
   WasActive := DataSet.Active;
   if WasActive then
      DataSet.Active := False;
   try
      FieldsList := TStringList.Create;
      try
         DataSet.FieldDefs.Update;

         // make a list of all the field names that aren't already on the DataSet
         for i := 0 to DataSet.FieldDefList.Count - 1 do
            with DataSet.FieldDefList[i] do
               if (FieldClass <> nil) and not(faHiddenCol in Attributes) then
               begin
                  FieldName := DataSet.FieldDefList.Strings[i];
                  Field := DataSet.FindField(FieldName);
                  if (Field = nil) or (Field.Owner <> DataSet.Owner) then
                     FieldsList.Add(FieldName);
               end;

         // add those fields to the dataset
         for i := 0 to FieldsList.Count - 1 do
         begin
            FieldDef := DataSet.FieldDefList.FieldByName(FieldName);
            Field := FieldDef.CreateField(DataSet.Owner, nil, FieldName, False);
            try
               Field.name := FieldName + IntToStr(random(MaxInt)); // make the name unique
            except
               Field.Free;
               raise ;
            end;
         end;
      finally
         FieldsList.Free;
      end;
   finally
      if WasActive then
         DataSet.Active := true;
   end;
end;

Answer 3

If the table is part of a SQL Databse, once you detect that the field is missing you can add the field via SQL, then re-open the table.

cmd := tAdoCommand.create;
try
  cmd.Connection := AdoConnection1;
  cmd.CommandText := 'ALTER TABLE table ADD TEST nvarchar(30)';
  cmd.Execute;
finally
  cmd.Free;
end; 

Answer 4

Try set Field.DataSet:= Table;

Answer 5

You can create new field using SQL. In my opinion is the best chance. If you don't want use SQL you can see this article for create new fields with ADO using ADOX (originaly in Spanish but at right you can translate the page) "Create fields in a table (Access) Code, using ADOX" original/translated

If must know the field in a table you can access the TADOTable and the method GetFiledDef. Additionaly the method FindField return nil if the field dot not exist.

Additionally some Databases/SGBD's have system tables that you can access with an SQL for know Existent Tables,Fields,Indexes,...

Excuse for my bad english.

Regards.

Answer 6

Greate function works well!! just the part

     for i := 0 to FieldsList.Count - 1 do
     begin
        FieldDef := DataSet.FieldDefList.FieldByName(FieldName);
        Field := FieldDef.CreateField(DataSet.Owner, nil, FieldName, False);
        try
           Field.name := FieldName + IntToStr(random(MaxInt)); // make the name unique
        except
           Field.Free;
           raise ;
        end;
     end;

replace FieldName by FieldsList[i]