Serge_G

Hum, take care, install is a bit buggy if you don't use the "standard" install path for Delphi https://quality.embarcadero.com/browse/RSP-36785 installed you have to install the package dclbde280.bpl in the C:\Program Files (x86)\Embarcadero\Studio\22.0\bin

Forgot this one, better IMHO, for Firebird<3 using a CTE and JOIN WITH c as (Select count(1) nb from appose) select c.nb,t.* from c full join appose t on 1=1

First what a strange query, a count without Grouping clause ! Try this (before fb 3, solution). Really disliking this "subselect" ! SELECT (SELECT COUNT(1) FROM ATABLE) AS TOT,DOCNO,DOCTYPE FROM ATABLE Or use a window (analytical) function (here SUM(1) OVER () is the window function) (Wow now FB3+ have analytical functions 😲) SELECT SUM(1) OVER () FULLCOUNT, DOCNO,DOCTYPE from ATABLE

@Dalija Prasnikar solution is also my way to solve this recurrent problem.

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

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 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 Structure and Relations by evidence, And if you give a script to fill with some test datas, better

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

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

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

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;

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

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