//add by czf 0518 //检测相关表中是否存在某字段 function IsExistColumn(sTableName :string ;sColumnName :string):Boolean; var sSql :string; iTableId :integer; begin sSql :='Select * from sysobjects where name='''+trim(sTableName)+''''; AdoQuery.sql.add(ssql); AdoQuery.open; if not AdoQuery.IsEmpty then begin iTableId :=AdoQuery.fieldbyname('id').AsInteger; sSql :='select * from syscolumns where id =' +IntToStr(iTableId)+' and name='''+trim(sColumnName)+''''; AdoQuery.sql.add(ssql); AdoQuery.open; if not AdoQuery.IsEmpty then begin Result :=true; end else begin Result :=false; end; end else begin Result :=false; end; end;
各位大侠:你们看 下面的函数有那些不妥? //检测相关表中是否存在某字段 function IsExistColumn(sTableName :string ;sColumnName :string):Boolean; var sSql :string; iTableId :integer; begin sSql :='Select * from sysobjects where name='''+trim(sTableName)+''''; OpenQuery(wagedata.query,sSql,false); if not wagedata.query.IsEmpty then begin iTableId :=wagedata.query.fieldbyname('id').AsInteger; sSql :='select * from syscolumns where id =' +IntToStr(iTableId)+' and name='''+trim(sColumnName)+''''; OpenQuery(wagedata.query,sSql,false); if not wagedata.query.IsEmpty then begin Result :=true; end else begin Result :=false; end; end else begin Result :=false; end; end;//在相关表中新增相应字段 function AddNewColumn(sTableName :string ;sColumnName :string; sColumnTypeStr:string):Boolean; var sSql :string; begin sSql :='alter table '+trim(sTableName)+' add '+trim(sColumnName)+' '+trim(sColumnTypeStr); if ExecQuery(wagedata.query,sSql) then begin Result :=true; end else begin Result :=false; end; end;
to zfmich(): 好像就是你给我的。 但是我用来用去就是不行?1、execquery这个过程不行。 2、openqueyr这个过程也不行;
Query1.Clear; Query1.SQL.Text := 'select * from table1'; Query1.Open; if Query1.FieldList.IndexOf('字段名') < 0 then ShowMessage('字段名' + 'no在');
function IsExistColumn(sTableName :string ;sColumnName :string):Boolean; begin Result := False; with wagedata.query do try Close; Text := 'SELECT * FROM Table1 WHERE <加一个条件让查询快一点>'; Open; Result := FieldList.IndexOf(sColumnName) >= 0; finally Free; end; end;
to zswang: 还是不行呢? 以前我都问过了: http://www.csdn.net/expert/topic/366/366791.shtm
我写了一个: 但搞来搞去就是不行 delphi6 +sql 2000procedure TYGForm.SpeedButton3Click(Sender: TObject); var s:string; begin s:= InputBox('新增年份', '请输入年份', ''); with dm1.ADOQuery1 do begin close; sql.Clear; sql.Add('select * from nianshtable where 序号=1'); open; if fieldlist.IndexOf(s)>= 0 then application.MessageBox('已经存在这个年份啦','小提示',0) else begin close; sql.Clear; sql.Add('alter table Nianshtable add '+s+' varchar(8)'); open; end; end; end;
to hymen1,zswang说的是对的啊!,可以这么写:function IsExistColumn(sTableName :string ;sColumnName :string):Boolean; begin Result := False; with wagedata.query do begin Close; Add('SELECT * FROM '+sTableName+' WHERE 1=2'); Open; Result := FieldList.IndexOf(sColumnName) >= 0; end; end; 你的例子可以这么写:procedure TYGForm.SpeedButton3Click(Sender: TObject); var s:string; begin s:= InputBox('新增年份', '请输入年份', ''); if trim(s)='' then exit; with dm1.ADOQuery1 do begin close; sql.Clear; sql.Add('select * from nianshtable where 1=2'); open; if fieldlist.IndexOf(s)>= 0 then application.MessageBox('已经存在这个年份啦','小提示',0) else begin close; sql.Clear; sql.Add('alter table Nianshtable add '+s+' varchar(8)'); execsql; //该处不能用open,因为你的该sql命令没返回任何值 end; end; end;
function IsExistColumn(sTableName :string ;sColumnName :string):Boolean;
var
sSql :string;
iTableId :integer;
begin
sSql :='Select * from sysobjects where name='''+trim(sTableName)+'''';
AdoQuery.sql.add(ssql);
AdoQuery.open;
if not AdoQuery.IsEmpty then
begin
iTableId :=AdoQuery.fieldbyname('id').AsInteger;
sSql :='select * from syscolumns where id =' +IntToStr(iTableId)+' and name='''+trim(sColumnName)+'''';
AdoQuery.sql.add(ssql);
AdoQuery.open;
if not AdoQuery.IsEmpty then
begin
Result :=true;
end
else
begin
Result :=false;
end;
end
else
begin
Result :=false;
end;
end;
下面的函数有那些不妥?
//检测相关表中是否存在某字段
function IsExistColumn(sTableName :string ;sColumnName :string):Boolean;
var
sSql :string;
iTableId :integer;
begin
sSql :='Select * from sysobjects where name='''+trim(sTableName)+'''';
OpenQuery(wagedata.query,sSql,false);
if not wagedata.query.IsEmpty then
begin
iTableId :=wagedata.query.fieldbyname('id').AsInteger;
sSql :='select * from syscolumns where id =' +IntToStr(iTableId)+' and name='''+trim(sColumnName)+'''';
OpenQuery(wagedata.query,sSql,false);
if not wagedata.query.IsEmpty then
begin
Result :=true;
end
else
begin
Result :=false;
end;
end
else
begin
Result :=false;
end;
end;//在相关表中新增相应字段
function AddNewColumn(sTableName :string ;sColumnName :string; sColumnTypeStr:string):Boolean;
var
sSql :string;
begin
sSql :='alter table '+trim(sTableName)+' add '+trim(sColumnName)+' '+trim(sColumnTypeStr);
if ExecQuery(wagedata.query,sSql) then
begin
Result :=true;
end
else
begin
Result :=false;
end;
end;
好像就是你给我的。
但是我用来用去就是不行?1、execquery这个过程不行。
2、openqueyr这个过程也不行;
Query1.SQL.Text := 'select * from table1';
Query1.Open;
if Query1.FieldList.IndexOf('字段名') < 0 then
ShowMessage('字段名' + 'no在');
begin
Result := False;
with wagedata.query do try
Close;
Text := 'SELECT * FROM Table1 WHERE <加一个条件让查询快一点>';
Open;
Result := FieldList.IndexOf(sColumnName) >= 0;
finally
Free;
end;
end;
还是不行呢?
以前我都问过了:
http://www.csdn.net/expert/topic/366/366791.shtm
但搞来搞去就是不行 delphi6 +sql 2000procedure TYGForm.SpeedButton3Click(Sender: TObject);
var
s:string;
begin
s:= InputBox('新增年份', '请输入年份', '');
with dm1.ADOQuery1 do
begin
close;
sql.Clear;
sql.Add('select * from nianshtable where 序号=1');
open;
if fieldlist.IndexOf(s)>= 0 then
application.MessageBox('已经存在这个年份啦','小提示',0)
else
begin
close;
sql.Clear;
sql.Add('alter table Nianshtable add '+s+' varchar(8)');
open;
end;
end;
end;
begin
Result := False;
with wagedata.query do
begin
Close;
Add('SELECT * FROM '+sTableName+' WHERE 1=2');
Open;
Result := FieldList.IndexOf(sColumnName) >= 0;
end;
end; 你的例子可以这么写:procedure TYGForm.SpeedButton3Click(Sender: TObject);
var
s:string;
begin
s:= InputBox('新增年份', '请输入年份', '');
if trim(s)='' then exit;
with dm1.ADOQuery1 do
begin
close;
sql.Clear;
sql.Add('select * from nianshtable where 1=2');
open;
if fieldlist.IndexOf(s)>= 0 then
application.MessageBox('已经存在这个年份啦','小提示',0)
else
begin
close;
sql.Clear;
sql.Add('alter table Nianshtable add '+s+' varchar(8)');
execsql; //该处不能用open,因为你的该sql命令没返回任何值
end;
end;
end;
你看看是啥!
是不是就可以了
数值可以作为字段名吗?
请修改为
sql.Add(Format('alter table Nianshtable add 年份%s varchar(8)', [s]));