Solved

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

Posted on 2003-12-08
25
706 Views
Last Modified: 2011-09-20
Hi all,

i want to ask, is anybody know how can I check whether a clomun is exists in a table or not, if not then add a new table
thank you ^^

ps : I use this way, but id doesn't work..... the column wont be added :'(

for x:= 1 to 10 do
 begin
    try TableData.Fields.CheckFieldNames(Cols[x]);
    except on EDatabaseError do begin TableData.FieldDefs.Add(Cols[x], ftFloat,  
            10, false);
    end;

end;
//ps : Cols[] is an array variable of strings

thanks alotttt ^^
0
Comment
Question by:allie109
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 8
  • 4
  • +1
25 Comments
 
LVL 27

Accepted Solution

by:
kretzschmar earned 30 total points
ID: 9896500
for adding a columnn use an
Alter table ...-sql-statement

what database do u use?
what connection-type do u use (bde,ado,other)?
0
 
LVL 5

Expert Comment

by:delphized
ID: 9896687
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
0
 

Author Comment

by:allie109
ID: 9901467
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 ;)
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 5

Expert Comment

by:delphized
ID: 9902286
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.
0
 

Author Comment

by:allie109
ID: 9902400
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
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 9902438
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 ;-)
0
 
LVL 5

Expert Comment

by:delphized
ID: 9907058
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
0
 
LVL 27

Expert Comment

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

this should be also work on filled tables, delphized
0
 
LVL 5

Expert Comment

by:delphized
ID: 9907996
ah! I didn't tried it because i gave it for sure! good to know!
0
 

Expert Comment

by:allie910
ID: 9910012
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 ;)
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 9910073
i'm ignored :-(
0
 

Expert Comment

by:allie910
ID: 9910142
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
0
 

Expert Comment

by:allie910
ID: 9910174
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
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 9910275
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 ;-)
0
 

Expert Comment

by:allie910
ID: 9910396
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 ^^
0
 

Expert Comment

by:allie910
ID: 9910409
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
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 9910419
glad you got it sorted ;-)
0
 

Expert Comment

by:allie910
ID: 9910460
btw.... why i can't accept your answer..??

0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 9910489
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 ;-)
0
 

Expert Comment

by:allie910
ID: 9910608
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
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 9910668
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 ;-)
0
 

Expert Comment

by:allie910
ID: 9910926

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)
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 9911024
to1.

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

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

meikl ;-)
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

696 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