[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1949
  • Last Modified:

IBQuery insert a new record.

Hi everybody,

I use delphi 7 and a firebird database and i want to insert records from my delphi form into a table.I use ibquery and i want the correct sql syntax to insert records based on what the user types into some editboxes.I try this :

       ibquery1.SQL.Clear;
       ibquery1.SQL.Text:='INSERT INTO people (name,address,tel) VALUES ('name.Text','address.Text','tel.Text')';
       ibquery1.ExecSQL;

but i get an error : missing operator or semicolon.

Please tell me where am i wrong or show me which is the best way to insert a new record.

Thanks in advance!

0
Alex
Asked:
Alex
  • 6
  • 5
  • 4
1 Solution
 
AlexSoftware EngineerAuthor Commented:
...i forget to tell that i have also in the table an id primary key.I create a generator field (people_id_gen) and to the object inspector i set the generator field  of the ibquery  to increment by 1 so to have an auto incrementand and i choose the aplly event on post.Is that right or maybe will this make some problems to the insert statement?
0
 
Pierre CorneliusCommented:
Change this:
 ibquery1.SQL.Text:='INSERT INTO people (name,address,tel) VALUES ('name.Text','address.Text','tel.Text')';

To this:
 ibquery1.SQL.Text:='INSERT INTO people (name,address,tel) VALUES (QuotedStr(name.Text),QuotedStr(address.Text),QuotedStr(tel.Text))';
0
 
Pierre CorneliusCommented:
Sorry. Correction (should be):

 ibquery1.SQL.Text
  :='INSERT INTO people (name,address,tel) VALUES ('
    +QuotedStr(name.Text)+','+QuotedStr(address.Text)+','+QuotedStr(tel.Text)+')';
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Pierre CorneliusCommented:
Another way of doing it is using parameters e.g.

Set the query text only once to this:
 ibquery1.SQL.Text:='INSERT INTO people (name,address,tel) VALUES (:name, :address, :tel)';

The whenever you want to insert records do this:

ibquery1.parambyname('name').asstring:= name.text;
ibquery1.parambyname('address').asstring:= address.text;
ibquery1.parambyname('tel').asstring:= tel.text;
ibquery.execsql;

Regards
Pierre

0
 
bokistCommented:
Hi

As you described, I suspect that you have identity field as index.
In this case you can handle it this way:

      ibquery1.Sql.clear;
      ibquery1.Sql.add('set identity_insert PEOPLE on');
      ibquery1.Execsql;
      ibquery1.Sql.clear;
      ibquery1.Sql.add('INSERT INTO people (name,address,tel) VALUES (''name.Text'',''address.Text'',''tel.Text'')';
or
      ibquery1.Sql.add('INSERT INTO people (name,address,tel) VALUES (:p1,:p2,:p3)';
      ibquery1.parambyname('p1').asstring := 'name.Text';  // in case of ADO : parameters.parambyname('p1').value
      ibquery1.parambyname('p2').asstring := 'address.Text';
      ibquery1.parambyname('p3').asstring := 'tel.Text';    
 
      ibquery1.ExecSQL;

I am not sure will this work on IB.
regards,
   steve

0
 
bokistCommented:
oops, mistake
right way :
      ibquery1.Sql.add('INSERT INTO people (name,address,tel) VALUES (''name.Text'',''address.Text'',''tel.Text'')');
or
      ibquery1.Sql.add('INSERT INTO people (name,address,tel) VALUES (:p1,:p2,:p3)');

0
 
AlexSoftware EngineerAuthor Commented:
Hi PierreC,bokist thank you for the post.

Now i don't get the error but it doesn't send the data to the table (both of your solutions)  i think it has to do with the autoincrement of the id field.

any suggestion?
0
 
bokistCommented:
Hi

Try to handle index field by yourself(and remove autoincrement)
like this:
var
  people_id : integer;

      ibquery1.Sql.clear;
      ibquery1.Sql.add('select nr=max(people_id_gen) from People');
      ibquery1.Open;
      people_id := ibquery1.fieldbyname('nr').asinteger;
      ibquery1.Sql.clear;
      ibquery1.Sql.add('INSERT INTO people (people_id_gen, name,address,tel) VALUES (:p0,:p1,:p2,:p3)');
      ibquery1.parambyname('p0').asinteger := people_id + 1;
      ibquery1.parambyname('p1').asstring := 'name.Text';  
      ibquery1.parambyname('p2').asstring := 'address.Text';
      ibquery1.parambyname('p3').asstring := 'tel.Text';    
      ibquery1.ExecSQL
0
 
AlexSoftware EngineerAuthor Commented:
bokist maybe this is work but it isn't the solution i want.PierreC is to close to the solution with the example of:
=====================
 ibquery1.SQL.Text:='INSERT INTO people (name,address,tel) VALUES (QuotedStr(name.Text),QuotedStr(address.Text),QuotedStr(tel.Text))';
=======================

but i get an error about the id autoincrement field of the table and the generatorfield propertty of the ibquery.

vallidation error for column ID, value "***null***"

any ideas?
0
 
AlexSoftware EngineerAuthor Commented:
if i do this  :

ibquery1.SQL.Text:='INSERT INTO people (id,name,address,tel) VALUES ("1",QuotedStr(name.Text),QuotedStr(address.Text),QuotedStr(tel.Text))';

everything post ok but i give the id by hand and static.I want to use the generator field that i had create with the IBExpert into the db and then to autoincrement this based on the property of the ibquery the generatorfield.
0
 
bokistCommented:
No more ideas.
Listening...
0
 
Pierre CorneliusCommented:
I normally use a trigger to set my record ID's before post. That will solve your problem.

e.g.

I have a Order detail table called POD and my generator is called GEN_POD_ID and my trigger will look as follows:

CREATE TRIGGER TRIG_POD_BI FOR POD
ACTIVE BEFORE INSERT POSITION 0 AS
BEGIN
  IF(NEW."ID" IS NULL) THEN NEW."ID" = GEN_ID(GEN_POD_ID,1);
END;
0
 
AlexSoftware EngineerAuthor Commented:
yes Pierrec i solved it, this is what i want, i made a generator and a trigger with the IBExpert,i was set the generator field of the ibquery,  i use your code with the quotedstr ... and everything it is ok now! Thanks a lot man.
0
 
AlexSoftware EngineerAuthor Commented:
... and will follow 1 question more about how to update and delete .... thanks again...
0
 
Pierre CorneliusCommented:
No problem. Glad I could help.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 6
  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now