Lost with Updates
lazarus / 16-Databases

----------------------------------------------------------------------
TRNelson · 2016-01-29 06:10

I think I may have reached exceeded by SQL knowledge by a fair degree
and have no idea what is going on.

I have a small application that displays a table using DBGrid.  I can
navigate the data in the grid as expected.  Now I have added some code
to allow the user to make changes to one of the records.  For a
variety of reasons I don't want to make the changes in the grid.  I
wrote some code to make the change.

The change appears to work (I have verified it with a table viewer)
but the table in the grid totally disappears.  When I attempt to
navigate, I get an error 'Operation cannot be performed on an inactive
dataset.'

Here is the snip of code that seems to cause my problem:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i      
SQLQuery1.Close;      SQLQuery1.SQL.Text := 'UPDATE spreadsheet SET
Edit_lock = :tEdit_lock WHERE archive_number = :tArchive_number';    
SQLQuery1.Params.ParamByName('tArchive_number').AsString  :=
Archive_number.text;     
SQLQuery1.Params.ParamByName('tEdit_lock').AsString  := '*';     
SQLQuery1.execsql;      SQLTransaction1.commit;     
DBConnection.Connected:= True;      SQLTransaction1.Active:= True; 
Any help would be greatly appreciated.  I am probably greatly
confused.

----------------------------------------------------------------------
balazsszekely · 2016-01-29 06:38

This is correct:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
 SQLQuery1.Close; SQLQuery1.SQL.Text := 'UPDATE spreadsheet SET
Edit_lock = :tEdit_lock WHERE archive_number = :tArchive_number'; SQLQ
uery1.Params.ParamByName('tArchive_number').AsString  := Archive_numbe
r.text; SQLQuery1.Params.ParamByName('tEdit_lock').AsString  :=
'*'; SQLQuery1.execsql; SQLTransaction1.commit;However you need a
separate query for the update + do not activate the transaction again.
Please delete this lines:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
DBConnection.Connected:= True;SQLTransaction1.Active:= True;

----------------------------------------------------------------------
Abelisto · 2016-01-29 07:00

Additionally to @GetMem post try Code: Pascal
 [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
SQLTransaction1.CommitRetaining; instead of Code: Pascal
 [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
SQLTransaction1.commit;

----------------------------------------------------------------------
balazsszekely · 2016-01-29 07:08

Good catch @Abelisto, I missed that one!

----------------------------------------------------------------------
mangakissa · 2016-01-29 08:27

If you using property UpdateSQL of the same TSQLquery your dbrid is
connected, you can use the same transaction connection instead of
creating a new one.

----------------------------------------------------------------------
TRNelson · 2016-01-29 13:57

Regrettably, none of those suggestions worked @Abelisto and @GetMem.

The update does happen but the table information in the grid
disappears and navigation fails.  I can reload the table info with a
Select but I lose my place.

----------------------------------------------------------------------
goodname · 2016-01-29 14:40

It is a bit of a hack but you can save the primary key before
updating. Then after reloading do a locate on the result set using the
saved primary key to get back to the same place.

----------------------------------------------------------------------
balazsszekely · 2016-01-29 14:55

Quote@TRNelson
The update does happen but the table information in the grid
disappears and navigation fails.  I can reload the table info with a
Select but I lose my place. Then please attach a simple application,
that reproduce the issue. One table with a few records.

----------------------------------------------------------------------
TRNelson · 2016-01-29 15:07

Quote from: goodname on January 29, 2016, 03:40:36 pmIt is a bit of a
hack but you can save the primary key before updating. Then after
reloading do a locate on the result set using the saved primary key to
get back to the same place.
It is an inelegant hack that I have considered and something that I
will likely do.  You say 'locate'.  Is this some SQL operation?

----------------------------------------------------------------------
TRNelson · 2016-01-29 15:07

Quote from: GetMem on January 29, 2016, 03:55:55 pmQuote@TRNelson
The update does happen but the table information in the grid
disappears and navigation fails.  I can reload the table info with a
Select but I lose my place. Then please attach a simple application,
that reproduce the issue. One table with a few records.
Yes, I will try and post one later today.  Thanks!

----------------------------------------------------------------------
goodname · 2016-01-29 17:37

Quote from: TRNelson on January 29, 2016, 04:07:21 pmIt is an
inelegant hack that I have considered and something that I will likely
do.  You say 'locate'.  Is this some SQL operation?
TSQLQuery.Locate();