Serge_G

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

Perhaps because you define your fields and let the parameter required value to true 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 ? 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]);

Agree 100%

Ah, I was not so clear. FDPhyFBDriver is not compatible with Android and IOS. It's not a FB version problem. I use only FBclient on Android (Firebird 4 or 3 no matter) and connect to a Fb 2.5 server (without any problem) but only with ZEOSDBO components. Vandrovnik change some part of IBX.IBIntf.pas to access Firebird instead of Interbase (see this discussion) I am not so optimist I think (and really prefer Firedac)

There are 2 ways to use Firebird : Firebird Client/Server - You have to deploy the DB and Firebird on the other pc Firebird Embedded - You have to put some files and directories in the same directory than application For a multi user Firebird app, it's quite the same approach except you deploy only client part, with the Firebird Setup it's easy, embedded you need at least fbclient.dll and perhaps firebird.msg files in the same dir that the app. Recommendation : Don't copy the DB file, uses backup/restore. Use a GUI Tool like Flamerobin

Hi one parenthesis to delete in the first line dmTaS.query_JobCard.SQL.Add('Insert into JobCard'); Lacking of a parenthesis after 'Values' dmTaS.query_JobCard.SQL.Add('Values (:ID :OrderNo, :JobName)'); Consider other solutions to write SQL to be more clear, i.e. const ISQL = 'INSERT INTO JobCard (ID ,OrderNo ,JobName) VALUES ( :ID :OrderNo, :JobName)'; // the "good" SQL for INSERT begin dmTaS.query_JobCard.Close; dmTaS.query_JobCard.SQL.Text:=ISQL; dmTaS.query_JobCard.Parameters.ParamByName('ID').Value := lb_ID.Text; dmTaS.query_JobCard.Parameters.ParamByName('OrderNo').Value := lb_OrderNo.Text; dmTaS.query_JobCard.Parameters.ParamByName('JobName').Value := lb_JobName.Text; dmtas.query_JobCard.ExecSQL; // dmTaS.query_JobCard.SQL.Clear; // unnecessary you use SQL.Text dmTaS.query_JobCard.SQL.Text := 'Select ID ,OrderNo ,JobName from JobCard'; // let's say I prefer list of columns needed rater tha a * dmTaS.query_JobCard.Open; Under this form, it's easier to maintain IMHO

Hi, still in experimentation. We have one woocommerce site, but traffic is low and interfacing (integrating orders/updating stocks) with my Delphi ERP is not a high priority. So I only made some connection and query tests

Interesting, I certainly prefer Firedac (why is the FDPhysFBdriver is not compatible is to me largely incomprehensible) and never think to use IBX instead 😲! I have tried other third party components to reach a Firebird DataBase like ZEOSDBO or UNIDAC, succesfully (even if deploying firebird on Androïd is really challenging the firsts times )

Yes, acting so even if you change stylebook at run time this custom style is independent Yes, even if it's not so easy, you have to change about three PNG images (dpi necessity) . I remember a video by Sarina Dupont about using Adobe and Stencils to change colors (see this tool) sorry, I don't found the link and many others interventions (videos, blogs) about bitmap designer tool Basically, using bitmap designer tool : save image , edit images with whatever tool you have, and then return to bitmap designer, load the new image (hoping this should not change regions defined, I never check this) Yes, you should say : "but bitmap designer is for VCL" (VSF file) but it's easy to save it as a FMX style I guess increasing the size of the image, putting the desired drawing outside the already defined areas will do the trick