Solved

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

Posted on 2003-12-08
25
701 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
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
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

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…
In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

803 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