Refresh dbedit after insert

[PenelopeSkye 1]

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 ;
  
       
  

[programmerdelphi2k 237]

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

[PenelopeSkye 1]

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!

 

[programmerdelphi2k 237]

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

 

[PenelopeSkye 1]

Ooh!  I will try closing and opening first!

[programmerdelphi2k 237]

no no no... use refresh!!! it's almost the same.

[programmerdelphi2k 237]

I think your case is a MASTER-DETAIL case! solve it

[programmerdelphi2k 237]

Bond... James Bond. 🤣

[PenelopeSkye 1]

I will go back to studying the master detail thingee!  Thanks Mr. Bond!

[programmerdelphi2k 237]

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

[PenelopeSkye 1]

I am still getting errors but I will start up again Monday!  I will solve it!

[programmerdelphi2k 237]

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)

[Stano 143]

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.

[PeterBelow 238]

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.

[PenelopeSkye 1]

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!

[programmerdelphi2k 237]

bang! bang!  stay closed with me, baby!

😂

Edited March 7, 2023 by programmerdelphi2k

[PenelopeSkye 1]

LOL!!! Will do!