SQLite and Locate with DBGrid?
lazarus / 16-Databases

----------------------------------------------------------------------
HatForCat · 2017-04-16 21:49

I am using SQLdb and a DBGrid and all working well displaying data
according specific "SELECT " clauses.

I have a checkbox field and when I click a Row it toggles that
Checkbox. This is OK if it is the first row, but if it is, say, the
third Row, the "ExecSQL will do the change but then position the
highlight to the first item.

I'd like it to come back to the Row I had been on before the click. I
tried saving the AutoInc and then using Locate but it doe snot
position the DBGrid to the Row.

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 procedure
TfrmMain.MarkReadYN(aYN : Boolean);var  anID : String;  tStr :
String;begin  anID:=dm.sqlEmails.FieldByName(fldID).AsString;  tStr:='
UPDATE Emails SET zRead = '+ArrYN[aYN]+' WHERE zID = '+anID+';'; 
dm.sqlEmails.SQL.Text:=tStr;  dm.sqlEmails.ExecSQL; 
dm.Locate('zID',anID,[]);  // This seems to be visually ignoredend;

----------------------------------------------------------------------
valdir.marcos · 2017-04-16 23:33

If I understand you correctly, the easiest way to achieve what your
are requesting is having two SQLQuery components. One for showing the
information and the other one to update it.
- Use a SQLQuery1 only to show the information on the DBGrid and keep
track of the current record position on DBGrid.
- SQLQuery1.ParseSQL := False;
- Use event OnChange of checkbox field to change information on
SQLQuery2.
- At the end, before close the form, clear the SQLQuery1 cache with
command:
SQLQuery1.CancelUpdates;

----------------------------------------------------------------------
talorigomat · 2017-04-17 16:28

TSQLQuery also has a property sqoKeepOpenOnCommit.  Try setting this
to True.  In the Object Inspector it is located under Options.

----------------------------------------------------------------------
mangakissa · 2017-04-18 07:30

If valdir.marcos you also doing a refresh on your dataset. That's the
reason why the recordpointer jumps to the first record of your
dataset. You can solve it by using a locate() after refresh.
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
dm.Locate('zID',anID,[]);  // This seems to be visually ignored This
line must be giving a error if you don't have a function locate()
created. This is right:
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
dm.sqlEmails.Locate('zID',anID,[]);  // This seems to be visually
ignored 
If it's not the case you doing it al wrong. If the dataset showed in
TDBGrid is the dataset you working, SQLdb is creating a query for you.
You only have to apply the data and a commit to save te changes.
If there only a few records to be updated, create an UpdateSQL query.
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 UPDATE
Emails SET zRead =  :zRead WHERE zID = :iID'

----------------------------------------------------------------------
HatForCat · 2017-04-20 14:55

@valdir.marcos:

Thanks, but I remember many many years back I tried something like
that, using two databases in parallel and it got to be a very big
confusing mess. It is probably OK with a small and simple Table, but I
will never go don that road again.

@talorigomat:
I already have that set, but thanks.

@mangakissa:
  OK, thank you, but I will live with the display as it ids for now.