SQL query updates then revertes changes

Question

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+'" 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;

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?

Did you already checked the sql user permission setting if you are permitted on the UPDATE command? — Bryan, Oct 17 '11 at 10:50
Are you using any transacton statements like commit/rollback in your code? — Bharat, Oct 17 '11 at 10:54
Have no clue where one would check that. Where do I see the user permission — Salty Tree Sparrow, Oct 17 '11 at 10:57
What are you using to observe that the changes are beging made (and later reverted) in the database? — crefird, Oct 17 '11 at 12:14
@SaltyTreeSparrow, `where do I see the user permission`, I don't know Access but I guess you would get the error about the missing permissions. And you can do the test by connecting with some standalone database manager under this user credentials and if you will execute these update statements then you have permissions for UPDATE command under the tables. — TLama, Oct 17 '11 at 12:14
@Bharat, OP missed to mention what component he's using but I would presume the `ExecSQL` will do the commit, or am I wrong ? Salty, what components are you using, what type is the `qryDictionary` ? Is that `TADOQuery` ? — TLama, Oct 17 '11 at 12:19
@TLama, im using the ADOConnection component to open the database dynamically, you are right in saying that I use the ADOQuery component for SQL and ExecSQL. qryDictionary is the ADOQuery component's name. — Salty Tree Sparrow, Oct 17 '11 at 12:24
I get no error when doing the update. I only see infront of my eyes the table component used to display the database table changing that specific field and then changing back in under 1 second. Tried connecting like you said still failed. It does seemingly update....just doesn't seem to stick. — Salty Tree Sparrow, Oct 17 '11 at 12:33
@SaltyTreeSparrow, you are executing the query by `TADOQuery.ExecSQL`, but the changes aren't committed and then you select all records by your `SelectAll` method so your changes are lost. And that `SelectAll` takes you about 1 second. — TLama, Oct 17 '11 at 14:07

score 1 · Answer 1 · answered Oct 17 '11 at 12:47

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

It says that one row will be affected by the change (if I update the english side) and 0 rows will be affected if I update the afrikaans side. However it still doesn't commit the transaction. Even before the message shows (which is before the select all) it reverts the changes. Seems like a delete, insert will be needed to replace the update. — Salty Tree Sparrow, Oct 17 '11 at 16:27
@SaltyTreeSparrowm, no if 0 rows are affected there's nothing to change in your data, so you can't expect that something will change in your dataset. I suspect you won't have an auto commit after change but still if you'll do a `BeginTrans` and `CommitTrans` after the changes in you dataset, you should get it updated. — TLama, Oct 17 '11 at 22:54

SQL query updates then revertes changes

1 Answers1