Error: cannot change or add record, because a related record is required in Delphi using MS SQL

Question

I have a program which creates components dynamically. It does this by having arrays of TPanel, tprogressbar and other components. These are created using an included index lI which copies the value of the most recent record's related field and includes it. this number is then saved into two tables namely tblGUI_p which saves the top position of these panels (Also included according to the last records related field) and tblDrives which is linked to tblGui via FK which is that index lI.

I'm receiving an error which seems as though I am breaking some referential rule "First chance exception at $7641D902. Exception class EOleException with message

You cannot add or change a record because a related record is required in table 'tblGUI_P''. Process Project1.exe (11136)

I'm not sure what to do.

Here is the code for the button which creates panels

unit DoneeSite;

interface


uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, ExtCtrls, StdCtrls, ComCtrls, Fundrasers, JPeg, Login, NPO_u;

type
  TForm7 = class(TForm)
    Panel1: TPanel;
    Panel2: TPanel;
    cbDt: TComboBox;
    dpStart: TDateTimePicker;
    dpEnd: TDateTimePicker;
    Memo1: TMemo;
    ImagePreview: TImage;
    edtAmount: TEdit;
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    Label4: TLabel;
    Label6: TLabel;
    btnCD: TButton;
    Button1: TButton;
    OpenDialog1: TOpenDialog;
    btnImg: TButton;
    procedure btnCDClick(Sender: TObject);
    procedure Button1Click(Sender: TObject);
    procedure btnImgClick(Sender: TObject);
    procedure pnlClick(Sender: Tobject);
  private
    { Private declarations }
    dImage:Tstream;
    objN:Torg;
  public
    { Public declarations }
    arrP:array[1..100] of TPanel;
    arrImj:array[1..100] of TImage;
    arrPro:array[1..100] of TProgressbar;
    arrLabelAmount:array[1..100] of Tlabel;
    arrLabelDriveType:array[1..100] of Tlabel;
    arrLabelProv:array[1..100] of Tlabel;
    arrLabelOrg:array[1..100] of Tlabel;
    i,ipos:integer;

  end;

var
  Form7: TForm7;


implementation

uses Connectdb, NPORegister;

{$R *.dfm}

procedure TForm7.btnCDClick(Sender: TObject);
var
li,newNum:integer;
dtype,dMsg,dcode,Organisation,Province:string;
sdate,edate:tdate;
tAmount:real;

const
pW=760;
pH=150;
pL=15;
begin

//initialisation;
dtype:=cbDt.Text;
sdate:=dpstart.Date;
edate:=dpend.Date;

dMsg:=memo1.Lines.Text;
//null check
if (dtype='') or (edtamount.Text='') or (dmsg='') or (dimage.ToString='') then
begin
  showmessage('Complete all fields before advancing');
end
else
begin
tamount:=strtoint(edtamount.Text);

//creating drive code
   //SQL to get last record
with dmconnect do
          begin
           qryOrg.SQL.Clear;
           qryOrg.SQL.Add('Select * from tblDrives ORDER BY DriveCode DESC');
           qryOrg.open;
           dcode:=qryOrg.FieldByName('DriveCode').asString;
           qryOrg.Close;

          end;
        newNum:=strtoint(copy(dcode,2,length(dcode)-1))+1;
        dcode:='D'+inttostr(newNum);
        //SQL to fetch details of Organisation
     //instantiation of object
        objn:= torg.Create;
        organisation:=objn.getName;
        province:=objn.getProvince;
//SQL to load details to database
   with dmconnect do
          begin
           qryOrg.SQL.Clear;
           qryOrg.SQL.Add('Insert into tblDrives (DriveCode,DriveType,StartDate,EndDate,CRNumber,dimage,dinfo,TargetAmount,PanelN)');
         qryOrg.SQL.add('Values ("'+dcode+'","'+dtype+'","'+datetostr(sdate)+'","'+datetostr(edate)+'","'+login.login_f.usr+'","'+dimage.ToString+'","'+dmsg+'","'+floattostrf(tamount,ffcurrency,6,2)+'","'+inttostr(li)+'")');
           qryOrg.ExecSQL;
          end;


//CODE FOR CREATING GRIDS
//gets last record
 with dmconnect do
          begin
           qryOrg.SQL.Clear;
           qryOrg.SQL.Add('Select * from tblGUI_P ORDER BY PanelN DESC');
           qryOrg.open;
           lI:=qryOrg.FieldByName('PanelN').AsInteger;
           ipos:=qryOrg.FieldByName('pTop').AsInteger;
           qryOrg.Close;

          end;

//index
lI:=lI+1;
//updating position
ipos:=ipos+200;
 if li=1 then
 begin
   ipos:=5;
 end;
  with dmconnect do
  begin
    qryOrg.SQL.Clear;
    qryOrg.SQL.Add('INSERT INTO tblGUI_P (PanelN,pTop,pHeight,pWidth)');
    qryOrg.SQL.Add('VALUES ("'+inttostr(li)+'","'+inttostr(ipos)+'","'+inttostr(pH)+'","'+inttostr(pW)+'")');
    qryOrg.ExecSQL;

  end;
  //create panel dynamically
arrP[li]:=tpanel.Create(self);
with arrP[li] do
begin

  parent:=fundrasers.Form6.ScrollBox1;
  width:=round(fundrasers.Form6.ScrollBox1.ClientWidth*(98/100));
  height:=pH;
  left:=pL;
  top:= ipos;
  cursor:=crhandpoint;
  onclick:=pnlclick;


end;
 //create image dynamically
arrImj[li]:=timage.Create(self);
with arrimj[li] do begin
  parent:=arrP[li];
  left:=608;
  height:=105;
  width:=105;
  top:=16;
  stretch:=true;
  picture.loadfromfile(opendialog1.FileName)

end;
//create progress bar dynamically
arrPro[li]:=tprogressbar.Create(self);
with arrpro[li] do
begin
  parent:=arrp[li];
  top:=64;
  width:=180;
  height:=17;
  left:=352;
end;
//create label for amount dynamically
arrLabelAmount[li]:=tlabel.Create(self);
with arrLabelAmount[li] do
begin
  parent:=arrP[li];
  top:=40;
  left:=365;
  caption:='R0 Raised of'+' '+floattostrf(tAmount,ffcurrency,6,2);
end;
//create label for Drive type dynamically
arrLabelDrivetype[li]:=tlabel.Create(self);
with arrLabelDrivetype[li] do
begin
  parent:=arrP[li];
  top:=40;
  left:=165;
  caption:=dtype;
end;
arrLabelProv[li]:=tlabel.Create(self);
with arrLabelProv[li] do
begin
  parent:=arrP[li];
  top:=75;
  left:=165;
  caption:=province;
end;
arrlabelOrg[li]:=tlabel.Create(self);
with arrlabelOrg[li] do
begin
  parent:=arrP[li];
  top:=100;
  left:=165;
  font.Size:=6;
  caption:='By: '+organisation;
end;


end;//end of null check







end;

procedure TForm7.Button1Click(Sender: TObject);
begin
form7.Hide;
form6.Show;
end;

procedure TForm7.pnlClick(Sender: Tobject);
var
ptest:tpanel;
pdat:string;
begin
 ptest:=TPanel(Sender);
 pdat:= inttostr(ptest.Top);
 showmessage(pdat);
end;

procedure TForm7.btnImgClick(Sender: TObject);
var
filename:string;
begin
if OpenDialog1.Execute then
  begin
    // Get the selected file name
     FileName := OpenDialog1.FileName;

    // Check if the selected file is an image (optional, depending on your needs)
    if (LowerCase(ExtractFileExt(FileName)) <> '.bmp') and
        (LowerCase(ExtractFileExt(FileName)) <> '.jpg') and
        (LowerCase(ExtractFileExt(FileName)) <> '.jpeg') and
        (LowerCase(ExtractFileExt(FileName)) <> '.png') then
    begin
      ShowMessage('Please select a valid image file (BMP, JPG, JPEG, or PNG).');
      Exit;
    end;

    // Load the selected image into the TImage component for preview
    ImagePreview.Picture.LoadFromFile(FileName);
     //converting image to binary soo it can be saved to data base
    dimage:= tmemorystream.Create;
    imagepreview.Picture.Graphic.SaveToStream(dimage);
  end;
end;

end.

I should also mention that I made some for of sub-program which is supported to use 3 linked tables to get relevant data and this is a school project so please don't mind my messy code

unit NPO_u;

interface
uses
ConnectDb,Login, Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, Unit2, Unit1, ExtCtrls,Db, ADODB;
type TOrg = class(TObject)



private
 FDMConnect: TADOConnection;



public
function getName: string;
function getProvince:string;
function getLastindex:integer;
function getTopPosition:integer;
function getCompanyName(index:integer):string;




 end;

implementation

{ TOrg }



function TOrg.getCompanyName(index: integer): string;
begin
with dmconnect do
begin
  qryorg.SQL.Clear;
  qryorg.SQL.Add('SELECT *FROM tblGui, tblDrives, tblOrgs');
qryorg.SQL.Add('WHERE tblGui.PanelN = tblDrives.PanelN');
qryorg.SQL.Add('AND tblDrives.CRNumber = tblOrgs.CRNumber');
qryorg.SQL.Add('AND tblGui.PanelN = "'+ inttostr(index) +'")');
qryorg.Open;
result:=qryorg.FieldByName('Company Name').AsString;
qryorg.close;

end;
end;

function TOrg.getLastindex: integer;
begin
 with dmconnect do
          begin

           qryOrg.SQL.Clear;
           qryOrg.SQL.Add('Select * from tblGUI_P ORDER BY PanelN DESC');
           qryOrg.open;
           result:=qryOrg.FieldByName('PanelN').AsInteger;
qryOrg.Close;

          end;
end;

function TOrg.getName: string;
begin
with dmconnect do
begin
  qryOrg.SQL.Clear;
           qryOrg.SQL.Add('Select * from tblOrgs WHERE CRNumber="'+login.login_f.usr+'"');
           qryOrg.open;
           result:=qryOrg.FieldByName('Company Name').asString;
           qryOrg.Close;
end;
end;



function TOrg.getProvince: string;
begin
 with dmconnect do
begin
  qryOrg.SQL.Clear;
           qryOrg.SQL.Add('Select * from tblOrgs WHERE CRNumber="'+login.login_f.usr+'"');
           qryOrg.open;
           result:=qryOrg.FieldByName('Province').asString;
           qryOrg.Close;
end;
end;




function TOrg.getTopPosition: integer;
begin
 with dmconnect do
          begin
           qryOrg.SQL.Clear;
           qryOrg.SQL.Add('Select * from tblGUI_P ORDER BY PanelN DESC');
           qryOrg.open;
           result:=qryOrg.FieldByName('pTop').AsInteger;
           qryOrg.Close;

          end;
end;

end.

Here is where the functions from the sub program is being used is being used

unit Fundrasers;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, ExtCtrls, ComCtrls,NPO_u;

type
  TForm6 = class(TForm)
    Panel1: TPanel;
    Label1: TLabel;
    ScrollBox1: TScrollBox;
    Button1: TButton;
    Timer1: TTimer;
    procedure Button1Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure Timer1Timer(Sender: TObject);
  private
    { Private declarations }
    objn:Torg;
  public
    { Public declarations }
      arP:array[1..100] of TPanel;
    arImj:array[1..100] of TImage;
    arPro:array[1..100] of TProgressbar;
    arLabelAmount:array[1..100] of Tlabel;
    arLabelDriveType:array[1..100] of Tlabel;
    arLabelProv:array[1..100] of Tlabel;
    arLabelOrg:array[1..100] of Tlabel;
  end;

var
  Form6: TForm6;
  arrPnl:array[1..100] of tpanel;

implementation

uses DoneeSite, Connectdb;

{$R *.dfm}

procedure TForm6.Button1Click(Sender: TObject);
begin
form6.hide;
form7.show;

end;

procedure TForm6.FormCreate(Sender: TObject);
var
x:integer;
begin
timer1.Enabled:=true;
if Timer1.Tag = 10 then
  begin


objn:=torg.Create;

for x := 1 to objn.getLastindex do
begin
arP[x]:=tpanel.Create(self);
with arp[x] do
begin
   parent:=fundrasers.Form6.ScrollBox1;
  width:=round(fundrasers.Form6.ScrollBox1.ClientWidth*(98/100));
  height:=150;
  left:=15;
  top:= objn.getTopPosition;
  cursor:=crhandpoint;
  onclick:=doneesite.Form7.pnlClick;

end;
end;
  end;//end of timer

end;

procedure TForm6.Timer1Timer(Sender: TObject);
begin
Timer1.Tag := Timer1.Tag + 1; // Increment the Tag by 1 every second


end;

end.

I have also included an image of the relationships

I tried adding related record to the tables to avoid a situation where there are no related fields, but it still refused to work properly

Answer 1

I think that the problem is the population of your tables. From the picture seems that tblGUI_P is linked with tblDrives by the PanelN parameter. If it is, the sequence of your code fill data to tblDrives and then into tblGUI_P where PanelN is primary key. Doing this

   with dmconnect do
   begin
     qryOrg.SQL.Clear;
     qryOrg.SQL.Add('Insert into tblDrives (DriveCode,DriveType,StartDate,EndDate,CRNumber,dimage,dinfo,TargetAmount,PanelN)');
     qryOrg.SQL.add('Values ("'+dcode+'","'+dtype+'","'+datetostr(sdate)+'","'+datetostr(edate)+'","'+login.login_f.usr+'","'+dimage.ToString+'","'+dmsg+'","'+floattostrf(tamount,ffcurrency,6,2)+'","'+inttostr(li)+'")');
     qryOrg.ExecSQL;
   end;

You are trying to insert a new row into table tblDrives with the "inttostr(li)" not initialized or equal to 0 but the relations need to get an existing data from tblGUI_P.

You need to insert a row with a grid into tblGUI_P and then insert a new row to tblDrives with reference to the new PanelN

1

//CODE FOR CREATING GRIDS
//gets last record
 with dmconnect do
 begin
    qryOrg.SQL.Clear;
    qryOrg.SQL.Add('Select * from tblGUI_P ORDER BY PanelN DESC');
    qryOrg.open;
    lI:=qryOrg.FieldByName('PanelN').AsInteger;
    ipos:=qryOrg.FieldByName('pTop').AsInteger;
    qryOrg.Close;
end;

//index
lI:=lI+1;
//updating position
ipos:=ipos+200;
if li=1 then
begin
   ipos:=5;
end;
with dmconnect do
begin
   qryOrg.SQL.Clear;
   qryOrg.SQL.Add('INSERT INTO tblGUI_P (PanelN,pTop,pHeight,pWidth)');
   qryOrg.SQL.Add('VALUES ("'+inttostr(li)+'","'+inttostr(ipos)+'","'+inttostr(pH)+'","'+inttostr(pW)+'")');
   qryOrg.ExecSQL;
end;

---

2 where li is already initialized with last grid

//creating drive code
//SQL to get last record
with dmconnect do
begin
   qryOrg.SQL.Clear;
   qryOrg.SQL.Add('Select * from tblDrives ORDER BY DriveCode DESC');
   qryOrg.open;
   dcode:=qryOrg.FieldByName('DriveCode').asString;
   qryOrg.Close;
end;
newNum:=strtoint(copy(dcode,2,length(dcode)-1))+1;
dcode:='D'+inttostr(newNum);
//SQL to fetch details of Organisation
//instantiation of object
objn:= torg.Create;
organisation:=objn.getName;
province:=objn.getProvince;
//SQL to load details to database
with dmconnect do
begin
   qryOrg.SQL.Clear;
   qryOrg.SQL.Add('Insert into tblDrives (DriveCode,DriveType,StartDate,EndDate,CRNumber,dimage,dinfo,TargetAmount,PanelN)');
   qryOrg.SQL.add('Values ("'+dcode+'","'+dtype+'","'+datetostr(sdate)+'","'+datetostr(edate)+'","'+login.login_f.usr+'","'+dimage.ToString+'","'+dmsg+'","'+floattostrf(tamount,ffcurrency,6,2)+'","'+inttostr(li)+'")');
   qryOrg.ExecSQL;
end;

//CODE FOR CREATING GRIDS
//gets last record
with dmconnect do
begin
   qryOrg.SQL.Clear;
   qryOrg.SQL.Add('Select * from tblGUI_P ORDER BY PanelN DESC');
   qryOrg.open;
   lI:=qryOrg.FieldByName('PanelN').AsInteger;
   ipos:=qryOrg.FieldByName('pTop').AsInteger;
   qryOrg.Close;
end;

---

Hope to help you Giansi87