Link to home
Create AccountLog in
Avatar of Alex
AlexFlag for Greece

asked on

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!

Avatar of Alex
Alex
Flag of Greece image

ASKER

...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?
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))';
ASKER CERTIFIED SOLUTION
Avatar of Pierre Cornelius
Pierre Cornelius
Flag of South Africa image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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

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

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

Avatar of Alex

ASKER

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?
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
Avatar of Alex

ASKER

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?
Avatar of Alex

ASKER

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.
No more ideas.
Listening...
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;
Avatar of Alex

ASKER

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.
Avatar of Alex

ASKER

... and will follow 1 question more about how to update and delete .... thanks again...
No problem. Glad I could help.