Web Development

General discussion


SQL Server 7, Auto-Increment Primary Key in Master/Detail relationship

By sks1607 ·
** URGENT **

I am working with amaster detail form in C#.net using SQL Server 7 database. (Major parts of table Structures are given as follows)

-- demand master table

create table libDemandMaster (
PK_DemandID numeric identity(1,1) primary key,
DemandDate smalldatetime,
HeadInCharge varchar(50) not null, ApprovedBy varchar(50) not null,
ReceivedBy varchar(50) not null

-- Demand master Detail

create table libDemandDetail (
FK_DemandID numeric references libDemandMaster(PK_DemandID),
SerialNo numeric not null,
ItemType varchar(15),
Title varchar(50) not null,
Price numeric(10,2) not null

Suppose, If I use non-auto-increment primary key field, I would maintain it in a transaction block, and the sql statements would be written inside this block like this...

Let LibDemandMaster's max ID is 300
we would can generate next as 301 and can store in a variable say MaxID

Start Transaction

Fire an insert query statement with MaxID variable for PK_DemandID to store a master record.
Start a loop to store detail (1 to n records)-----
Fire an insert query statement with MaxID variable for FK_DemandID to store a detail record.

End of loop ---
If ok Commit transaction
If notok Rollback Transaction

Now problem is that, my PK_DemandID field is auto-increament field. now how can I store the auto-generated primary key in the related detailed record in the libDemandDetail table.

Thanks in advance.

Sharad Kapil

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Related Discussions

Related Forums