[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2003-12-08
25
Medium Priority
?
719 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
  • 9
  • 8
  • 4
  • +1
23 Comments
 
LVL 27

Accepted Solution

by:
kretzschmar earned 120 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
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…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses
Course of the Month17 days, 15 hours left to enroll

831 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