Properly using Interbase Dataset.InsertSQL in Delphi

Hello, All
I use interbase and delphi fairly often. I am trying to insert a record into a table using a dataset and InsertSQL. The primary key (MID) uses a generator and trigger
      begin new.MID = Gen_ID(QANES_GENERATOR, 1); end
to behave as an autoincremented value.
I created a dataset in delphi with the INSERTSQL statement reading
"INSERT INTO MYTABLE (FIELD01) VALUES  ('TEST')"
and this has been added to the MyDataset.InsertSQL.
When I try to use the following statments
     MyDataset.Open;
     MyDataset.Insert;
     MyDataset.Post;
    MyDataset.Close;
Interbase responds with MID must have a value. Interbase is supposed to assign the value automatically, no? I suppose I could figure out the number by querying the generator and assigning it manually, but I figure I'm doing something wrong.

Perhaps the question should be: Can someone give me an example of inserting a blank, new record with the primary key ID set automatically?

Thanks


dkeeneAsked:
Who is Participating?
 
Gary BenadeCommented:
You dont need to make any of the other changes I mentioned, the trigger will do its job without futher changes, leave the tigger as you had it and change your insertsql back to:

INSERT INTO MYTABLE (FIELD01) VALUES  ('TEST')
or
INSERT INTO MYTABLE (FIELD01) VALUES  (:FIELD01)
to allow the proper use of ParamByName to populate field values


0
 
rfwoolfCommented:
Only trying to help...
In your dataset, if you create a persistent field for FIELD01 and set its field type fkCalculated. ...
Other than that I don't know this one...
0
 
Gary BenadeCommented:
Not familiar with the IB componets, I prefer IBObjects, but you could try setting insertsql to:

INSERT INTO MYTABLE (MID,FIELD01) VALUES  (Gen_ID(QANES_GENERATOR, 1),'TEST')

0
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

 
Gary BenadeCommented:
If my previous suggestion didnt work, it should have, you could also try changing it to
INSERT INTO MYTABLE (MID,FIELD01) VALUES (:MID,:FIELD01)
This probably wont work, but check out page 35 of this document
http://grdelin.phy.hr/~ivo/Nastava/Baze_podataka/ekstra/firebird/DevGuide.pdf

updatesql would look something like
UPDATE MYTABLE SET FIELD01=:FIELD01 WHERE MID = :OLD_MID
and deletesql
DELETE FROM MYTABLE WHERE MID = :OLD_MID

HTH
Gary

0
 
Gary BenadeCommented:
Solved, sorry for the initial wild goose chase

MyDataset.Open;
MyDataset.Insert;
MyDataset.FieldByName('MID').Required := False;
MyDataset.Post;
MyDataset.Close;

Regards
Gary
0
 
dkeeneAuthor Commented:
Hobbit
Thanks for your help. It works, but the Generator number was incrementing by two each time I added a record. i changed the Gen_ID function to increment by zero and it worked. Is this because calling the GEN_ID function once during the SQL and once by Interbase in the normal insert process causes incrementing by two?

THanks
0
 
dkeeneAuthor Commented:
Hobbit you're the greatest thanks for your help!
Doug
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.