How to Append a new line and save it in DBGrid? lazarus / 16-Databases ---------------------------------------------------------------------- panoss · 2018-08-01 07:44 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). ---------------------------------------------------------------------- taazz · 2018-08-01 09:18 Quote from: panoss on August 01, 2018, 09:44:30 amI '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. ---------------------------------------------------------------------- panoss · 2018-08-01 09:28 Quote from: taazz on August 01, 2018, 11:18:27 am2) 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure TForm1.SQLQuery1AfterPost(DataSet: TDataSet);begin     SQLQuery1.ApplyUpdates;     SQLTransaction1.CommitRetaining;     end; ---------------------------------------------------------------------- taazz · 2018-08-01 09:29 oops yes applyupdates is a must as well. Sorry! I forgot about that. ---------------------------------------------------------------------- panoss · 2018-08-01 09:31 But the data is not saved in the db. ---------------------------------------------------------------------- wp · 2018-08-01 09:37 Quote from: panoss on August 01, 2018, 09:44:30 amI '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. ---------------------------------------------------------------------- panoss · 2018-08-01 09:44 I replaced the attachment in the first post with the correct one (now it really includes the db  ). ---------------------------------------------------------------------- gsa · 2018-08-01 10:42 Quote from: panoss on August 01, 2018, 11:44:17 amI replaced the attachment in the first post with the correct one (now it really includes the db  ). You should remove "SELECT * FROM countries" in property InsertSQL of SQLQuery1. Gerd ---------------------------------------------------------------------- egsuh · 2018-08-01 10:46 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 · 2018-08-01 11:37 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'. ---------------------------------------------------------------------- egsuh · 2018-08-01 11:49 "Post" might not have been done, so that ApplyUpdates has not been carried out. ---------------------------------------------------------------------- panoss · 2018-08-01 12:50 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? ---------------------------------------------------------------------- wp · 2018-08-01 12:59 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). ---------------------------------------------------------------------- panoss · 2018-08-01 20:29 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!! From both grids! And both navigators disabled! I have to close the app and re-open it in order to see the records! ---------------------------------------------------------------------- wp · 2018-08-01 20:48 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". ---------------------------------------------------------------------- panoss · 2018-08-01 20:59 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... ...what do you think?) ---------------------------------------------------------------------- wp · 2018-08-01 21:57 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure TForm1.DBNavigator1Click(Sender: TObject; Button: TDBNavButtonType);begin  if Button = nbInsert then    DBNavigator1.DataSource.Dataset.Append;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. ---------------------------------------------------------------------- egsuh · 2018-08-02 01:41 QuoteI 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. ---------------------------------------------------------------------- taazz · 2018-08-02 03:51 Quote from: panoss on August 01, 2018, 10:59:23 pmThis 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... ...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. ---------------------------------------------------------------------- mangakissa · 2018-08-02 06:13 Use DBGrid for info and a new form with editfields for modifying / insert  reords. ---------------------------------------------------------------------- panoss · 2018-08-02 10:13 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure TForm1.SQLQuery2BeforePost(DataSet: TDataSet);var SQLQuery1_country_id: Integer;begin  SQLQuery1_country_id := SQLQuery1.FieldByName('country_id').AsInteger;  if Dataset.FieldByname('country_id').IsNull then  begin    Dataset.FieldByname('country_id').Value:= SQLQuery1_country_id;  end;end;   I think it 's ok now, what do you think? ---------------------------------------------------------------------- panoss · 2018-08-02 20:12 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.