* * *

Author Topic: SQLite and Locate with DBGrid?  (Read 626 times)

HatForCat

  • Sr. Member
  • ****
  • Posts: 260
SQLite and Locate with DBGrid?
« on: April 16, 2017, 11:49:43 pm »
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]
  1. procedure TfrmMain.MarkReadYN(aYN : Boolean);
  2. var
  3.   anID : String;
  4.   tStr : String;
  5. begin
  6.   anID:=dm.sqlEmails.FieldByName(fldID).AsString;
  7.   tStr:=' UPDATE Emails SET zRead = '+ArrYN[aYN]+' WHERE zID = '+anID+';';
  8.   dm.sqlEmails.SQL.Text:=tStr;
  9.   dm.sqlEmails.ExecSQL;
  10.   dm.Locate('zID',anID,[]);  // This seems to be visually ignored
  11. end;
  12.  
Acer-i5, 2.6GHz, 6GB, 256-SSD, Ubuntu 14.04-LTS, Unity Desktop, Lazarus 1.6.2, SQLite3 -- Retired: Programming for my own use for Ubuntu.

valdir.marcos

  • Full Member
  • ***
  • Posts: 216
Re: SQLite and Locate with DBGrid?
« Reply #1 on: April 17, 2017, 01:33:14 am »
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

  • Jr. Member
  • **
  • Posts: 82
Re: SQLite and Locate with DBGrid?
« Reply #2 on: April 17, 2017, 06:28:31 pm »
TSQLQuery also has a property sqoKeepOpenOnCommit.  Try setting this to True.  In the Object Inspector it is located under Options.
Lazarus 1.6.4, Windows 10

mangakissa

  • Hero Member
  • *****
  • Posts: 684
Re: SQLite and Locate with DBGrid?
« Reply #3 on: April 18, 2017, 09:30:44 am »
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]
  1. dm.Locate('zID',anID,[]);  // This seems to be visually ignored
  2.  
This line must be giving a error if you don't have a function locate() created. This is right:
Code: Pascal  [Select]
  1. dm.sqlEmails.Locate('zID',anID,[]);  // This seems to be visually ignored
  2.  

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]
  1. UPDATE Emails SET zRead =  :zRead WHERE zID = :iID'
  2.  
 
 
« Last Edit: April 18, 2017, 09:41:47 am by mangakissa »
Lazarus 1.6 (32b) / FPC 3.0
Windows Vista /  10

HatForCat

  • Sr. Member
  • ****
  • Posts: 260
Re: SQLite and Locate with DBGrid?
« Reply #4 on: April 20, 2017, 04:55:55 pm »
@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.
Acer-i5, 2.6GHz, 6GB, 256-SSD, Ubuntu 14.04-LTS, Unity Desktop, Lazarus 1.6.2, SQLite3 -- Retired: Programming for my own use for Ubuntu.

 

Recent

Get Lazarus at SourceForge.net. Fast, secure and Free Open Source software downloads Open Hub project report for Lazarus