Jump to content
Sign in to follow this  
PenelopeSkye

Refresh dbedit after insert

Recommended Posts

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

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 by programmerdelphi2k

Share this post


Link to post

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

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
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!!!

bds_zaDMsb3l6y.gif

Edited by programmerdelphi2k

Share this post


Link to post

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

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
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. :classic_dry:

Share this post


Link to post

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

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×