Solved

inserting data to a table with a generator

Posted on 2002-06-19
12
1,713 Views
Last Modified: 2013-12-09
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
0
Comment
Question by:Zinner
  • 4
  • 3
  • 2
  • +2
12 Comments
 
LVL 4

Accepted Solution

by:
YodaMage earned 250 total points
ID: 7093333
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
 
LVL 4

Expert Comment

by:YodaMage
ID: 7093701
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
 

Author Comment

by:Zinner
ID: 7095101
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
 
LVL 3

Expert Comment

by:oavidov
ID: 7095144
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
 
LVL 4

Expert Comment

by:YodaMage
ID: 7096066
(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
 
LVL 1

Expert Comment

by:Moondancer
ID: 7099068
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 2

Expert Comment

by:IPCH
ID: 7106692
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
 
LVL 2

Expert Comment

by:IPCH
ID: 7112870
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
 
LVL 4

Expert Comment

by:YodaMage
ID: 7113574
Am I missing something....where did he say he was using TTable?
0
 

Author Comment

by:Zinner
ID: 7119772
Your answer helped me much in solving my problem. Thanks!
0
 
LVL 1

Expert Comment

by:Moondancer
ID: 7119940
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
 

Author Comment

by:Zinner
ID: 7123993
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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Creating and Managing Databases with phpMyAdmin in cPanel.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now