Check column exists in a table, if not add a new column

if table1.FindField(Cols[x])<>nil then
begin
  //add field as suggested from kretzschmar
end;

CheckFieldNames is discouraged, and it gives an exception where the field is present (not your way).

TableData.FieldDefs.Add(Cols[x], ftFloat,10, false); add a field to the table definition in memory, but until you don't to a table.createtable (only for paradox) nothig happens.

so answer to kretzschmar 's questions and we'll see

Hi ^^

I use table DBASE IV as my Database table, o yah.... and then how can I specify the decimal digits yah...??

uhmm... the connection is BDE ;)

thank alots ;)

ah, I forgot to tell you than if you use ttable.crete table, you loose the data in the table.
to add a field to a DBASE you can use dbi functions (dbidorestructure  look in http://info.borland.com/devsupport/bde/bdeapiex/dbidorestructure.html) or you can create a new table and pass the data from the old one.

uhmmm how if I want to add a column with SQL..??

btw, Delphized.. the example u give me... does it need that long program just to add a new column..??

fyi, I only want to add an empty column, so I dont need to retain the data from an old table

a sample

unit db_add_field_u;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, Grids, DBGrids, DB, DBTables;

type
  TForm1 = class(TForm)
    Table1: TTable;
    Query1: TQuery;
    DataSource1: TDataSource;
    DBGrid1: TDBGrid;
    Button1: TButton;
    procedure Button1Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.Button1Click(Sender: TObject);
var sl : TStringList;
begin
  sl := TstringList.Create;
  try
    //get fieldnames
    session.GetFieldNames('DBDEMOS','TEST.DBF',sl);
    // check if field exists
    if sl.IndexOf('N1') = -1 then
    begin
      //if not
      table1.Close; //no touch during adding
      //query1 holds:
      //alter table "test.dbf" add N1 NUMERIC(10,2)
      query1.ExecSQL;
      table1.Open;
    end;
  finally
    sl.Free;
  end;
end;

end.

meikl ;-)

if you only want to add a column on an empty table follow kretzschmar advice or use fieldefs.add (as you used) and then ttable.close, ttable.cretetable

>if you only want to add a column on an empty table follow kretzschmar advice

this should be also work on filled tables, delphized

ah! I didn't tried it because i gave it for sure! good to know!

hi,

i tried the deplhized's wayto add a new column in an NON-Empty table, but all of the data exists before I add the column will be deleted. So my table has become an empty table with a new column added....

how can I retain the data exists before I add the column ?

thank alot ;)

i'm ignored :-(

Heyy... Im not..... sorryy.... im not ignoring youu kretzschmar...

I already tried your way....

TableData.Close;
QueryData.Close;
QueryData.SQL.Clear;
QueryData.SQL.Add('ALTER TABLE ' + TableData.TableName + ' ADD ' + ShareID + '  
                        NUMERIC(10,4)');
QueryData.ExecSQL;

but I got this error....
 it says that the table is not exists.... im working on it.... =D wait a yahhhh

TableData.Active:= true;
               TableData.Close;
               if TableData.Exists = true then ShowMessage(TableData.TableName);

               QueryData.Close;
               QueryData.DatabaseName := DBDir;
               QueryData.SQL.Clear;
               QueryData.SQL.Add('ALTER TABLE '+ TableData.TableName+' ADD ' + ShareID  
                           + ' NUMERIC(10,4)');
               QueryData.ExecSQL;

when I run this.... the program will show me the message of the tablename... (That means that the table is exists right...? ); but later it will give an error message that the table is not exists.... why... oh whyy....?? =p

try this change

QueryData.SQL.Add('ALTER TABLE "'+ TableData.TableName+'" ADD ' + ShareID  
                           + ' NUMERIC(10,4)');
               

-> the tablename must enclosed within doublequotes
-> the tablename must have the suffix (dbf) also
-> ShareID must be a valid ColumnName

meikl ;-)

hummmm I fixed it al ready but it gives me another error... it said that te table is busy :'(

fyi =)

-TableData.TableName  is Shares2002.dbf -> so it has the .dbf already
- ShareID is OCBC for example -> it's simply a string

Thanks alott ^^

Hi againnnn hahahahahaha

ok it works just fineeeee ^^ I forget to close the table from the Database desktop =D thanks alott...

btw i just know that the table name must be inside double quote.... because this time I never use the double quote... just put the tablename... and it works :S

anyway thanks alotttt

glad you got it sorted ;-)

btw.... why i can't accept your answer..??

there should be a button titled "accept" or similar
directly rioght sided on each comment from an expert

just press the button of the comment,
which helps you most

meikl ;-)

alow againnnn hehhee another proble comes -_-;

1. uhmmm how can I specify that the value of the column is until 4 decimal digit
( ##.####) ? because although I specified Float(10,4) the decimal digit is only 1 (##.#)

2. When I want to add another column (for another ShareID) the data in the previous ShareID added is gone -_-; how can I fix this..? ;'(


Thank again =p

to 1.

the specifcation (10,4) has nothing to do, how the value is displayed,
it just says that the precission is limied to 4 digits.

if you want to display it as defined, just add a displayformat
for the underlying TField like #0.0000

to 2.
seems the data aren't posted correctly, or the os held it in the cache.
what connections type do u use (ado,bde,other)?

meikl ;-)

to1.
How can I specify the display format ..??

to2.
Im using BDE I guess =p (I'm using the table inside the BDE tab in delphi editor)

to1.

TFloatField(DatasetHere.FieldByName('FieldNameHere')).DisplayFormat := '#0.0000';

to2.
try to use the flushbuffers-method after each post

meikl ;-)