从excel表导入到sql2000中为什么总是说主键重复？

Question

    为什么在插入有相同记录数据时不行啊这样处理
    照这样的话，就算插入相同的记录也会出错，而是在mmo1里的显示导入不成功。
   而数据表是这样的：  procedure TForm1.SpeedButton1Click(Sender: TObject);
var
    ExcelID,Sheet: Variant;
    i,ExcelRowCount,OkNum,FailNum:integer;
    sFilename :string;
    
begin
    //指定文件
    OkNum:=0;   //导入成功的数据
    FailNum:=0; //导入失败的数据
    mmo1.Clear;
    //指定要导入的EXCEL文件
    with dlgOpen1 do
    begin
     DefaultExt:='Xls';
     Filter :='Excel工作簿文件（*.xls）|*.Xls';
     if Execute then
         sFileName := FileName
     else
         exit;
    end;    try
        ExcelID := CreateOleObject('Excel.Application');
        ExcelID.Visible :=False; //若为true,则将显示并打开将要导入的excel文件
    except
      on E: Exception do
      begin
        ExcelID.Quit;
        ExcelID := Unassigned;
        Application.Restore;
        Application.BringToFront;
        MessageBox(Self.Handle,Pchar('系统提示您,创建Excel对象出错,原因为:'+ e.Message), Pchar(AppliCation.Title),MB_OK+MB_ICONERROR);
        Exit;
      end;
   end;   try
      Try
        ExcelID.WorkBooks.Open(sFilename);
        Sheet:= ExcelID.WorkBooks[1].WorkSheets[1];
//        ExcelColCount := ExcelID.WorkSheets[1].UsedRange.Columns.Count;
        ExcelRowCount := ExcelID.WorkSheets[1].UsedRange.Rows.Count; //获得本数据表有多少行数据
        con1.BeginTrans; //开始事务
        for i :=2 to ExcelRowCount do   //将要从哪行开始读,本程序为从第二行开始读,第一行为标题
        begin
          if length(trim(Sheet.Cells[i,1 ].Value)) <=0 then break;   //如果第一个单元格为空,则提前结束循环,此处也可以用其它方式结束
          With qry1 do
          begin
              close;
              SQL.Clear;                     //班级名称和学期为主键
              SQL.Add('Select * from chargestandardinfo where classname='''+Sheet.Cells[i,1 ].Value+''' and term='''+Sheet.Cells[i,2 ].Value+''''); //此处指定每行第1、2单元格的内容为关键字,不得有重复,若有重复,则该行的数据不导入
              Open;
              if eof then
              begin
                Close;
                SQL.Clear;
                SQL.Add('Insert into chargestandardinfo( classname, term, tuition, bookfee)');
                SQL.Add(' values( :classname, :term, :tuition, :bookfee)');
                Parameters.parambyname('classname').Value := Sheet.Cells[i,1].Value;
                Parameters.parambyname('term').Value := Sheet.Cells[i,2].Value;
                Parameters.parambyname('tuition').Value := Sheet.Cells[i,3 ].Value;
                Parameters.parambyname('bookfee').Value := Sheet.Cells[i,4 ].Value;
                ExecSQL;
                OkNum := OkNum+1;   //导入成功数加1                mmo1.Lines.Add('第  '+inttostr(i)+'  行数据导入成功！')              end
              else
              begin
                FailNum := FailNum+1; //若库已有相应的关键字记录,则导入失败数加1
                mmo1.Lines.Add('第 '+inttostr(i)+' 行数据导入失败！')
              end;
          end;
        end;
          con1.CommitTrans; //提交事务
          MessageBox( 0 ,Pchar('系统提示您:系统共成功导入'+IntToStr(OkNum+FailNum)+'条信息,其中'+IntToStr(FailNum)+'条信息系统内已有记录,'+IntToStr(OkNum)+'条信息成功导入!'),
                                         Pchar( ' 警 告 '),MB_OK+MB_ICONINFORMATION);
         
       except
         on E: Exception do
         begin
           con1.RollbackTrans; //报错,回滚事务
           MessageBox(Self.Handle,Pchar('系统提示您,数据导入失败,原因为:'+e.Message),
                                         Pchar(' 警  告 '),MB_OK+MB_ICONERROR);
         end;
       end
   finally
      ExcelID.WorkBooks[1].Close(false,'');
      ExcelID.Quit;
      ExcelID := Unassigned;
      sheet := Unassigned;
      Application.Restore;
      Application.BringToFront;   end;

Answer 1

数据表：classname term tuition bookfee 其中classname和term合在一起为主键   
  SQL.Add('Select * from chargestandardinfo where classname='''+Sheet.Cells[i,1 ].Value+''' and term='''+Sheet.Cells[i,2 ].Value+''''); //此处指定每行第1、2单元格的内容为关键字,不得有重复,若有重复,则该行的数据不导入
 这里查询插入不重复的记录的  

Answer 2

   为什么只设置当classname为主键时，插入重复的会失败。为什么当classname和term都为主键时会出错啊？代码我实在找不出那里有错的 ，就是执行 execSQL时就出错说主键不能重复

Answer 3

導入前先判斷下聯合主鍵是否有重復，然後再導入
procedure TRES_DCC_ECRN_F.cxButton1Click(Sender: TObject);
var
  ExcelApp,WorkBook:Olevariant;
  ExcelSheetCount,i,k:Integer;
begin
  inherited;
  if RzButtonEdit1.Text <>'' then
  begin
    if (ExtractFileExt(RzButtonEdit1.Text)<>'.xls')   then
    begin
      MessageDlg('請選擇要導入的Excel檔案!',mtWarning,[mbOK],0);
      RzButtonEdit1.Clear;
    end
    else
    begin
      try
        Application.ProcessMessages;
        ExcelApp:=CreateOleObject('Excel.Application');
        WorkBook:=ExcelApp.WorkBooks.Open(RzButtonEdit1.Text);
        ExcelApp.Visible:=False;
        ExcelSheetCount:=WorkBook.WorkSheets.Count;
        for i:=1 to ExcelSheetCount  do
        begin
          Screen.Cursor:=crSQLWait;
          WorkBook.WorkSheets[i].Activate;
          a:=ExcelApp.Cells[4,2].Value;
          b:=ExcelApp.Cells[8,2].Value;
          c:=ExcelApp.Cells[14,2].Value;
          d:=ExcelApp.Cells[21,2].Value;
          e:=ExcelApp.Cells[22,2].Value;
          f:=ExcelApp.Cells[23,2].Value;
          if ((Copy(Trim(a),1,3)<>'ECR') and (b<>'')) and ((Copy(Trim(a),1,3)<>'DCR') and (b<>'')) then
          begin
            MessageDlg('檔案格式錯誤!',mtWarning,[mbOK],0);
            InsertErrorLOG;
            Screen.Cursor:=crDefault;
            Exit;
          end;          with adoq_ecrn do
          begin
            Close;
            SQL.Text:='select * from RES_ECR_TYPE where ECRNO='''+ a+'''';
            Open;
            if RecordCount=1 then
            begin
              MessageDlg('請檢查:導入檔案時編號"'+ a+'"的記錄重復!',mtWarning,[mbOK],0);
              InsertRepeatLOG;
              Screen.Cursor:=crDefault;
              Exit;
            end;
          end;           with adoq_ecrn do
           begin
             Close;
             SQL.Clear;
             SQL.Add('insert into RES_ECR_TYPE(ECRNO,DESOFCHANGE,RELEASEDATE,RESPEOPLE,ISSUEDEPT,MODEL) values(:a,:b,:c,:d,:e,:f)');
             Parameters.ParamByName('a').Value:=a;
             Parameters.ParamByName('b').Value:=b;
             Parameters.ParamByName('c').Value:=Trim(Copy(c,1,19));
             Parameters.ParamByName('d').Value:=d;
             Parameters.ParamByName('e').Value:=e;
             Parameters.ParamByName('f').Value:=f;
             ExecSQL;
           end;
           InsertECRLOG;           ProgressBar1.Min:=0;
           ProgressBar1.Max:=ExcelSheetCount;
           for k:=33 to WorkBook.WorkSheets[i].usedrange.rows.count  do
           begin
             g:=ExcelApp.Cells[K,1].Value;
             q:=ExcelApp.Cells[k,2].Value;
             w:=ExcelApp.Cells[k,3].Value;
             v:=ExcelApp.Cells[k,4].Value;
             r:=ExcelApp.Cells[k,5].Value;
             t:=ExcelApp.Cells[k,6].Value;
             y:=ExcelApp.Cells[k,7].Value;
             u:=ExcelApp.Cells[k,8].Value;
             o:=ExcelApp.Cells[k,9].Value;
             if (q<>'')and(w<>'') then
             with adoq_item do
             begin
               Close;
               SQL.Clear;
               SQL.Add('insert into RES_ECR_ITEM(GROUPID,ECRNO,ITEMNO,ITEMDESPTION,FZRELATION,CHANGETYPE,BEFORECHANGE,AFTERCHANGE,PROPOSE,REMARK) values(:g,:p,:q,:w,:v,:r,:t,:y,:u,:o)');
               Parameters.ParamByName('g').Value:=g;
               Parameters.ParamByName('p').Value:=a;
               Parameters.ParamByName('q').Value:=q;
               Parameters.ParamByName('w').Value:=w;
               Parameters.ParamByName('v').Value:=v;
               Parameters.ParamByName('r').Value:=r;
               Parameters.ParamByName('t').Value:=t;
               Parameters.ParamByName('y').Value:=y;
               Parameters.ParamByName('u').Value:=u;
               Parameters.ParamByName('o').Value:=o;
               ExecSQL;
               ProgressBar1.Position:=ProgressBar1.Position+1;
             end;
           end;
           Screen.Cursor:=crDefault;
           Application.ProcessMessages;
           ProgressBar1.Position:=0;
           RefreshECRN;
           RefreshGroupItem;
           SendToEmail;
           Zt:=1;
         end;
      finally
        ExcelApp.ActiveWorkBook.Saved:=True;
        WorkBook.Close;
        ExcelApp.Quit;
      end;
     end;
  end
  else
  begin
    MessageDlg('請選擇要導入的Excel檔案!',mtWarning,[mbOK],0);
    Exit;
  end;
end;

Answer 4

     我的代码主要是这里他无法检查出原来的数据库里是否有重复的记录。为空就应插入，但是要是没空的话就不应插入，但是我导入数据时，他还是说主键重复？我都作了出错处理了，为什么？但是他只有一个当为主键时就没事，数据库里没重复就插入，有重复就插入失败。为什么classname和 term合在一起作为主键时就不行？
    
With qry1 do
          begin
              close;
              SQL.Clear;                     //班级名称和学期为主键
              SQL.Add('Select * from chargestandardinfo where classname='''+Sheet.Cells[i,1 ].Value+''' and term='''+Sheet.Cells[i,2 ].Value+''''); //此处指定每行第1、2单元格的内容为关键字,不得有重复,若有重复,则该行的数据不导入
              Open;
              if eof then
              begin
                Close;
                SQL.Clear;
                SQL.Add('Insert into chargestandardinfo( classname, term, tuition, bookfee)');