开发者

SQL query updates then revertes changes

I'm currently baffled by the one problem. I can insert and delete records from my table but I can't update certain fields. It does update it temporarily before reverting changes 0.5 seconds later, I physically see the change. Btw this is done in 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+'" wher开发者_开发知识库e ([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;

Am I updating wrong or missing something in OI? It does update just doesn't make it permanent.

Forgot to mention: The table word list has 3 fields: an auto number field called ID, english and afrikaans. Could the auto number be causing a problem to update?


I would try the following. I'm not sure if it helps but you can check the ExecSQL result. It seems as Bharat mentioned that you have uncommited transaction in your code.

...
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;
...

You can also check if some of the rows will be affected by a commit. This is returned by TADOQuery.ExecSQL function result, so you can check it this way.

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

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜