inserting data to a table with a generator

I'm new to IB and Delphi.
I've created a table with an auto increment generator in IB .when I insert data manually through the Isql of Interbase everything works fine.
Problem is when I try to insert data  through a dbedit component in a delphi form .I he won't accept it .because it needs a value for the primary key field which should be auto incemented by the generator.

For example I have a table of 3 columns :

1.  UserCode integer not null primary key
2.  FirstName char(15) not null
3.  LastName  char(15) not null

the generator fills in the UserCode field automatically.
when I insert data to the 2nd and third field through a
dbedit box on a delphi form it won't let me submit and
will shout that the first field (usercode) needs a value.


I have no idea what am I doing wrong/missing
ZinnerAsked:
Who is Participating?
 
YodaMageCommented:
Create a storedproc like:

CREATE PROCEDURE GETUSERGEN
RETURNS (
    NUM INTEGER)
AS
begin
    NUM = gen_id("GEN_UserTable_ID", 1);
end

Now, Drop a TStoredProc on your form and fill in databasename and storedprocname properties.

Now in code use something like:

procedure TfrmUserSetup.cdsUserBeforePost(DataSet: TDataSet);
begin
   if (cdsUser.State = dsInsert) then
   begin
      sp1.Prepare;
      sp1.ExecProc;
      cdsUser.FieldByName(gcUSER_GEN).AsInteger :=
      sp1.ParamByName(gcNUM).AsInteger;
      sp1.UnPrepare;
   end;
end;
0
 
YodaMageCommented:
Note, you can control the insertion with a trigger on a before insert event (using the same code as the stored proc), but beware, you have no control over order of events and servers with complex trigger arrangements may fire things in an order you do not want an which is unpredictable to you.
0
 
ZinnerAuthor Commented:
I will try out your solution but first I want to understand.
Is it not possible to let the IB trigger the generator by itself ? (once I insert some values for the other two columns).
Since I did put a trigger for the generator in the IB DB and as I said it all works fine when I insert values manually ,the trigger fires and the generator generates the number.It just won't work when I insert the values through a delphi form.  

when I extract the db it says:
/* Triggers only will work for SQL triggers */
Is this why it won't work ?

 
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
oavidovCommented:
Zinner,
I think when all columns's property is not null means
when you generate only the primary key other columns get values of blank or other difault value.
Solution could be to link between all columns by dinamic sql or dynamic table and then generate it.
Hope it help.
Regards.
oavidov
0
 
YodaMageCommented:
(Comment edit by Moondancer/Moderator per agreement)   What you (probably have happening is essentially a duplicate insert. What data source have you connected to the dbedit fields and is the datasource autoedit? If using a TQuery, is its' request live true or are you using cached updates? What is the updatemode currently on your data set?

Sounds like the way you are going you may have something like:

-data.insert
-data trigger event filing value
-data.post
-data.insert
-data.field1 + 2 assigned
-data.post

*ERROR*

Found It: 'Delphi Unleashed'

"Don't use Triggers on active Indices....each server gernerates a different kind of trigger. The developers did not want to run around finding out how to handle triggers for 10 different kinds of servers and neither did the developers of the BDE.

In the example under discussion, Delphi's poor support for triggers is not crucial because the table is not sorted on the CODE field, but on the LAST and FIRST fields. If it were sorted on the CODE field, this trigger might cause Delphi to lose track of the current record after the insert operation. Delphi would not know that the trigger fired. As a result the current record might be lost, not permanently lost, but removed from Delphi's field of sight."


LOL, it get worse if you have, say, a rowchange event on you dataset which then fires as your table resorts/filters based on values you have established. I believe you have lost your pointer.
0
 
MoondancerCommented:
oavidov ->  Your proposed answer has been rejected, question returned to the Open Question queue.  Please see this for information:
http://www.experts-exchange.com/jsp/qShow.jsp?ta=new_users&qid=20314185
Moondancer - EE Moderator
0
 
IPCHCommented:
Try to use IBDataSet, IBQuery with IBUpdateSQL or Query with UpdateSQL. There you can specify how to make insert in a script. It's recomended to use them not Table or IBTable components because they are much faster. You can see the diferens of updating with Table and Query with UpdateSQL using SQL Monitor. When you use Table in your script that you send to the server in the where clause are selected all fields that you have, when you use UpdateSQL you can set to use in the where clause only your key field.

Ivan
0
 
IPCHCommented:
If you still want to use Table you can do somthing like this:

procedure TForm1.Table1NewRecord(DataSet: TDataSet);
begin
Table1.FieldByName('UserCode').AsInteger:=0;
end;

When you insert the record the trigger will make value of UserCode proper.

Ivan
0
 
YodaMageCommented:
Am I missing something....where did he say he was using TTable?
0
 
ZinnerAuthor Commented:
Your answer helped me much in solving my problem. Thanks!
0
 
MoondancerCommented:
Thank you, Zinner, for letting us know that you were much helped here in solving your problem.  May I correct this grade for you to an A?  The points to you are the same, but the points awarded to our Experts are a direct result of the Grade you assign.  If the information provided was less than you needed, we always appreciate hearing from you so we can all learn and benefit in the process.

:) Moondancer - EE Moderator
0
 
ZinnerAuthor Commented:
Ok ,you may correct the grade to A.
The reason of giving a B was since my solution was different than suggested.as a matter of fact IPCH answers also where very helpful and I would like to credit him with 100 pts if that's possible.
My final solution was to use a "before post" event there I inserted some default value for the new row .The value would then be corrected by the trigger firing.Which is
 close to IPCH last answer.
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.