在Delphi 7中运行更新查询时，如何解决封闭的数据集错误?

问题描述

在使用Microsoft Jet引擎和Microsoft Access(* .mdb)数据库的应用程序中，我在Delphi 7中一直出现错误.我正在通过TADOQuery组件进行连接.该错误提示无法对封闭的数据集执行此操作"，并且在尝试执行UPDATE查询时仅在一个事件处理程序中发生:

I keep getting an error in Delphi 7 with my application that uses the Microsoft Jet engine and a Microsoft Access (*.mdb) database. I am making the connection via a TADOQuery component. The error says 'Cannot perform this operation on a closed dataset' and happens only in one event handler when trying to execute an UPDATE query:

frmHome.adoqryMain.SQL.Text := 'UPDATE Predictions SET Complete = True WHERE UID = "'+sUID+'"';

事件处理程序的代码如下:

The event handler's code is as follows:

    procedure TfrmAdmin.bmbSubmitClick(Sender: TObject);
var
  sScore, sEID, sPrediction, sUID : String;
  iRecordCount, x, iPos, iLength, iActual, iPoints, iPointsNew : Integer;
  rPrediction : Real;
begin
  // Assign values to variables
  sScore := IntToStr(sedSuthies.Value) + '-' + IntToStr(sedOpponent.Value);
  iActual := sedSuthies.Value + sedOpponent.Value;
  sEID := frmHome.arrEID[lstEvents.ItemIndex];
  // Update the score for the event in the database
  frmHome.adoqryMain.Active := False;
  frmHome.adoqryMain.SQL.Clear;
  frmHome.adoqryMain.SQL.Text := 'UPDATE Events SET Score = "'+sScore+'",Complete = True WHERE EID = "'+sEID+'" ';
  frmHome.adoqryMain.ExecSQL;
  frmHome.adoqryMain.SQL.Text := 'SELECT * FROM Predictions WHERE EID = "'+sEID+'" ';
  frmHome.adoqryMain.Open;
  iRecordCount := frmHome.adoqryMain.RecordCount;
  //Assign points to users for all the predictions
  for x := 0 to (iRecordCount - 1) do begin
    sUID := frmHome.adoqryMain.Fields[1].AsString;
    sPrediction := frmHome.adoqryMain.Fields[4].AsString;
    iPos := Pos('-',sPrediction) - 1;
    iLength := Length(sPrediction) - iPos;
    ShowMessage('1');
    if ((sedSuthies.Value >= sedOpponent.Value) AND (StrToFloat(Copy(sPrediction, 1, iPos)) >= StrToFloat(Copy(sPrediction, iPos + 2, iLength + 1)))) OR ((sedSuthies.Value < sedOpponent.Value) AND (StrToFloat(Copy(sPrediction, 1, iPos)) < StrToFloat(Copy(sPrediction, iPos + 2, iLength + 1)))) then begin
      rPrediction := StrToFloat(Copy(sPrediction, iPos + 2, iLength + 1)) + StrToFloat(Copy(sPrediction, 1, iPos));
      if rPrediction >= iActual then
        rPrediction := rPrediction - iActual
      else
        rPrediction := iActual - rPrediction;
      iPoints := Round(10 * (1 - (rPrediction / iActual)));
    end
    else
      iPoints := 0;
    ShowMessage('2');
    frmHome.adoqryMain.Open;
    frmHome.adoqryMain.SQL.Text := 'UPDATE Predictions SET Complete = True WHERE UID = "'+sUID+'"';
    frmHome.adoqryMain.ExecSQL;
    ShowMessage('3');
    ShowMessage(IntToStr(iPoints));
    frmHome.adoqryMain.Next;
  end;
  ShowMessage('Score succefully submitted!');
  frmHome.UpdateEventLists;
end;

此刻有点混乱，但是不管UPDATE查询更新什么，它总是会出现此错误.我尝试过使用各种运行查询的方法(带有语句和参数)等打开TADOQuery的方法.任何帮助将不胜感激.

It's a bit messy at the moment, but irrespective of what the UPDATE query updates, it always gives this error. I've tried opening the TADOQuery, using various methods of running the query (with statement and parameters), etc. Any help would be greatly appreciated.

P.S. ShowMessage函数用于调试目的，将被删除.

P.S. The ShowMessage functions are there for debugging purposes and will be removed.

推荐答案

我想我知道您正在尝试做什么.您使用相同的qry从中获取数据，并在循环中使用它来更新数据库.

I think I see what you're trying to do. You're using the same qry both to fetch data from, and in the loop you use it to update the database.

开始使用2个变相，然后它应该可以工作.

Start using 2 adoqueries and then it should work.

所以Adoquerymain和一个新的AdoQueryInLoop

So Adoquerymain, and a new AdoQueryInLoop

ShowMessage('2');
frmHome.adoqryInLoop.Close; //I'm an overzealous query closer
frmHome.adoqryInLoop.SQL.Text := 'UPDATE Predictions SET Complete = True WHERE UID = "'+sUID+'"';
frmHome.adoqryInLoop.ExecSQL;
ShowMessage('3');

这篇关于在Delphi 7中运行更新查询时，如何解决封闭的数据集错误?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！