How to avoid getting this error “non-nullable column cannot be updated to null” when using ADO Master-Detail relation?

0
votes

I am trying to build Master-Detail Form using TADODataSet, TDBText for Master Table and TDBGrid for Details Table (something smiller to Orders Form like Master Table includs the order header and Details Table includes the Order Items)

Master primary key is Identity column (autoincreament Field)

When trying to add a new record in Master Table and then trying to add records in Details Table before posting the record in Master Table I get this error "non-nullable column cannot be updated to null" and this happens because the master table primary key value is still not known because I didn't post the master record but if I tried the same scenario except that before I add the details records I posted the Master record then the error doesn't appear.

how to work around this problem?

I am connecting Master Table with Details Table using the following properties : Both DataSets have courser location : Client

Details Table :

  • DataSource : Master Table DataSource
  • Master Records : Id (Primary key of the master table)
  • IndexFieldNames : OrderId (the field in Details Table that indicates to which master record does this detail record belong to)
  • Lock Type : BatchOptimistic

Please help me

Thanks in advance Yazan Al-lahham

2
sql-serverdelphiadomaster-detail
What's wrong with posting the master table record first? Or does it not work? – Sertac Akyuz

2 Answers

3
votes

Well,

You should do something like that (pseudo-code):

1 - start a transaction
2 - post master record
3 - get the id inserted on master
4 - pass the master id to detail dataset
5 - post detail record
6 - If it worked, commit transaction. Otherwise, rollback transaction.

0
votes

Just an side note: CTP of the new SQL Server codename 'Denali' will bring the feature of SEQUENCES, working much near of whar firebird generator works. So this task will become MUCH easier:

When you get the command from gui to start an insert, get an ID from sequence Use it to fill the PK field of master record Post master record While you have detail records to insert Fill detail(s) record Post detail record Commit transaction

Very niiiice...