Solved

How do I get the IDENTITY field from the d/b?

Posted on 2001-08-10
28
584 Views
Last Modified: 2013-12-26
I am developing a master/detail window. Master refers to a table Amaster and the detail datawindow refers to Adetail table. These two tables have a one-2-many relationship, on a column named m_id, which is an identity column (autonumbered) in table Amaster.

When I insert data in the master datawindow in my app, since the m_id field is not given (it is autonumbered, so SQL Server will give it a number when I commit changes to the d/b), when I try to add data into my detail datawindow and save everything, it tells me that cannot insert NULL value into the m_id field of the Adetail table.

The problem is that since m_id hasn't got a value yet from the Amaster table, when inserting data to the Adetail, it hasn't got the m_id in order to pass it in the INSERT INTO Adetail (...) VALUES (...)

Any help on how to get the m_id before entering data into my detail datawindow, so that it avoids giving me the NULL error?

Or any well known implementation fixes for this problem?
0
Comment
Question by:gm395
  • 13
  • 9
  • 3
  • +2
28 Comments
 
LVL 4

Expert Comment

by:Bhatti
Comment Utility
Did you checked with the  "identity" datawindow column property to set to "No". But this is also depend upen the DBMS. Or may be related to Foreign Key in your Master and Detail tables.
0
 
LVL 1

Author Comment

by:gm395
Comment Utility
The identity is checked into the Update Properties of the master datawindow. I am using SQL Server 7.0 and Powerbuilder 7.

The problem still remains. It doesn't get the identity m_id from the Amaster table, in order to pass it into the m_id field (not allowing NULLS) of the Adetail table, so the application gives me the error...
0
 
LVL 1

Author Comment

by:gm395
Comment Utility
Adetail has a primary key d_id which is identity, autonumber, and a foreign key, m_id, which is the primary key of Amaster. Amaster has 3 keys, one of them being the "m_id", a text field "txt_id", and an int "int_id". The values of txt_id and int_id are predefined and have no problem with them. The m_id gives me all the problems.
0
 
LVL 4

Expert Comment

by:Bhatti
Comment Utility
I am understading so (may me is wrong), but any how, m_id is a PK of the master table and d_id is the PK and FK of the detail table. one to many relation is master to detail relation.

If you have no such script that it inserts into master the d_id you are inserting in detail you should with hand
enter the value in master table m_id. (because one m_id can be a many d_id in detail).

I don't know you are inserting in detail first then in  master or first in master then in detail.

But if you are inserting in detail first the m_id as d_id you are inserting in detail must be in m_id.

I realy afraid but you can test it if you have a test database also.

Please let me know I understood the problem?
0
 
LVL 1

Author Comment

by:gm395
Comment Utility
Adetail has a primary key d_id which is identity, autonumber, and a foreign key, m_id, which is the primary key of Amaster. Amaster has 3 keys, one of them being the "m_id", a text field "txt_id", and an int "int_id". The values of txt_id and int_id are predefined and have no problem with them. The m_id gives me all the problems.
0
 
LVL 1

Author Comment

by:gm395
Comment Utility
Again:

TABLE Amaster WITH
PK m_id
FK txt_id
FK int_id

TABLE Adetail WITH
PK d_id
FK m_id

RELATIONSHIP:
One record in Amaster can have many relationships to records in Adetail. So, many records of Adetail can refer to 1 record in Amaster.

The problem is that when I try to enter data using a master/detail sheet, when it updates the database, it first updates the Amaster table, but it doesn't pass the m_id (that was generated when the record was inserted) into the detail datawindow, so when the update function of the detail is called, m_id is NULL, and it gives me this problem. Is there a way of grabbing the m_id when the data is inserted into the Amaster table (100% accurate in multiuser environment) and pass it into the Adetail INSERT query, when inserting the detail datawindow records?


The problem is getting
0
 
LVL 4

Expert Comment

by:Bhatti
Comment Utility
You are inserting values in  Adeatil table  (d_id - which is primary key and foreign key of Adetail table and related to primary key of the Amaster table m_id). I think you must first enter with hand the d_id you are inserting in Adetail table in Amaster table.

In otherworlds enter the m_id with hand in Amaster table which you want to insert in Adetail as d_id.
0
 
LVL 1

Author Comment

by:gm395
Comment Utility
Again:

TABLE Amaster WITH
PK m_id
FK txt_id
FK int_id

TABLE Adetail WITH
PK d_id
FK m_id

RELATIONSHIP:
One record in Amaster can have many relationships to records in Adetail. So, many records of Adetail can refer to 1 record in Amaster.

The problem is that when I try to enter data using a master/detail sheet, when it updates the database, it first updates the Amaster table, but it doesn't pass the m_id (that was generated when the record was inserted) into the detail datawindow, so when the update function of the detail is called, m_id is NULL, and it gives me this problem. Is there a way of grabbing the m_id when the data is inserted into the Amaster table (100% accurate in multiuser environment) and pass it into the Adetail INSERT query, when inserting the detail datawindow records?


The problem is getting
0
 
LVL 1

Author Comment

by:gm395
Comment Utility
This is the problem with you last comment. Since I don't have the m_id (it is autoincrement in SQL Server), if I don't commit the row in Amaster, I cannot get the m_id, in order to pass it into the Adetail's m_id field. The problem is with the way master/detail datawindows work. The m_id MUST BE KNOWN, when I start inserting rows into the detail sheet in my window...
0
 
LVL 4

Expert Comment

by:Bhatti
Comment Utility
I am afraid but you know that column with Primary keys and Foreign keys to adjust as autoincrement is sometime dangerous. For exemple I don't know what you are inserting but if  you want to insert in detail number 27 and your master table has only max number 25 then the autoincement in master is 26 not 27, so cannot be updated.

You can also check with max function to get the master max number and then check what you want to insert is the same with increment in the master or not.

Please think on this proposal and let me know, I am thinking further.
0
 
LVL 1

Author Comment

by:gm395
Comment Utility
If I use the max function, and two users try to write at the same table, then they will both get the same m_id, and then pass it into the Adetail table, and duplicate key error will occur. I need to somehow save the data into my Amaster table, then get the m_id that was assigned ACCURATELY, in order to pass it into the Adetail table.

All this in a master/detail datawindow.
0
 
LVL 4

Expert Comment

by:Bhatti
Comment Utility
Before inserting please compare with master ID,
if necessary first insert in master untill  approach the inserted number
and then insert in the detail.

( Am I right?)
0
 
LVL 2

Expert Comment

by:rajeXsh
Comment Utility
Can you try the following
(1) Update the master datawindow, and COMMIT the change
(2) Read the value of the m_id column. (You will need to have put the m_id col in the datawindow. On doing  a ReselectRow(rownum) for the master, you will get the value of m_id)
(3) SetItem the value of m_id in all of the detail datawindow row(s) and update n COMMIT the detail datawindow.

You will have to take care of the transaction control manually i.e., deleting the changes from the Amaster table, if the update of Adetail is unsuccessful.
0
 
LVL 4

Expert Comment

by:Bhatti
Comment Utility
Hallo qm395,

What about IDENTITY ?
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 1

Author Comment

by:gm395
Comment Utility
Sorry I am on a small vacation, I will talk to you from my office, tomorrow morning.Thanks.
0
 
LVL 1

Author Comment

by:gm395
Comment Utility
Normally, I would think Powerbuilder would understand in such cases that m_id is an identity field, and before storing all the data into the detail datawindow, would automatically commit the master's data, get the m_id and pass it into the Adetail table. Can this be implemented automatically.

For the moment I had to remove the autoincrement from the Amaster table, and use another table named Aidentityvalue, in which I store a unique value, and each time I want to insert a record in Amaster (and related records in Adetail) I insert a record into Aidentityvalue, get its unique ID, and pass it as integer in Amaster. In this way, it is automatically passed to both Amaster/Adetail.

The problem in which I need your help now is the following:

I get the unique ID value from Aidentityvalue table is that I add a record with a large random number, and then I get the max(id) from that table, where random number="the random I have inserted before".

Now, if more than 1 users access the table at the same time, and accidentally, get the same random number 1/32767, then the whole system is screwed. Since the previous actions is only an INSERT and a SELECT, how do I lock these two actions, so that if a users runs these two SQLs, noone else can access the table?

I am writing all this info into the script of a button, and I want to do this:

LOCK table Aidentityvalue
INSERT INTO Aidentityvalue ....
SELECT FROM Aidentityvalue ....
UNLOCK table

what are the commands for implementing this LOCK/UNLOCK of the table in Powerbuilder 7.0.

Thanx

If two or more people access the Aidentityvalue
0
 
LVL 4

Expert Comment

by:Bhatti
Comment Utility
May I write you in detail later because I am busy in some requests to change.

Please look a "Lock database preferences" in Help SQLCA.Lock = "Value" may this top brings some results.

talk you later.
0
 
LVL 4

Expert Comment

by:Bhatti
Comment Utility
Did you read "Lock database preference"?
0
 
LVL 1

Author Comment

by:gm395
Comment Utility
Yes, but it seems that I have to define locks for the whole database (I am using SQL Server 7.0). The only lock I need is at these two actions I described before OR ... a lock on any action on the table Aidentityvalue.

Any help there?
0
 
LVL 2

Expert Comment

by:rajeXsh
Comment Utility
Try using TABLOCK or TABLOCKx.

The syntax is:

SELECT <col_name>
FROM <table_name>
WHERE <where_stuff> (TABLOCKX)

The lock is released when your transaction is complete.
0
 
LVL 1

Author Comment

by:gm395
Comment Utility
As I understand this will lock the table for the SELECT statement. I need the table locked for both the INSERT and SELECT statements. How would this work?
0
 
LVL 2

Accepted Solution

by:
rajeXsh earned 100 total points
Comment Utility
This will lock the table until the transaction is completed

Try

BEGIN TRAN

SELECT stm to lock

INSERT into tbl

SELECT from tbl

COMMIT TRAN
0
 
LVL 4

Expert Comment

by:Bhatti
Comment Utility
I think you need not to lock the tables. If SQL Server support embaded SQL as you write in SELECT statement at the end , FOR UPDATE will lock the table untill COMMIT OR ROLLBACK worked.

0
 

Expert Comment

by:kokan
Comment Utility
I come across with the same case.  For me, in the save event, try to update the header datawindow, since I have another column that is unique also, I try to retrieve the identity column based on it.  Set the identity column value into details database and update the details datawindow.  Finally commit the all changes if no error found.  That's it.  If you don't have an unique column other than the identity column, skip above opinion.  Suggest you to generate the key value instead of auto-gen by system.
0
 
LVL 3

Expert Comment

by:hink
Comment Utility
I don't see, where is the problem. I use it each day. No locking is needed. Identity value will appear in master datawindow after successful insert (when the column is marked as identity), commit is not needed. Then You have to transcribe this value into detail DW, column m_id, and update of detail DW will success too. Commit should be done after both datawindow updates.

There is only one bug in PB. Update with no resetflag "dw.update(true,false)" will cause, the master datawindow will obtain identity values, that can be rolled back, if second update (detail) will fail. Now, before second attempt to update master DW, You have to set identity values to NULL in master datawindow in rows, that remained newmodified, otherwise (this is the bug) datawindow will not get new identity values.
0
 

Expert Comment

by:kokan
Comment Utility
May be it's database driven.  I'm using MS Access 97/2000, the identity field can't refresh once inserted.
0
 
LVL 1

Author Comment

by:gm395
Comment Utility
I tried everything. The problem is that in a single D/W it works perfectly, and returns the variable, but if I am adding data into the detail datasheet, it seems that in the second row, and the next ones, it does not pass the ID field. I fixed it by removing the identity field, and getting values from SELECTing from another table. At least it works...
0
 
LVL 1

Author Comment

by:gm395
Comment Utility
it is just a partial solution to my problem.
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

Suggested Solutions

Programmer's Notepad is, one of the best free text editing tools available, simply because the developers appear to have second-guessed every weird problem or issue a programmer is likely to run into. One of these problems is selecting and deleti…
Update (December 2011): Since this article was published, the things have changed for good for Android native developers. The Sequoyah Project (http://www.eclipse.org/sequoyah/) automates most of the tasks discussed in this article. You can even fin…
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
The viewer will learn how to use NetBeans IDE 8.0 for Windows to connect to a MySQL database. Open Services Panel: Create a new connection using New Connection Wizard: Create a test database called eetutorial: Create a new test tabel called ee…

771 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

14 Experts available now in Live!

Get 1:1 Help Now