Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Adding Fields to Existing table

Posted on 1998-10-01
4
Medium Priority
?
528 Views
Last Modified: 2010-04-06
Is it possible to add new fields to an existing table at runtime. I have looked at help files regarding fielddefs.add, but it seems to indicate this is for use only on new table creation.

I have an existing database application, but I want to be able to add new functionality requiring new fields, but I don't want to erase existing data.
0
Comment
Question by:JohnE
  • 2
4 Comments
 
LVL 4

Accepted Solution

by:
BoRiS earned 200 total points
ID: 1341396
JohnE

her is one way to do just that, this addes a stringfiled to the dataset...

procedure TForm1.AddFieldButClick(Sender: TObject);

var

  NewField: TStringField;

begin

  Table1.Close;

  NewField := TStringField.Create(Self);

  NewField.FieldName := 'NEW_FIELD';

  NewField.Name := Table1.Name + NewField.FieldName;

  NewField.Index := Table1.FieldCount;

  NewField.DataSet := Table1;

  Table1.FieldDefs.UpDate;

  Table1.Open;

end;

Later
BoRiS
0
 

Expert Comment

by:dejohn99
ID: 1341397
BoRiS' answer is incomplete - it will add a new field to the internal representation of the dataset, however it will not actually add it to the tables!

The complete algorithm should be

1. Rename the existing table to something innocuous like "WORKING"

2. Create a new TTable object with the appropriate paths, names, etc.

3. Copy the field structure of the working table structure to the field structure of the new TTable object

4. Call TTable.Create  and TTable.Open with the new TTable object

5. Either use BatchMove or a "While not WorkingTable.EOF" loop to read records from the working table and insert them into the new table.  Ensure that your field transfer method uses fields by name

6. Close both the new table and the working table

7. Make modifications to the old TTable object as noted by BoRiS

8. Re-open the original Table object (which now sees the new field)

Procedure btnAddFieldClick (Sender);
{
    Table1 - the table we are changing
    WorkingTable - the internal representation of the table we are using to work with
    NewTable - The output of the final (revised) table structure
}
var
    WorkingTable, NewTable: TTable;
    i: Integer;
begin
    Screen.Cursor := crHourGlass;
    Table1.Close;
    WorkingTable := TTable.Create (Self);
    NewTable := TTable.Create (Self);
    try
        with WorkingTable do begin
            DatabaseName := Table1.DatabaseName;
            TableName := Table1.TableName;
            Open;
            RenameTable ('WORKING');
        end;

        with NewTable do begin
            DatabaseName := Table1.DatabaseName;
            TableName := Table1.TableName;
            for i := 0 to  WorkingTable.FieldCount-1 do begin
                TFieldDef.Create (FieldDefs,
          WorkingTablel.Fields[i].FieldName,
          WorkingTable.Fields[i].DataType,
          WorkingTable.Fields[i].Size,
          WorkingTable.Fields[i].Required,
                    i);
            end; {for}
            TFieldDef.Create (FieldDefs,
                'NewField',
                dtString,
                100,
                false,
                FieldCount);

            CreateTable;
            WorkingTable.First;
            while not WorkingTable.EOF do begin
                Insert;
                for i := 0 to  WorkingTable.FieldCount-1 do begin
                    FieldByName (WorkingTable.Fields[i]).FieldName) .Value := WorkingTable.Fields[i].Value;
                end;
                Post;
                WorkingTable.Next;
            end;
            Close;
        end; {with}
        WorkingTable.Close;

    finally
        WorkingTable.Destroy;
        NewTable.Destroy;
        Table1.Open;
        Screen.Cursor := crArrow;
    end;
end;
0
 

Expert Comment

by:dejohn99
ID: 1341398
oops!  I forgot to add the new field to the existing TTable object.

Use BoRiS's code for that final step.
0
 

Author Comment

by:JohnE
ID: 1341399
Thanks, we dejohn99's comments, it turned out great!
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
This is an update to some code that someone else posted on Experts Exchange. It is an alternate approach, I think a little easier to use, & makes sure that things like the Task Bar will update.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question