数据库更新表的问题

Question

procedure TForm1.Button1Click(Sender: TObject);
var
  TableListOld, TableListNew: TStringList;
  FieldListOld, FieldListNew: TStringList;
  I, J: Integer;
  t:tfieldtype;
  fdtype:string;   m, k: Integer;
   adoxTables:Tables;
   adoxTable: _Table;
   adoxColumns: Columns;
   adoxColumn: _Column;
   adoxcatalog:_catalog;
begin
  TableListOld := TStringList.Create;
  FieldListOld := TStringList.Create;
  TableListNew := TStringList.Create;
  FieldListNew := TStringList.Create;  ADOConnectionOld.GetTableNames(TableListOld);
  ADOConnectionNew.GetTableNames(TableListNew);  for I := 0 to TableListNew.Count - 1 do
  begin
    ADOConnectionNew.GetFieldNames(TableListNew[I], FieldListNew);
    if TableListOld.IndexOf(TableListNew[I]) <> -1 then
    begin
      //旧数据库中表TableListNew[I]存在
      ADOConnectionOld.GetFieldNames(TableListNew[I], FieldListOld);
      for J := 0 to FieldListNew.Count - 1 do
      begin
         if FieldListOld.IndexOf(FieldListNew[J]) <> -1 then
         begin
         // showmessage('存在该字段');          { 旧数据库表TableListNew[I]中 字段FieldListNew[J]存在 }
          end
        else
          begin
       // showmessage('该字段不存在要创建');
       // showmessage(FieldListNew[J]);
       // showmessage(tablelistnew[i]);
                //adotable1   连接新表
        adotable1.Active:=false;
        adotable1.TableName:=tablelistnew[i];
        adotable1.Open;   //判断字段类型
        t:=adotable1.FieldByName(FieldListNew[J]).DataType;         if t=ftWideString then
         begin
         fdtype:='varchar';
         showmessage('ftWideString');
         adoquery2.Close;
         adoquery2.SQL.Clear;
         adoquery2.SQL.Add('Alter table'+' ' +tablelistnew[i]+' '+'add'+' '+FieldListNew[J]+' '+fdtype+'(50)') ;
         adoquery2.ExecSQL;
         end;         if t=ftBoolean  then
         begin
         adoxCatalog:= CoCatalog.Create;
         adoxCatalog.Set_ActiveConnection(ADOConnectionold.ConnectionObject);
         adoxTables := adoxCatalog.Tables;
         k := adoxTables.Count;
         for m:=0 to k-1 do
           begin
           adoxTable := adoxTables.Item[m];
           if UpperCase(adoxTable.Name)=tablelistnew[i] then
            Break;//得到要修改的表
           END;
         adoxColumns := adoxTable.Columns;
         adoxColumn := coColumn.Create;
         adoxColumn.Name := FieldListNew[J];
         adoxColumn.Type_ := adboolean;
         adoxColumn.DefinedSize :=1;
         //adoxColumn.Attributes := AdColNullable;
       //adoxcolumn.Properties['Jet OLEDB:Allow Zero Length'].Value := false;
         adoxColumns.Append(adoxColumn,adboolean,1);
         {with adoxcolumn do
           begin
          Properties['Jet OLEDB:Allow Zero Length'].Value := true;
           end;}
       end;
         if t=ftdatetime  then
         begin         adoxCatalog:= CoCatalog.Create;
         adoxCatalog.Set_ActiveConnection(ADOConnectionold.ConnectionObject);
         adoxTables := adoxCatalog.Tables;
        k := adoxTables.Count;
         for m:=0 to k-1 do
           begin
           adoxTable := adoxTables.Item[m];
           if UpperCase(adoxTable.Name)=tablelistnew[i] then
            Break;//得到要修改的表
           END;
           showmessage(tablelistnew[i]);
           showmessage( adoxTable.Name+'ok');
         //adoxtable:=adoxtables.Item[i];
         adoxColumns := adoxTable.Columns;
         adoxColumn := coColumn.Create;
         adoxColumn.Name := FieldListNew[J];
         adoxColumn.Type_ := addate;
         adoxColumn.DefinedSize :=8;
         adoxColumn.Attributes := AdColNullable;
      //adoxcolumn.Properties['Jet OLEDB:Allow Zero Length'].Value := false;
         adoxColumns.Append(adoxColumn,addate,8);       end;
      { 旧数据库表TableListNew[I]中 字段FieldListNew[J]不存在，修改表TableListNew[I]的字段 }          end;
      end;
    end
else
    begin
      showmessage('该表不存在要创建');
      { 旧数据库中表TableListNew[I]不存在，创建表TableListNew[I] }  end;
  end;
  TableListOld.Free;
  TableListNew.Free;
  FieldListOld.Free;
  FieldListNew.Free;
end;

Answer 1

日期型和逻辑型字段乱创建，字符型不乱创建，就是新表：A，字段aa,当旧表A没有这个字段的时候，创建的时候给创建到别的表里去了。

Answer 2

procedure TForm1.Button1Click(Sender: TObject);
var
  TableListOld, TableListNew: TStringList;
  FieldListOld, FieldListNew: TStringList;
  I, J: Integer;
  t:tfieldtype;
  fdtype:string;   m, k: Integer;
   adoxTables: Tables;
   adoxTable: _Table;
   adoxColumns: Columns;
   adoxColumn: _Column;
   adoxcatalog:_catalog;
begin
  TableListOld := TStringList.Create;
  FieldListOld := TStringList.Create;
  TableListNew := TStringList.Create;
  FieldListNew := TStringList.Create;  ADOConnectionOld.GetTableNames(TableListOld);
  ADOConnectionNew.GetTableNames(TableListNew);  for I := 0 to TableListNew.Count - 1 do
  begin
    ADOConnectionNew.GetFieldNames(TableListNew[I], FieldListNew);
    if TableListOld.IndexOf(TableListNew[I]) <> -1 then
    begin
      //旧数据库中表TableListNew[I]存在
      ADOConnectionOld.GetFieldNames(TableListNew[I], FieldListOld);
      for J := 0 to FieldListNew.Count - 1 do
      begin
        if FieldListOld.IndexOf(FieldListNew[J]) <> -1 then
        begin
        // showmessage('存在该字段');          { 旧数据库表TableListNew[I]中 字段FieldListNew[J]存在 }
        end else
        begin
          adotable1.Active:=false;
          adotable1.TableName:=TableListNew[i];
          adotable1.Open;   //判断字段类型
          t:=adotable1.FieldByName(FieldListNew[J]).DataType;          if t=ftWideString then
          begin
            fdtype:='varchar';
            showmessage('ftWideString');
            adoquery2.Close;
            adoquery2.SQL.Clear;  //应该确定ADOQuery2是否连接为旧数据库 ?????????????
            adoquery2.SQL.Add('Alter table'+' ' +tablelistnew[i]+' '+'add'+' '+FieldListNew[J]+' '+fdtype+'(50)') ;            adoquery2.ExecSQL;
          end;          if t=ftBoolean  then
          begin
            adoxCatalog:= CoCatalog.Create;
            adoxCatalog.Set_ActiveConnection(ADOConnectionold.ConnectionObject);
            adoxTables := adoxCatalog.Tables;
            k := adoxTables.Count;
            for m:=0 to k-1 do
            begin
              adoxTable := adoxTables.Item[m];
              if UpperCase(adoxTable.Name)=tablelistnew[i] then
                Break;//得到要修改的表
            END;
            adoxColumns := adoxTable.Columns;
            adoxColumn := coColumn.Create;
            adoxColumn.Name := FieldListNew[J];
            adoxColumn.Type_ := adboolean;
            adoxColumn.DefinedSize :=1;
            //adoxColumn.Attributes := AdColNullable;
            //adoxcolumn.Properties['Jet OLEDB:Allow Zero Length'].Value := false;
            adoxColumns.Append(adoxColumn,adboolean,1);
            {with adoxcolumn do
            begin
            Properties['Jet OLEDB:Allow Zero Length'].Value := true;
            end;}
          end;
          if t=ftdatetime  then
          begin            adoxCatalog:= CoCatalog.Create;
            adoxCatalog.Set_ActiveConnection(ADOConnectionold.ConnectionObject);
            adoxTables := adoxCatalog.Tables;
            k := adoxTables.Count;
            for m:=0 to k-1 do
            begin
              adoxTable := adoxTables.Item[m];
              if UpperCase(adoxTable.Name)=UpperCase(tablelistnew[i]) then////
              //------------------- 可能是这儿的问题,小小安也可以用单步调试
              //比较的时候if等号两边的都要UpperCase
                Break;//得到要修改的表
            END;
            showmessage(tablelistnew[i]);
            showmessage( adoxTable.Name+'ok');
            //adoxtable:=adoxtables.Item[i];
            adoxColumns := adoxTable.Columns;
            adoxColumn := coColumn.Create;
            adoxColumn.Name := FieldListNew[J];
            adoxColumn.Type_ := addate;
            adoxColumn.DefinedSize :=8;
            adoxColumn.Attributes := AdColNullable;
            //adoxcolumn.Properties['Jet OLEDB:Allow Zero Length'].Value := false;
            adoxColumns.Append(adoxColumn,addate,8);          end;
          { 旧数据库表TableListNew[I]中 字段FieldListNew[J]不存在，修改表TableListNew[I]的字段 }        end;
      end;
    end else
    begin
      showmessage('该表不存在要创建');
      { 旧数据库中表TableListNew[I]不存在，创建表TableListNew[I] }
      //下面就是一个简单的例子, 你扩展一下就能使用了, 现在创建了一个空表, 没有
       //太多 时间给你写一个完整的代码了
      with AdoQuery2 do
      begin
        Close;
        SQL.Clear;
        SQL.Add('Create Table aaa');
        ExecSQL;
        Close;
      end;    end;
  end;
  TableListOld.Free;
  TableListNew.Free;
  FieldListOld.Free;
  FieldListNew.Free;end;