Recent

Author Topic: How to Append a new line and save it in DBGrid?  (Read 3486 times)

panoss

  • Full Member
  • ***
  • Posts: 156
  • You only live twice
How to Append a new line and save it in DBGrid?
« on: August 01, 2018, 09:44:30 am »
I 'm trying to make a Master - Detail app based on this tutorial.
But I have problems with DBGrid.
I 'm using the DBNavigator.

I press the button 'Insert' (+), it inserts a new row before the current (and does not append it after last row, from what I read).
I want to append it after the last row of the grid.

Then I press the 'Post' button of the navigator but the record does not get saved.
How can I save it?

I 've attached the project (including the db).
You 'll have to add the sqlite3.dll (I use version 3.21, if this of any importance) by your self (put it in the same folder with the project file ('MasterDetail1.lpi'). I can't upoad it because, even compressed, exceeds the limit of 250kb).

« Last Edit: August 01, 2018, 12:00:16 pm by panoss »
Windows 7 32bit, Lazarus v1.8.4    FPC 3.0.4.

taazz

  • Hero Member
  • *****
  • Posts: 5363
Re: How to Append a new line and save it in DBGrid?
« Reply #1 on: August 01, 2018, 11:18:27 am »
I 'm trying to make a Master - Detail app based on this tutorial.
But I have problems with DBGrid.
I 'm using the DBNavigator.

I press the button 'Insert' (+), it inserts a new row before the current (and does not append it after last row, from what I read).
I want to append it after the last row of the grid.

Then I press the 'Post' button of the navigator but the record does not get saved.
How can I save it?

I 've attached the project (including the db).
You 'll have to add the sqlite3.dll (I use version 3.21, if this of any importance) by your self (put it in the same folder with the project file ('MasterDetail1.lpi')).
haven't seen your code sorry I do not plan to spend time on it.
two comments that might help you in your endeavors.
1) dbnavigator does not support append only insert you can add your own button that calls append instead or press ctrl+end inside the grid to go to the last record and then press down that will auto append.
2) calling post is half the job, the designers of the library insist on manual transaction management (ignoring the world around them) so after post you need to call commit or commit retaining for the changes to persist.
Good judgement is the result of experience … Experience is the result of bad judgement.

OS : Windows 7 64 bit
Laz: Lazarus 1.4.4 FPC 2.6.4 i386-win32-win32/win64

panoss

  • Full Member
  • ***
  • Posts: 156
  • You only live twice
Re: How to Append a new line and save it in DBGrid?
« Reply #2 on: August 01, 2018, 11:28:15 am »
2) calling post is half the job, the designers of the library insist on manual transaction management (ignoring the world around them) so after post you need to call commit or commit retaining for the changes to persist.

I have done this, ApplyUpdates and CommitRetaining:
Code: Pascal  [Select]
  1. procedure TForm1.SQLQuery1AfterPost(DataSet: TDataSet);
  2. begin
  3.      SQLQuery1.ApplyUpdates;
  4.      SQLTransaction1.CommitRetaining;    
  5. end;  
  6.  
Windows 7 32bit, Lazarus v1.8.4    FPC 3.0.4.

taazz

  • Hero Member
  • *****
  • Posts: 5363
Re: How to Append a new line and save it in DBGrid?
« Reply #3 on: August 01, 2018, 11:29:48 am »
oops yes applyupdates is a must as well. Sorry! I forgot about that.
Good judgement is the result of experience … Experience is the result of bad judgement.

OS : Windows 7 64 bit
Laz: Lazarus 1.4.4 FPC 2.6.4 i386-win32-win32/win64

panoss

  • Full Member
  • ***
  • Posts: 156
  • You only live twice
Re: How to Append a new line and save it in DBGrid?
« Reply #4 on: August 01, 2018, 11:31:21 am »
But the data is not saved in the db.
Windows 7 32bit, Lazarus v1.8.4    FPC 3.0.4.

wp

  • Hero Member
  • *****
  • Posts: 5836
Re: How to Append a new line and save it in DBGrid?
« Reply #5 on: August 01, 2018, 11:37:08 am »
I 've attached the project (including the db).
No. The db is missing. And you should not hard-code the db file name in the SQLite3Connection because nobody of us will have the db at your exact absolute location. Better to assign the relative db filename in the oncreate event and open the connection and sqlqueries there.
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

panoss

  • Full Member
  • ***
  • Posts: 156
  • You only live twice
Re: How to Append a new line and save it in DBGrid?
« Reply #6 on: August 01, 2018, 11:44:17 am »
I replaced the attachment in the first post with the correct one (now it really includes the db  :D).

« Last Edit: August 01, 2018, 12:02:32 pm by panoss »
Windows 7 32bit, Lazarus v1.8.4    FPC 3.0.4.

gsa

  • Newbie
  • Posts: 4
Re: How to Append a new line and save it in DBGrid?
« Reply #7 on: August 01, 2018, 12:42:15 pm »
I replaced the attachment in the first post with the correct one (now it really includes the db  :D).

You should remove "SELECT * FROM countries" in property InsertSQL of SQLQuery1.

Gerd

egsuh

  • Full Member
  • ***
  • Posts: 189
Re: How to Append a new line and save it in DBGrid?
« Reply #8 on: August 01, 2018, 12:46:54 pm »
gsa is right.

You had defined SQLQuery1.insertSQL as  'select * from countries'.   If you want to add something here, I think it should be something like:

   insert into countries (country_name) values (:country_name)
  <-- this worked, but not sure.

I just removed it, and everything works fine...  the primary key is automatically given when post a new country name (moving to other row after typing in a country name), and the content is saved.

(I think you already know). You don't to have press post. Just moving to previous or next row from "recently edited" one automatically do the 'post'

Quite easy and simple.  Have fun.

panoss

  • Full Member
  • ***
  • Posts: 156
  • You only live twice
Re: How to Append a new line and save it in DBGrid?
« Reply #9 on: August 01, 2018, 01:37:57 pm »
Ok, I deleted the SQL from SQLQuery1.insertSQL (ofcourse I had put it there by mistake  :-[).
Now I can save the new data. Fine!

But, when I delete a row and then press refresh on the navigator, I get this error:
'Must apply updates before refreshing data'.
Windows 7 32bit, Lazarus v1.8.4    FPC 3.0.4.

egsuh

  • Full Member
  • ***
  • Posts: 189
Re: How to Append a new line and save it in DBGrid?
« Reply #10 on: August 01, 2018, 01:49:36 pm »
"Post" might not have been done, so that ApplyUpdates has not been carried out.

panoss

  • Full Member
  • ***
  • Posts: 156
  • You only live twice
Re: How to Append a new line and save it in DBGrid?
« Reply #11 on: August 01, 2018, 02:50:56 pm »
I tried 'SQLQuery1.Post' in event SQLQuery1AfterDelete, but I get an error. ('it 's not in an insert or edit state')
So, I guess, it doesn't need a post but something else?
« Last Edit: August 01, 2018, 02:53:10 pm by panoss »
Windows 7 32bit, Lazarus v1.8.4    FPC 3.0.4.

wp

  • Hero Member
  • *****
  • Posts: 5836
Re: How to Append a new line and save it in DBGrid?
« Reply #12 on: August 01, 2018, 02:59:54 pm »
Attached modified version of your demo does not require explicit ApplyUpdates and Commt commands because the corresponding options, sqoAutoApplyUpdates and sqoAutoCommit, have been set in the SQLQuery - this gets close to how the old BDE components of Delphi were working. Deleting and inserting works for both master and detail datasets. I also added an OnBeforePost handler for the detail dataset to make sure that the foreign key to the master dataset has been entered (of course you could also create the field with the attribute Required).
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

panoss

  • Full Member
  • ***
  • Posts: 156
  • You only live twice
Re: How to Append a new line and save it in DBGrid?
« Reply #13 on: August 01, 2018, 10:29:00 pm »
wp I tried your version (MasterDetail-wp.zip) but does not work correctly:
(in the left grid)
- I click on '+' of the navigator (the one on the left)
- I enter the name for the new country
- I click on another row. All records are gone! Removed! Vanished!! :o From both grids! And both navigators disabled!

I have to close the app and re-open it in order to see the records!



« Last Edit: August 01, 2018, 10:37:55 pm by panoss »
Windows 7 32bit, Lazarus v1.8.4    FPC 3.0.4.

wp

  • Hero Member
  • *****
  • Posts: 5836
Re: How to Append a new line and save it in DBGrid?
« Reply #14 on: August 01, 2018, 10:48:19 pm »
I could swear that it was working - now it does not...

This way it works (hopefully...):
  • Add also sqoKeepOpenOnCommit to the Options of both SQLQueries.
  • You must manually close the connection when the program ends (otherwise there will be an error that "Operation cannot be performed on active dataset".
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10