* * *

Author Topic: Three Grids - Three Tables with Foreign Keys  (Read 296 times)

Landslyde

  • Full Member
  • ***
  • Posts: 140
Three Grids - Three Tables with Foreign Keys
« on: June 12, 2018, 05:35:34 am »
It pains me to come here because I know I'm the least knowledgeable guy in the Forum. But I need help with this.

This is master/detail relationship stuff. In the pic below, you'll see three grids. Top: master. Other two are for details. I can make all of this work, but it's really clumsy, like a first-grader did it.

Top grid has a field client_id. I use that as a FK in the other two detail grids. The second grid will hold a recipe name and client_id. And the third will hold the many ingredients for that recipe. By using the grids for all the data entry, I'm having trouble sending the client_id from grid1 to the other two grids. This is what I have that isn't working:
Code: Pascal  [Select]
  1. procedure TForm1.SQLQuery2AfterPost(DataSet: TDataSet);
  2. begin
  3.   SQLQuery2.Open;
  4.   SQLQuery2.FieldByName('client_id').AsInteger:=SQLQuery1.FieldByName('client_id').AsInteger;
  5.   SQLQuery2.ApplyUpdates;
  6.   SQLTransaction1.CommitRetaining;
  7. end;
The error I get is:
Code: Pascal  [Select]
  1. SQLQuery2: Field recipe_id is required but not supplied
So to better understand that, here are the tables:
Code: Pascal  [Select]
  1. SQLQuery1.SQL.Text:='CREATE TABLE IF NOT EXISTS clients('+
  2.                     'client_id INT AUTO_INCREMENT,'+
  3.                     'client_email VARCHAR(50),'+
  4.                     'client_password VARCHAR(50),'+
  5.                     'PRIMARY KEY(client_id));';
  6.   SQLQuery1.ExecSQL;
  7.  
  8.   SQLQuery1.SQL.Text:='CREATE TABLE IF NOT EXISTS recipes('+
  9.                     'recipe_id INT PRIMARY KEY,'+
  10.                     'client_id INT,'+
  11.                     'recipe_name VARCHAR(50),'+
  12.                     'FOREIGN KEY(client_id) REFERENCES clients(client_id) '+
  13.                     'ON DELETE CASCADE);';
  14.   SQLQuery1.ExecSQL;
  15.  
  16.   SQLQuery1.SQL.Text:='CREATE TABLE IF NOT EXISTS ingredients('+
  17.                     'ingredient_id INT PRIMARY KEY,'+
  18.                     'recipe_id INT,'+
  19.                     'ingredient_name VARCHAR(50),'+
  20.                     'amount FLOAT,'+
  21.                     'FOREIGN KEY(recipe_id) REFERENCES recipes(recipe_id) '+
  22.                     'ON DELETE CASCADE);';
  23.   SQLQuery1.ExecSQL;
recipe_id is suppose to be added automatically, but it isn't. And I know it's because I'm interfering with the ApplyUpdates by trying to make sure the client_id gets added to SQLQuery2. If you understand what I'm talking abt and can help, I appreciate you.
« Last Edit: June 12, 2018, 07:10:12 am by Landslyde »
Linux Mint 18.3
Lazarus 1.8.4
FPC 3.0.4

Landslyde

  • Full Member
  • ***
  • Posts: 140
Re: Three Grids - Three Tables with Foreign Keys
« Reply #1 on: June 12, 2018, 06:37:26 am »
I think I fixed it.

In the recipes and ingredients tables, I added AUTO_INCREMENT to recipe_id and ingredient_id fields.

Then I changed to this:
Code: Pascal  [Select]
  1. procedure TForm1.SQLQuery2AfterInsert(DataSet: TDataSet);
  2. begin
  3.   SQLQuery2.Open;
  4.   SQLQuery2.FieldByName('client_id').AsInteger:=SQLQuery1.FieldByName('client_id').AsInteger;
  5.   SQLQuery2.ApplyUpdates;
  6.   SQLTransaction1.CommitRetaining;
  7. end;
  8.  
  9. procedure TForm1.SQLQuery2AfterPost(DataSet: TDataSet);
  10. begin
  11.   SQLQuery2.Open;
  12.   SQLQuery2.ApplyUpdates;
  13.   SQLTransaction1.CommitRetaining;
  14. end;
The first procedure adds the client_id to the recipes table, and the second procedure writes the rest of the data to the db.

While this is working for me, is it the right way? Or did I just get lucky and figure out something that was never meant to be that somehow works?  :D
Linux Mint 18.3
Lazarus 1.8.4
FPC 3.0.4

mangakissa

  • Hero Member
  • *****
  • Posts: 796
Re: Three Grids - Three Tables with Foreign Keys
« Reply #2 on: June 12, 2018, 08:42:15 am »
Your afterpost and afterinsert can not work.
If SQLQuery2 field receives data from SQLQuery1 it must be in the edit mode. If not, an error occurs.
In de Afterpost there are no records to apply when you opening the table.

Which database are you using? SQLite?
Lazarus 1.64 (32b) / FPC 3.0
Windows 10

Landslyde

  • Full Member
  • ***
  • Posts: 140
Re: Three Grids - Three Tables with Foreign Keys
« Reply #3 on: June 12, 2018, 11:52:19 am »
Quote
Which database are you using?
MariaDB

And I'm clicking the '+' on the TDBNavigator for the second TDBGrid before adding data. This is experimenting on my part and the second Grid is the only one I'm working with. And my sole objective there is to insert the client_id from Grid1 into Grid2 when I click the add sign '+'. And that works for me now. Then I add a recipe name and click POST. And that also works. I can kill the program and start it up again and my entered data is there.

The thing is, I'm trying hard as heck to understand the ins and outs of the TDBGrid and the TDBNavigator. I just got lucky (I guess) when I found this combination last night that actually seemed to work. I do get the desired result...but I don't know if the code
Code: Pascal  [Select]
  1. procedure TForm1.SQLQuery2AfterInsert(DataSet: TDataSet);
  2. begin
  3.   SQLQuery2.Open;
  4.   SQLQuery2.FieldByName('client_id').AsInteger:=SQLQuery1.FieldByName('client_id').AsInteger;
  5.   SQLQuery2.ApplyUpdates;
  6.   SQLTransaction1.CommitRetaining;
  7. end;
  8.  
  9. procedure TForm1.SQLQuery2AfterPost(DataSet: TDataSet);
  10. begin
  11.   SQLQuery2.Open;
  12.   SQLQuery2.ApplyUpdates;
  13.   SQLTransaction1.CommitRetaining;
  14. end;
is legitimate. I can't find any resources online that shows how to use the TDBGrid and TDBNavigtor together. So I'm just winging it for the most part. And winging it with code ain't cool.
« Last Edit: June 12, 2018, 12:27:03 pm by Landslyde »
Linux Mint 18.3
Lazarus 1.8.4
FPC 3.0.4

 

Recent

Get Lazarus at SourceForge.net. Fast, secure and Free Open Source software downloads Open Hub project report for Lazarus