• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 161
  • Last Modified:

Adding new FIELD to database

Hi all.

Is it possible to add a new FIELD to a Paradox database?  I have a table which looks like this:

Name: String
ID:   Integer
Word: String

Now, I want to add another field to this table at run-time.  It has to be an integer field. So we would now have

Name: String
ID:   Integer
Word: String
Ref:  Integer

Any ideas?  The reason it has to be done like this is because I need to add a new field for each user.  There could be as few users as 20 and as many as 500.

Stuart.
0
Stuart_Johnson
Asked:
Stuart_Johnson
  • 3
  • 2
1 Solution
 
kjtengCommented:
You have to create a new table and use tableN.fieldDefs.add method to add new field definition. After that copy all you existing data to the new table and rename it. (there is an example in delphi help). It is easier to use SQL to create new table if you are familier with SQL.

BTW I wonder why you need to add one field for each user. Something is not wrong with your database design. It appears to me that your database is not normalised. Try to normalise it and you can avoid adding indefinite number of field at runtime.  

0
 
Stuart_JohnsonAuthor Commented:
Thanks for the suggestion.  Just so you know, the reason we want to add a new field for each person is this.  We have a class of 20 students (this could vary as I mentioned before, upto at least 500).  We have a list of over 8700 words which we need to teach to these students.  We need to assign each student one record for each word with a status on that word.

For example.

WORD   STUDENT1   STUDENT2
------|---------|----------|--- >
EGG   | 1       | 3  
BIN   | 0       | 2
GREEN | 2       | 0

Etc.  Now, can you see why we want to do it this way?  The other way would be too slow and difficult.

Can you give me info on how to do that with SQL?

Cheers!


Stu.
0
 
kjtengCommented:
Create an event to add field: something like this

procedure TForm1.Button1Click(Sender: TObject);
begin
{What the following code does is actually same as this SQL statement:  alter table "my.db" add newItem integer}
  with query1 do begin
    close;
    sql.clear;
    sql.add( 'alter table ''cat.db'' add newItem integer');
    execSql
  end;
end;

That simple! That's why I love Delphi :-)



0
 
kjtengCommented:
if the Ref is not more than 255, you may then do this:

1. create two string fields, namely s1_255 and str256_511
2. intilised the value of both field to '0000...0' ie 255 times of zero
3. if ref value of student with number 1 to 255 change, just assign the new value to  1st character of s1_255, something like this
   a:= table1.fieldbyname( 's1_255').asString;
   a[n]:= 'a'   { n = 1..255}
4. similarly for student number higher than 255:
   b:= table1.fieldbyname( 's256_511').asString;
   b[n-255]:= 'b' { n = 256..511}
  etc
5  to show the ref for student n, showMessage( b[n-255])
Will this work for you?

Bye.
Will come back 8 hours later.
0
 
Stuart_JohnsonAuthor Commented:
Excellent!! Wonderful!  Thanks for that! I used the SQL method.  Neater and quicker.

Stu.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now