allie109
asked on
Check column exists in a table, if not add a new column
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.CheckFiel dNames(Col s[x]);
except on EDatabaseError do begin TableData.FieldDefs.Add(Co ls[x], ftFloat,
10, false);
end;
end;
//ps : Cols[] is an array variable of strings
thanks alotttt ^^
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.CheckFiel
except on EDatabaseError do begin TableData.FieldDefs.Add(Co
10, false);
end;
end;
//ps : Cols[] is an array variable of strings
thanks alotttt ^^
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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 ;)
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.
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.
ASKER
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
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('DBD EMOS','TES T.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 ;-)
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
var sl : TStringList;
begin
sl := TstringList.Create;
try
//get fieldnames
session.GetFieldNames('DBD
// 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
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 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
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.Tabl eName);
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
TableData.Close;
if TableData.Exists = true then ShowMessage(TableData.Tabl
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 ;-)
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 ^^
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
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 ;-)
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
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 ;-)
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.Fi eldByName( 'FieldName Here')).Di splayForma t := '#0.0000';
to2.
try to use the flushbuffers-method after each post
meikl ;-)
TFloatField(DatasetHere.Fi
to2.
try to use the flushbuffers-method after each post
meikl ;-)
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(Co
so answer to kretzschmar 's questions and we'll see