SQL 查询更新然后恢复更改

Question

我目前对一个问题感到困惑。我可以在表中插入和删除记录，但无法更新某些字段。它确实会暂时更新它，然后在 0.5 秒后恢复更改，我实际看到了更改。顺便说一句，这是在 Delphi 7 中完成的：

CloseDatabase;     // Closes my database first to prevent an error from accessing one that is already open
OpenDatabase;      // Dynamically opens the database
ActivateEdits;

if dbeEnglish.Enabled then
begin
  qryDictionary.SQL.Text := 'Update [word list] set [english] = "'+dbeEnglish.Text+'" where  ([afrikaans] = "'+dbeAfrikaans.Text+'") and ([english] = "'+sEnglishBefore+'")';
  qryDictionary.ExecSQL;
end
else
begin
  qryDictionary.SQL.Text := 'Update [word list] set [afrikaans] = "'+dbeAfrikaans.Text+'" where ([english] = "'+dbeEnglish.Text+'") and ([afrikaans] = "'+sAfrikaansBefore+'")';
  qryDictionary.ExecSQL;
end;

SelectAll;          // SQL to select * from [word list]  as well as set the column widths
bEngOnce := False;  // variable i used to prevent both dbe (data base edits) from being edited
bAfrOnce := False;

我是否更新了 OI 中的错误或丢失的内容？它确实更新了，但并没有使其永久化。

忘记提及：表单词列表有 3 个字段：一个称为 ID、英语和南非荷兰语的自动编号字段。自动编号是否会导致更新问题？

Answer 1

我会尝试以下方法。我不确定它是否有帮助，但你可以检查 ExecSQL 结果。巴拉特似乎提到您的代码中有未提交的事务。

...
if dbeEnglish.Enabled then
begin
  qryDictionary.Connection.BeginTrans;
  try
    qryDictionary.SQL.Text := 'Update [word list] set [english] = "'+dbeEnglish.Text+'" where  ([afrikaans] = "'+dbeAfrikaans.Text+'") and ([english] = "'+sEnglishBefore+'")';
    qryDictionary.ExecSQL;
    qryDictionary.Connection.CommitTrans;
  except
    qryDictionary.Connection.RollbackTrans;
  end;
end
else
begin
  qryDictionary.Connection.BeginTrans;
  try
    qryDictionary.SQL.Text := 'Update [word list] set [afrikaans] = "'+dbeAfrikaans.Text+'" where ([english] = "'+dbeEnglish.Text+'") and ([afrikaans] = "'+sAfrikaansBefore+'")';
    qryDictionary.ExecSQL;
    qryDictionary.Connection.CommitTrans;
  except
    qryDictionary.Connection.RollbackTrans;
  end;
end;
...

您还可以检查某些行是否会受到提交的影响。这是由 TADOQuery.ExecSQL 函数结果返回的，因此您可以通过这种方式进行检查。

var
  RowsAffected: Integer;
...
RowsAffected := qryDictionary.ExecSQL;
ShowMessage(IntToStr(RowsAffected) + ' row(s) will be affected by commiting this query ...');
...