Syntax error in From clause

[taariq 0]

Good day

Please assist, I am a newbie

I am trying to save data to an Access database...
Keep getting the following error
Syntax error in From clause

 

Code below

 

procedure TMainForm.b_save_new_recorsClick(Sender: TObject);
begin
dmTaS.query_JobCard.SQL.Add('Insert into JobCard(');
dmTaS.query_JobCard.SQL.Add('(ID ,OrderNo ,JobName)');
dmTaS.query_JobCard.SQL.Add('Values :ID :OrderNo, :JobName)');

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;
dmTaS.query_JobCard.SQL.Text := 'Select * from JobCard';
dmTaS.query_JobCard.Open;

 

I added the following in the TADOQuery under SQL
 SELECT * FROM JobCard

 

Thank you

 

Edited February 18, 2022 by taariq

[Serge_G 87]

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

Edited February 19, 2022 by Serge_G

[haentschman 92]

Hi...welcome here.

 

First:

Quote

data to an Access database

...why ACCESS and ADO? 🤢 ...😉

 

For the future: can you change the database? 😉

 

const ISQL = 'INSERT INTO JobCard (ID ,OrderNo ,JobName)  VALUES ( :ID :OrderNo, :JobName)';

...better than the ADD orgy. It is even better to store the SQL outside the "form" or "datamodule" as SQL file. 😉

Quote

I am a newbie

...i know. No problem. Learning by doing... 😉

[Stano 143]

Such a small tool

class function TFDFunction.TextInsertInto(const ATableName: string; AllFields: Boolean = False): string;
var
  FTable: TFDTable;
  sFields, sValues: string;
  Field1: Integer;
begin
  FTable := TFDFunction.NewTable(nil, ATableName);

  if AllFields then
    Field1 := 0
  else
    Field1 := 1;

  try
    // Without primary key. It is auto increment.
    sFields := ATableName + ' ([' + FTable.FieldList[Field1].FullName + ']';
    sValues := ' VALUES (:' + FTable.FieldList[Field1].FullName;

    for var I := Field1 + 1 to FTable.FieldList.Count - 1 do
    begin
      sFields := sFields + ', [' +  FTable.FieldList[I].FullName+ ']';
      sValues := sValues + ', :' + FTable.FieldList[I].FullName;
    end;

    Result := 'INSERT INTO ' +sFields + ')' + sValues + ')';
  finally
    FreeAndNil(FTable);
  end;
end;

 

[taariq 0]

Thank you all so much - I will have a look and update now...Thank you
Just a question - what other databases will you advice me to use...

Thank you all - so nice to see Delphi live and well ...

[Stano 143]

It depends on the purpose of use. If it is an embedded / local version, I recommend Firebird. There is no need to install anything. Just copy.

Access is, as usual with MS, unlike other DBs. You will not acquire the right habits.

[corneliusdavid 214]

17 hours ago, taariq said:

Just a question - what other databases will you advice me to use...

Firebird is awesome--works well as either embedded or server, so it can scale with your app.

 

SQLite is small and nimble and available on every single platform.

 

If you have a choice, do not use Access--you will have many problems down the road.

[taariq 0]

Good day

How do I get started with Firebird ?
Please can you point me in the right direction....

Thank you

[corneliusdavid 214]

18 minutes ago, taariq said:

How do I get started with Firebird ?

Start here: https://firebirdsql.org/

 

Lots of links to documentation, support tools, and community blogs, etc.

[Guest]

Official site last v3 / v4 (tottaly free include commercial use)

https://firebirdsql.org/en/downloads/

https://www.firebirdsql.org/en/documentation/

 

you can install (1 instance) or just copy files on zip to another folders-target (more than 1 instance same pc, basically only change the "instance name" AND "port= nnnn" to access it) and setup it if you need more than one instance, like on servers.

 

on RAD, many suites can manage it, like: FireDAC, UniDAC, IBX, ODBC, old FibPlus, so...

 

in FireDAC: you need always:

1 FDWaitxxxx

1 FDPHYSxxxFB (firebird)

1 FDConnection

1 or more FDQuery/FDTable

1 or 2 (2 if separate READ and WRITE tasks) FDTransaction if needs manually controls it, else it will be automatically created by FireDAC

1 or more Datasourses to bind it to data-aware controls

 

by default Server is a SuperServer standalone instance but you can use it like a Classic instance for scalable server.

Edited February 20, 2022 by Guest

[Dmitry Arefiev 101]

> 1 FDWaitxxxx

> 1 FDPHYSxxxFB (firebird)

Long time are not required ...

[Guest]

will be? on RAD11 I dont have tested anymore FB...

If you manually add unit-names in "USES" clause can be true, then some visual or not components can not necessary at all. (reference to class it's that matter)

The driver-reference is always necessary to access the library functions (DLL/SO)

Edited February 21, 2022 by Guest

[taariq 0]

thanks - let me download and do a test database...
I am doing a Windows based program for my self - Job Card

So just to see if I understand Firebird
Download and install
Create database in Firebird
Use in Delphi

If I want to use program on another pc can I just like in Access copy the DB into correct folder and use or do I need to install something ells on that pc...
Thanks for all the reply's and help...appreciate it...

 

Thank you

[Serge_G 87]

8 hours ago, taariq said:

If I want to use program on another pc can I just like in Access copy the DB into correct folder and use or do I need to install something ells on that pc...
Thanks for all the reply's and help...appreciate it...

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   

[taariq 0]

Thank you so much

Playing with Firebird and Flamerobin
How would I setup an id field that is an auto number for ID as access db

My SQL

create table JobCard ( id varchar(10) not null primary key,
  orderno varchar(12),
  jobname varchar(100),
  startdate varchar(24),
  completiondate varchar(24),
  amountex varchar(12),
  desc varchar (50)
  );

 

Thank you

[Guest]

https://stackoverflow.com/questions/34553826/easiest-way-to-create-an-auto-increment-field-in-firebird-database

[taariq 0]

Thanks will have a look...
Just a question if I have a field  > ORDERNO , can I use that as my id as all tables will use the >orderno< to identify the records related to that order number...
Or is it a must that I must have an id field....

 

Thank you

 

 

[Guest]

basically, any field with no-null and unique-value can be a Identity-field, the problem is "next values" can dont be acceptable at all.

let's use like example a field named MyValuesIntWithNoDuplicatedValues 

in first looked, it would can be.

but the IDentity field have internal control for generate next values  then this can dont accept this field... you would can create a new field type IDentity, insert the values from field above into it, and if necessary update the last-value-sequence on IDentity field to continue the sequence. 

Then  if last-value-sequence was 54, but the last value inserted was 77, you would need use 

SET SEQUENCE .... FIELDXXX to 77.

So, the next values inserted on the table would be ok.

[haentschman 92]

Hi...😎

Quote

I must have an id field.... 

...always! Per table or better per database. 😉 Per database = the ID is complete unique.

 

id varchar(10) not null primary key,

... never string!

 

ID int not null primary key,

 

Edited February 22, 2022 by haentschman

[irawan 2]

16 hours ago, taariq said:

Thank you so much

Playing with Firebird and Flamerobin
How would I setup an id field that is an auto number for ID as access db

My SQL

create table JobCard ( id varchar(10) not null primary key,
  orderno varchar(12),
  jobname varchar(100),
  startdate varchar(24),
  completiondate varchar(24),
  amountex varchar(12),
  desc varchar (50)
  );

 

Thank you

for primary key DO NOT use char or varchar. better is use integer or bigint.
if you insist to use char or varchar, all what you will get is database performance become slower and slower.
some times depend on your application, there is a requirement that some field have to unique.
for in this requirement, you should use unique index.

ideally every table have its own primary key.

do not mix business requirement and database requirement

best regard

[taariq 0]

Thank for all the help - really learning a lot now - thank you so much for your time and effort...

 

So now I did the following...
1. Create my Firbird databse
2. Used FlameRobin

3. I added a record via Delphi to the database - so happy
4. Now I am trying to generate the ID next number so I don't have to manually add it...
My code works all fine adding record, but not once I add the code to sort field to get ID vale...
I get error under //get next ID

 

procedure TMainForm.Button1Click(Sender: TObject);
//declare my var to enter new job card
var sOrderNo, sJobName , sStartDate, sCompletionDate, sAmountEx: string ;
iID : integer;
begin
//assign values to input box
//iID:= StrToInt ( Inputbox ( 'ID' ,   'ID'  , '002'  ) ) ;
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'  ) ;
sAmountEx := Inputbox ( 'AmountEx' ,   'Enter Amount Ex'  , 'R '  ) ;
//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 ;
dmTaS.tblJobCard['OrderNo' ] := sOrderNo ;
dmTaS.tblJobCard['JobName' ] := sJobName ;
dmTaS.tblJobCard['StartDate' ] := sStartDate ;
dmTaS.tblJobCard['CompletionDate' ] := sCompletionDate ;
dmTaS.tblJobCard['AmountEx' ] := sAmountEx ;
dmTaS.tblJobCard.Post ;
//refresh db grid
DBGrid1.DataSource.DataSet.Refresh;
//show messages

 

Thank you

[Serge_G 87]

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

 

[taariq 0]

Good day

I tried that already id Flamerobin and get the following error...

 

Please view attached image

[Serge_G 87]

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

[taariq 0]

thanks for the quick reply - I don't understand that - sorry newbie - first time working with Firebird and flamerobin...
Maybe I created the table wrong - here is the code I used to created the table..

 

create table JobCard (id integer generated by default as identity primary key,
  orderno varchar(12),
  jobname varchar(100),
  startdate varchar(24),
  completiondate varchar(24),
  amountex varchar(12),
  desc varchar (50)
  );