Solved

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

Posted on 2003-12-08
25
697 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 5

Expert Comment

by:delphized
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
>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
Comment Utility
ah! I didn't tried it because i gave it for sure! good to know!
0
 

Expert Comment

by:allie910
Comment Utility
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
Comment Utility
i'm ignored :-(
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Expert Comment

by:allie910
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
glad you got it sorted ;-)
0
 

Expert Comment

by:allie910
Comment Utility
btw.... why i can't accept your answer..??

0
 
LVL 27

Expert Comment

by:kretzschmar
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility

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
Comment Utility
to1.

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

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

meikl ;-)
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

763 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now