Lazarus

Programming => Databases => Topic started by: panoss on August 01, 2018, 09:44:30 am

Title: How to Append a new line and save it in DBGrid?
Post by: panoss on August 01, 2018, 09:44:30 am
I 'm trying to make a Master - Detail app based on this (http://wiki.freepascal.org/MasterDetail) 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).

Title: Re: How to Append a new line and save it in DBGrid?
Post by: taazz on August 01, 2018, 11:18:27 am
I 'm trying to make a Master - Detail app based on this (http://wiki.freepascal.org/MasterDetail) 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.
Title: Re: How to Append a new line and save it in DBGrid?
Post by: panoss 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.  
Title: Re: How to Append a new line and save it in DBGrid?
Post by: taazz on August 01, 2018, 11:29:48 am
oops yes applyupdates is a must as well. Sorry! I forgot about that.
Title: Re: How to Append a new line and save it in DBGrid?
Post by: panoss on August 01, 2018, 11:31:21 am
But the data is not saved in the db.
Title: Re: How to Append a new line and save it in DBGrid?
Post by: wp 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.
Title: Re: How to Append a new line and save it in DBGrid?
Post by: panoss 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).

Title: Re: How to Append a new line and save it in DBGrid?
Post by: gsa 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
Title: Re: How to Append a new line and save it in DBGrid?
Post by: egsuh 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.
Title: Re: How to Append a new line and save it in DBGrid?
Post by: panoss 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'.
Title: Re: How to Append a new line and save it in DBGrid?
Post by: egsuh on August 01, 2018, 01:49:36 pm
"Post" might not have been done, so that ApplyUpdates has not been carried out.
Title: Re: How to Append a new line and save it in DBGrid?
Post by: panoss 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?
Title: Re: How to Append a new line and save it in DBGrid?
Post by: wp 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).
Title: Re: How to Append a new line and save it in DBGrid?
Post by: panoss 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!



Title: Re: How to Append a new line and save it in DBGrid?
Post by: wp on August 01, 2018, 10:48:19 pm
I could swear that it was working - now it does not...

This way it works (hopefully...):
Title: Re: How to Append a new line and save it in DBGrid?
Post by: panoss on August 01, 2018, 10:59:23 pm
This one works perfectly!!!
Now, what if I want the record to be appended (that is to be added to the end of the rows) and not just inserted after the current?
(ok, there have been some suggestions that I should make my own button that calls append, but I was thinking of something more...elegant...like...subclassing the dbnavigator... :-X...what do you think?)
Title: Re: How to Append a new line and save it in DBGrid?
Post by: wp on August 01, 2018, 11:57:47 pm
No - just look at the DBNavigator events: There's an OnClick and it has the NavButton clicked as a parameter!

I tested this to work (hopefully, again...):
Code: Pascal  [Select]
  1. procedure TForm1.DBNavigator1Click(Sender: TObject; Button: TDBNavButtonType);
  2. begin
  3.   if Button = nbInsert then
  4.     DBNavigator1.DataSource.Dataset.Append;
  5. end;

You should also consider sorting the query by the ID (SELECT ... FROM ... ORDER BY ...) then the new record will be put to the correct location automatically after posting.
Title: Re: How to Append a new line and save it in DBGrid?
Post by: egsuh on August 02, 2018, 03:41:17 am
Quote
I want the record to be appended

You may press down keys until a new blank row is created. But the position in database is meaningless. Well, might have had some meaning at dBASE or Paradox days, but not now. You'd better get accustomed  to using index, 'order by', etc.
Title: Re: How to Append a new line and save it in DBGrid?
Post by: taazz on August 02, 2018, 05:51:36 am
This one works perfectly!!!
Now, what if I want the record to be appended (that is to be added to the end of the rows) and not just inserted after the current?
(ok, there have been some suggestions that I should make my own button that calls append, but I was thinking of something more...elegant...like...subclassing the dbnavigator... :-X...what do you think?)
it is far easier and faster to drop a toolbar wiht some tool buttons that emulate or even replicate the dbnavigator behavior than to subclass dbnavigator to add a button. So I personally wouldn't waste time on it unless you plan to
1) create a feature request in the bug tracker with the new control
2) create a component library or extend an existing component library

in which case I would suggest to either get intouch with the library authors before any decisions to see how they would like to proceed.
Title: Re: How to Append a new line and save it in DBGrid?
Post by: mangakissa on August 02, 2018, 08:13:45 am
Use DBGrid for info and a new form with editfields for modifying / insert  reords.
Title: Re: How to Append a new line and save it in DBGrid?
Post by: panoss on August 02, 2018, 12:13:55 pm
I think I finished it!
(check the attached file)

The essence of the changes I made is in the before post event of the Query2:
Code: Pascal  [Select]
  1. procedure TForm1.SQLQuery2BeforePost(DataSet: TDataSet);
  2. var SQLQuery1_country_id: Integer;
  3. begin
  4.   SQLQuery1_country_id := SQLQuery1.FieldByName('country_id').AsInteger;
  5.   if Dataset.FieldByname('country_id').IsNull then
  6.   begin
  7.     Dataset.FieldByname('country_id').Value:= SQLQuery1_country_id;
  8.   end;
  9. end;    
  10.  

I think it 's ok now, what do you think?
Title: Re: How to Append a new line and save it in DBGrid?
Post by: panoss on August 02, 2018, 10:12:52 pm
I see...silence, I 'll interprete this as an approval.
I think this project could be incuded in Lazarus' example projects.
It would have saved me a lot of time if it was there.