关于ADOQuery数据追加问题。。
我要做的事是:将一个Access表中的所有数据更新到SQL表中(两个表中字段一样,以ID作为条件),若SQL表中存在相应的ID则更新,不存在则插入。
我用两个ADOQuery来连接数据库查询,代码大致如下:
ADOQuery_2->SQL->ADD("SELECT * FROM Access表;"); ADOQuery_2->Open(); while(!ADOQuery_2->Eof) { ADOQuery_1->SQL->ADD("SELECT COUNT(*) FROM SQL表 WHERE ID = " + ADOQuery_2->FieldList->FieldByName("ID")->AsString); ADOQuery_1->Open(); if(ADOQuery_1->RecordCount > 0) { Update SQL表... } else Insert Into SQL表... ADOQuery_2->Next(); }
ADOQuery_2->SQL->ADD("SELECT * FROM Access表;"); ADOQuery_2->Open(); while(!ADOQuery_2->Eof) { ADOQuery_1->SQL->ADD("SELECT * FROM SQL表 WHERE ID = " + ADOQuery_2->FieldList->FieldByName("ID")->AsString); ADOQuery_1->Open(); if(ADOQuery_1->RecordCount > 0) { ADOQuery_1->Edit(); ADOQuery_1->FieldByName("a")->Value = ADOQuery_2->FieldByName("a")->Value; ADOQuery_1->FieldByName("b")->Value = ADOQuery_2->FieldByName("b")->Value; ADOQuery_1->FieldByName("c")->Value = ADOQuery_2->FieldByName("c")->Value; .... ADOQuery_1->Post(); } else { ADOQuery_1->Append(); ADOQuery_1->FieldByName("a")->Value = ADOQuery_2->FieldByName("a")->Value; ADOQuery_1->FieldByName("b")->Value = ADOQuery_2->FieldByName("b")->Value; ADOQuery_1->FieldByName("c")->Value = ADOQuery_2->FieldByName("c")->Value; .... ADOQuery_1->Post(); } ADOQuery_2->Next(); }
[解决办法]
TADOQuery *Query=new TADOQuery(this);
Query->Connection=ADO_DBM->ADOConnection1;
Query->SQL->Add("select * from "+TableName);
Query->Open();
int nFieldCount=Query->FieldCount;
while(!Query->Eof)
{
TADOQuery *TQuery=new TADOQuery(this);
TQuery->Connection=ADO_DBM->ADOConnection1;
TQuery->SQL->Add("select top 0 * from "+TableName);
TQuery->Open();
TQuery->Append();
for(int i=0;i<nFieldCount;i++)
{
AnsiString sField=Query->Fields->Fields[i]->DisplayName;
if(sField!="RecID")//主键
{
if(Query->Fields->Fields[i]->DataType==ftBoolean)
TQuery->FieldByName(sField)->AsBoolean=Query->FieldByName(sField)->AsBoolean;
else
TQuery->FieldByName(sField)->AsString=Query->FieldByName(sField)->AsString;
}
}
TQuery->Post();
delete TQuery;
Query->Delete();
}
delete Query;