PenelopeSkye 1 Posted March 2, 2023 Long story short: How can I refresh a dbedit box with data from a table I have just inserted data into? Long story long: My sql table and UI has fields for PresentationID, DesignID, StockID plus 5 other initially blank fields. With no input from the user I populate the PresentationID, DesignID, StockID fields in the database using values gotten from elsewhere in the application, the 5 fields are populated with null because they are not included in the insert (see code below). If I did include them they would be null anyway since I don't enter data into them until after the row is inserted with the 3 values. After I insert values into the database using the code below I am able to enter text in the other 5 fields or update the 3 fields but when I click on another tab in the application, and leave the tab I am on, the code written elsewhere that posts the data kicks in and attempts to update the database with the values any of the 8 fields. At that point I get the following Cannot insert the value NULL into column 'Presentation', table 'AdiMS2.dbo.ValuePackLinks'; column does not allow nulls. INSERT fails. because the application doesn't recognize the values just inserted for PresentationID, DesignID, StockID. If I leave the app and come back in the 3 fields are populated with data and the post works just fine with values from the 8 fields. How can I refresh a dbedit box with data from a table I have just inserted data into so that I can immediately start editing and not have to leave the app? Does that make sense? Thank you! PresentationID := dm.tb_dm_presentation_design_links.fieldbyname('presentation').asstring; DesignID := dm.tb_design_master.fieldbyname('design_id').asstring; StockID := dm.tb_design_master.fieldbyname('jmc_stock_num').asstring; q_InsValuePackLinks.SQL.Add ('insert into ValuePackLinks '); q_InsValuePackLinks.SQL.Add ('(Presentation,Design_id,Stock_pack)'); q_InsValuePackLinks.SQL.Add ('VALUES('+Quotedstr(PresentationID)+','+Quotedstr(DesignID)+','+Quotedstr(StockID)+')'); q_InsValuePackLinks.ExecSQL ; Share this post Link to post
programmerdelphi2k 237 Posted March 2, 2023 (edited) I dont know if understand but... the "post" happens when you change to another record, for example... if any field is "NOT NULL", then it needs a valid value! then... empty or null it's not valid here! if you need "insert" a new record in "a table", based on values from other, then you needs have it in "another table"! in SQL Db u can use the "events" INSERTING, UPDATING, DELETING to process any other actions, for example, insert a new record in historic tables...or any other action. in Delphi, you can use DATASOURCE component to create a MASTER-DETAIL way... then, you can have your fields DETAILS automacally fill when in edit mode, for example. Edited March 2, 2023 by programmerdelphi2k Share this post Link to post
PenelopeSkye 1 Posted March 2, 2023 All 5 fields are set to accept null values, only the 3 are not null and I always have that data. They are indeed being inserted as new rows into another table, not the tables that the 3 fields are originally populated from I am going to start investigating the master detail relationship! Thanks James! Share this post Link to post
programmerdelphi2k 237 Posted March 2, 2023 DBEDIT as others DBxxxx dont refresh automatically any changes on db, you needs refresh your records... tablexxx.REFRESH... for example or close and open table. a refresh can cause "cancel" changes not commited on tables Share this post Link to post
PenelopeSkye 1 Posted March 2, 2023 Ooh! I will try closing and opening first! Share this post Link to post
programmerdelphi2k 237 Posted March 2, 2023 no no no... use refresh!!! it's almost the same. Share this post Link to post
programmerdelphi2k 237 Posted March 2, 2023 I think your case is a MASTER-DETAIL case! solve it Share this post Link to post
PenelopeSkye 1 Posted March 2, 2023 I will go back to studying the master detail thingee! Thanks Mr. Bond! Share this post Link to post
programmerdelphi2k 237 Posted March 2, 2023 (edited) type TForm1 = class(TForm) DBGrid1: TDBGrid; DBGrid2: TDBGrid; fdMemMASTER: TFDMemTable; fdMemDETAILS: TFDMemTable; fdMemDETAILSID_XX_One: TIntegerField; fdMemDETAILSID_XX_Two: TIntegerField; fdMemDETAILSID_XX_Three: TIntegerField; fdMemDETAILSAny_Field_Null_Or_Not: TStringField; fdMemMASTERID_AA_One: TIntegerField; fdMemMASTERID_AA_Two: TIntegerField; fdMemMASTERAny_Field_Null_Or_Not: TStringField; dsMASTER: TDataSource; dsDETAILS: TDataSource; procedure FormCreate(Sender: TObject); private { Private declarations } public { Public declarations } end; var Form1: TForm1; implementation {$R *.dfm} procedure TForm1.FormCreate(Sender: TObject); begin fdMemMASTER.Close; fdMemMASTER.IndexFieldNames := 'ID_AA_One;ID_AA_Two'; // simulating my PKs fdMemMASTER.IndexesActive := true; // fdMemDETAILS.Close; fdMemDETAILS.MasterSource := dsMASTER; fdMemDETAILS.MasterFields := 'ID_AA_One;ID_AA_Two'; fdMemDETAILS.DetailFields := 'ID_XX_Two;ID_XX_Three'; fdMemDETAILS.IndexFieldNames := 'ID_XX_Two;ID_XX_Three'; fdMemDETAILS.IndexesActive := true; // fdMemMASTER.Open; fdMemDETAILS.Open; end; end. A sample using FDMemTable in FireDAC for test!!! Edited March 3, 2023 by programmerdelphi2k Share this post Link to post
PenelopeSkye 1 Posted March 2, 2023 I am still getting errors but I will start up again Monday! I will solve it! Share this post Link to post
programmerdelphi2k 237 Posted March 2, 2023 my sample is using just 2 fields on Master, and 2 on Details, but you can use others setup ... your case I think that you needs a 3 fields on Master to fill 3 fields on Details! ...then, 3 indexes or 1 index with this 3 fields grouped!!! (not so good this way) Share this post Link to post
Stano 143 Posted March 3, 2023 I have a policy that if the user is in Insert/Edit mode, they can't "click" anywhere else. In other words. I will not allow him to take an action that would interfere with working on a record. Share this post Link to post
PeterBelow 238 Posted March 3, 2023 2 hours ago, Stano said: I have a policy that if the user is in Insert/Edit mode, they can't "click" anywhere else. In other words. I will not allow him to take an action that would interfere with working on a record. Very sensible policy, it saves you from serious headaches. Share this post Link to post
PenelopeSkye 1 Posted March 7, 2023 I was working with Master\Detail but I was seriously out of time when someone pointed out that there is already a function the refreshes the data used by the app. I used that function and it works perfectly 🤦♀️ I am still going to work with Master\Detail in the future cause now I know about it I want to figure it out! Thanks James! Share this post Link to post
programmerdelphi2k 237 Posted March 7, 2023 (edited) bang! bang! stay closed with me, baby! 😂 Edited March 7, 2023 by programmerdelphi2k Share this post Link to post