Serge_G

Why ! ?  It's not like VCL has no numerous other Grids purposed by tiers

1- It's possible but not the correcr approach

3- only one connection

2- use a parametrized query like SELECT * FROM <table> WHERE <fieldname>=:<fieldname of AdoQuery1> (note : really not easy without the relation fieldnames !)

    and set Adoquery1.mastersource to datasource2  

Yes that's also a way, but I was attempting to use SQLite functions defined in  UFonctionSQlite.pas 

see chapter Extending SQLite Engine/Custom Functions  https://docwiki.embarcadero.com/RADStudio/Sydney/en/Using_SQLite_with_FireDAC

I also go to explore collation in the same chapter but not in the github program

1 hour ago, limelect said:

@Serge_G

While running I got date encode error recovery error

D10.2.3 maybe because our date is different? 

Oh, databasename was not supposed to be changed (autocreation)
And no, it's not a Delphi version problem it was the challenge, using a "text date" ( 'AAAAMMJJ' *) and getting a date value from a "SQLite function" (in " " because using Firedac SQLite functions)

* IMHO a really bad Patrick Premartin's habit

Here is what I call a script, at bottom the solution you need even if I really dislike all those sub-querys
 

CREATE TABLE ITEMS
(
  ID BIGINT GENERATED BY DEFAULT AS IDENTITY  NOT NULL,
  ITEMNO VARCHAR(20) NOT NULL,
  BOMCOST NUMERIC(15,5),
  CONSTRAINT PK_ITEMS PRIMARY KEY (ID)
);

CREATE UNIQUE INDEX IDX_ITEMSCODE ON ITEMS (ITEMNO);

CREATE TABLE VENDOR   -- bad name here should be VENDOR_ITEMS  somewhere necessits a TABLE VENDOR (id BIGINT GENERATED BY DEFAULT AS IDENTITY  NOT NULL,NAME VARCHAR(80)) 
(
  ID INTEGER GENERATED BY DEFAULT AS IDENTITY  NOT NULL,
  ITEMID BIGINT,
  ITEMNO VARCHAR(20) NOT NULL,
  CUSTNO INTEGER NOT NULL,
  PREFERRED VARCHAR(3),
  PRICE NUMERIC(8,2),
  CONSTRAINT PK_VENDOR PRIMARY KEY (ID)
);

ALTER TABLE VENDOR ADD CONSTRAINT FK_VENDOR_ITEM
  FOREIGN KEY (ITEMID) REFERENCES ITEMS (ID) ON UPDATE CASCADE ON DELETE CASCADE;
CREATE UNIQUE INDEX IDX_VENDORITEMS ON VENDOR (ITEMNO,CUSTNO);

COMMIT; 
-- some test datas
INSERT INTO VENDOR (ID, ITEMID, ITEMNO, CUSTNO, PREFERRED, PRICE) VALUES ('2', '1', '1', '1', 'Yes', '100.00');
INSERT INTO VENDOR (ID, ITEMID, ITEMNO, CUSTNO, PREFERRED, PRICE) VALUES ('3', '1', '1', '2', 'No', '150.00');
INSERT INTO VENDOR (ID, ITEMID, ITEMNO, CUSTNO, PREFERRED, PRICE) VALUES ('4', '2', '2', '2', 'Yes', '150.00');
INSERT INTO VENDOR (ID, ITEMID, ITEMNO, CUSTNO, PREFERRED, PRICE) VALUES ('6', '3', '3', '4', NULL, '10.00');
INSERT INTO VENDOR (ID, ITEMID, ITEMNO, CUSTNO, PREFERRED, PRICE) VALUES ('7', '3', '3', '5', 'No', '12.00');

INSERT INTO ITEMS (ID, ITEMNO, BOMCOST) VALUES ('1', '1', '0.00000');
INSERT INTO ITEMS (ID, ITEMNO, BOMCOST) VALUES ('2', '2', '0.00000');
INSERT INTO ITEMS (ID, ITEMNO, BOMCOST) VALUES ('3', '3', '0.00000');

COMMIT;
-- Now updating
UPDATE ITEMS a
SET a.BOMCOST = (SELECT PRICE FROM VENDOR WHERE ITEMNO = a.ITEMNO and PREFERRED='Yes') 
WHERE EXISTS (SELECT 1 FROM VENDOR WHERE ITEMNO = a.ITEMNO and PREFERRED='Yes')   
  AND a.ITEMNO=(SELECT ITEMNO FROM VENDOR WHERE ITEMNO = a.ITEMNO and PREFERRED='Yes')
COMMIT;

result for  SELECT r.ID, r.ITEMNO, r.BOMCOST FROM ITEMS r

Quote

ID    ITEMNO    BOMCOST
1    1                100.00000
2    2                150.00000
3    3                    0.00000

I keep using your ITEMNO and CUSTNO strings, but it's really a bad habit. Try to apply tips upper to have a better database

What a horrid structure !
Before going further, some tips :
Don't use VARCHAR as primary keys , take custom to have an id bigint value filled by a SEQUENCE or generated by default as identity

For the varchar keys create index  i.e  CREATE UNIQUE INDEX IDX_VENDORITEMS ON VENDOR  (ITEMNO, CUSTNO);

Don't use name ITEMNO if it's a VARCHAR, prefer ITEMCODE (prefix NO for numbers)
Don't forget to use Foreign keys when necessary

I'll soon write a better script
 

Another guess (not tested)  but still lack something

UPDATE ITEMS IT 
  SET 
  IT.BOMCOST = (SELECT FPRICE FROM VENDOR
                           WHERE ITEMNO = IT.ITEMNO and PREFERRED ='Yes')
  WHERE I.ITEMNO=(SELECT ITEMNO FROM VENDOR  WHERE ITEMNO=IT.ITEMNO and PREFERRED='Yes') 

Yes, the goal was to show you there is something missing in the where clause

Something like IT.ITEMNO= ????
This why I asked you

20 minutes ago, Serge_G said:

structure of tables ITEMS and VENDOR

Structure and Relations by evidence, And if you give a script to fill with some test datas, better

13 hours ago, limelect said:

So my main question is can someone  give me some link for source or guideline

well if you don't mind to read French 

I wrote a sample (with SQLite functions involved) here https://github.com/Serge-Girard/GestionComptesPersonnels (google trad readme.md for description but for comments inside pas file, should be a little more ctr+c/ctrl+v manipulations )

13 hours ago, limelect said:

While developing I found I get locked situation I could not get rid off.

This occurs if the database is used by 2 apps simultaneously. I run too often in that situation when debugging and having another GUI for SQLite still open, or it can occur when, during design, your data is open and your program asked for opening it  (I tend to set both  FDConnection.ConnectedStoredUsage  properties to false,  to avoid this problem)

Hi, without structure of tables ITEMS and VENDOR I should say that there is something missing in the where clause of the update  

a guess

UPDATE ITEMS IT 
  SET 
  IT.BOMCOST = (SELECT FPRICE FROM VENDOR
                           WHERE ITEMNO = IT.ITEMNO and PREFERRED ='Yes')
  WHERE EXISTS (SELECT 1 FROM VENDOR  WHERE ITEMNO=IT.ITEMNO and PREFERRED='Yes')   

Ok, I don't know this way was possible. So I explore more over, and you can also write something like
 

select * from myable where mycol like '%'||:a||'%'

Anyway, I still prefer the macro way to adjust the where clause if necessary

2 hours ago, Dany Marmur said:

Do not fret on this for my sake. 

Should just for my peace of mind, but tomorrow

No program writing, just an FdConnection and FDQuery and at design time I can confirm that a CONTAINING clause can work with a parameter 

7 minutes ago, Dany Marmur said:

What is preventing FireDAC users from using a parameter for the like clause?

Ah, I don't know why but every time I test the parameter for the LIKE clause it does not work (Firebird speaking here)

But, if Firebird speaking LIKE can be replaced by CONTAINING and this one, I think, never test myself, can be used with parameter 

22 hours ago, limelect said:

@Serge_G read my solution it is so simple no need for software

Which one ? I was only noticing that your query don't work because you forgot to put an alias name  in this SQL part
 

 FDQuery4.SQL.add('Select SectionsId,MainDirectory,MyDateTime,ProjectrealName,CAST(Category AS CHAR)  ,Description,FilesIndex'); 

by the way, your whole query

FDQuery4.SQL.add('Select SectionsId,MainDirectory,MyDateTime,ProjectrealName,Category,Description,FilesIndex');
  FDQuery4.SQL.add('from Files,Projects');
   FDQuery4.SQL.add('WHERE MainDirectory LIKE ''%' + jvDirectoryEdit1.Text + '%'''
    + 'and FilesIndex=SectionsId');

is an horid thing, did you ever ear about JOINtures, down a normalize SQL

SELECT SectionsId,MainDirectory,MyDateTime,ProjectrealName,CAST(Category AS VARCHAR(150)) Category ,Description,FilesIndex
FROM FILES JOIN PROJECT on FilesIndex=SectionsId

and Firedac parameters or macros ?

 FDQuery4.SQL.add('WHERE MainDirectory LIKE ''%' + jvDirectoryEdit1.Text + '%'''

this should be wrote  like this

FDQuery4.SQL.add('WHERE MainDirectory LIKE &amacro');
//usage, before openning query
FDQuery4.macrobyname('amacro').asRaw:=QuotedStr('%'+jvdirectoryEdit1.text+'%');
FDQuery4.open;

Or changing all the where clause like this

FDQuery4.SQL.add('&amacro');
//usage, before openning query
FDQuery4.macrobyname('amacro').asRaw:=Format('WHERE maindirectory LIKE %s',[QuotedStr('%'+jvdirectoryEdit1.text+'%')]);
FDQuery4.open;

20 hours ago, limelect said:

1. category not found

2. Error near from

 

I know this is why I need help

you need an alias column 

 CAST(substring(Category from 1 for 128) as varchar(128))  Category 

else, column should be known as CASTn (where n is a number)

Yes but easy to override. GDB for MSWindows is sort of backup if in "MS folders". 
But, aware of that I effectively use fdb or fb extension for my databases

Hi,

Suggestion : in the archive, don't put GDB file but a backup (fbk) of the database

Unfortunately, I have no Firebird 4 installed (well running to be clear)  to check 

You can also consider this way 

fdquery1.SQL

SELECT  r.ORDERNO, r.JOBNAME, r.STARTDATE, r.COMPLETIONDATE, r.AMOUNTEX,
    r.DESCRIPTION
FROM JOBCARD r ORDER BY r.ORDERNO

with fdupdateSQL sentences

DeleteSQL

DELETE FROM JOBCARD
WHERE ID = :OLD_ID

FetchRowSQL

SELECT ID, ORDERNO, JOBNAME, STARTDATE, COMPLETIONDATE, AMOUNTEX, DESCRIPTION
FROM JOBCARD
WHERE ID = :OLD_ID

insertSQL

INSERT INTO JOBCARD
(ORDERNO, JOBNAME, STARTDATE, COMPLETIONDATE,
  AMOUNTEX, DESCRIPTION)
VALUES (:NEW_ORDERNO, :NEW_JOBNAME, :NEW_STARTDATE, :NEW_COMPLETIONDATE,
  :NEW_AMOUNTEX, :NEW_DESCRIPTION)
RETURNING ID, ORDERNO, JOBNAME, STARTDATE, COMPLETIONDATE, AMOUNTEX, DESCRIPTION

modifySQL

UPDATE JOBCARD
SET ORDERNO = :NEW_ORDERNO, JOBNAME = :NEW_JOBNAME, STARTDATE = :NEW_STARTDATE,
  COMPLETIONDATE = :NEW_COMPLETIONDATE, AMOUNTEX = :NEW_AMOUNTEX,
  DESCRIPTION = :NEW_DESCRIPTION
WHERE ID = :OLD_ID
RETURNING ID, ORDERNO, JOBNAME, STARTDATE, COMPLETIONDATE, AMOUNTEX, DESCRIPTION

and just using Grid and Navigator
Or
 

procedure TForm23.Button1Click(Sender: TObject);
var abookmark : TBookMark;
begin
fdQuery1.DisableControls;
fdQuery1.Insert;
fdQuery1.FieldByName('oderno').AsString:=Edit1.Text;
fdQuery1.FieldByName('jobname').AsString:=Edit2.Text;
fdQuery1.FieldByName('StartDate').AsDateTime:=DateTimePicker1.Date;
fdQuery1.FieldByName('CompletionDate').AsDateTime:=DateTimePicker2.Date;
fdQuery1.FieldByName('amount').AsCurrency:=StrToFloatDef(Edit3.Text,0);
fdQuery1.Post;

// refreshing Grid
Abookmark:=fdquery1.GetBookmark;
fdQuery1.Open();
fdquery1.GotoBookmark(aBookmark);
// not sure of the above lines cause : open can change bookmarks methinks
fdQuery1.EnableControls;
end;

You can note here I give "good" type for data structure
 

CREATE TABLE JOBCARD
(
  ID INTEGER GENERATED BY DEFAULT AS IDENTITY  NOT NULL,
  ORDERNO VARCHAR(12),
  JOBNAME VARCHAR(100),
  STARTDATE DATE,
  COMPLETIONDATE DATE,
  AMOUNTEX NUMERIC(8,2),
  DESCRIPTION VARCHAR(50),  -- rename DESC (keyword) to DESCRIPTION
  CONSTRAINT PK_JOBCARD PRIMARY KEY (ID)
);

check your database and sequence value

3 minutes ago, taariq said:

Good day

I get runtime error? Sure I did something wrong - I did check my typing

 

Mine typing  it's ExecSQL or ExecSQLScalar

Why don't you use this in your code ?

 procedure TMainForm.Button1Click(Sender: TObject);
//declare my var to enter new job card
var sOrderNo, sJobName , sStartDate, sCompletionDate, sAmountEx: string ;
begin
sOrderNo := Inputbox ( 'OrderNo' ,   'Enter OrderNo'  , 'QU000'  ) ;
sJobName := Inputbox ( 'JobName' ,   'Enter JobName'  , 'Company Name'  ) ;
sStartDate := Inputbox ( 'StartDate' ,   'Enter Start Date'  , 'YYYY/MM/DD'  ) ;
sCompletionDate := Inputbox ( 'CompletionDate' ,   'Enter Completion Date'  , 'YYYY/MM/DD'  ) ;
dmTas.tblJobCard.Connection.ExeSQL('INSERT INTO jobCard (orderno,jobname,StartDate,Completiondate,amount) values (?,?,?,?,?)', [sOrderNo,sJobName,sStartDate,sCompletionDate,sAmountEx]);
// if you want to retrieve last id you can use ExecSQLScalar
//LID:=dmTas.tblJobCard.Connection.ExeSQL('INSERT INTO jobCard (orderno,jobname,StartDate,Completiondate,amount) values (?,?,?,?,?) RETRUNING ID into :id',  [sOrderNo,sJobName,sStartDate,sCompletionDate,sAmountEx]);
// put LID as private or public of the form
// do refresh Grid 
end;

Note : I really dislike those InputBox it's so easy to have DBEdits in a form

1 hour ago, taariq said:

But on my Delphi side it is still asking for valid ID?

Perhaps because you define your fields and let the parameter required value to true

27 minutes ago, taariq said:

Now I see that the sort by does not work and I have to do it by SQL..

Yes if the datasource of the grid is a Table I understand sort is not working, but one time it's depending on components you use 

On my side, I quite don't use FDTable, only FDQuery and FDUpdateObjects, but as I remember FDTable can be sorted using properties as IndexFieldName

PS. You don't indicate if you are on the VCL or the FMX side of Delphi and I don't remember seeing version you use
 

1- you don'd need any trigger if you use generated identitity

2- for date use date type or timestamp type
3- for amountex use something like  NUMERIC(8,2) if currency

Ok, it's because you change domain. Keep it to a somewhat RDB$anumber wich is the domain for the column. In brief, don't change what is purposed by default in this zone

Hi,

what a strange usage ! I don't understand the InputBox usage, so borring 
But first

Are you using Firedac or another set of components ? What version of Firebird did you install ?

18 hours ago, taariq said:

How would I setup an id field that is an auto number for ID as access db

There are at least two ways : using a SEQUENCE (aka GENERATOR) and a Trigger or using if FB3+

GENERATED BY DEFAULT AS IDENTITY (see this versions notes)

With flamerobin you can easily create the  GENERATOR/Trigger like this (create the table quick 'CREATE TABLE test        
( ID INTEGER NOT NULL PRIMARY KEY,
  Texte VARCHAR(30)
)' )
and then  dbclick to open properties of new table 

With SEQUENCE you don't have to take care about ID
your code

//get next ID
//dmTaS.tblJobCard.Sort := 'ID ASC'  ;
//dmTaS.tblJobCard.Last;
//iID : = dmTaS.tblJobCard['ID'] + 1 ;

//insert values to DBgrid
dmTaS.tblJobCard.Insert ;
// dmTaS.tblJobCard['ID' ] := iID ; let it to null, Firebird is in charge 
...
dmTaS.tblJobCard.post;

But, if you use Firedac I can suggest you more efficient coding
i.e
 

const InsertSQL= 'INSERT INTO jobCard (orderno,jobname,StartDate,Completiondate,amount) values (?,?,?,?,?.)';

begin   

dmTas.fdConnection.ExeSQL(InsertSQL, [sOrderNo,sJobName,sStartDate,sCompletionDate,sAmountEx]);