Solved

ADO.Net how to get auto-increment primary key from master into detail table

Posted on 2010-09-22
6
1,318 Views
Last Modified: 2012-05-10
Using Visual Studio 2008 I have written an app in VB.Net that runs under .Net Framework 2.0.

I am working with disconnected datasets and in this example I have 2 tables in a dataset.

One table can be regarded as the master and the other as the detail.

The Master table has an autoincrement Primary Key value, the detail table has a foreign key into this primary key.

The detail tables' foreign key allows NULLs.


When I add a new row to the master (disconnected from the database) it gets a -ve primary key value i.e. -1 - (because the autoincrement PK can't be properly known until the data is inserted into the database).

When I call an Update() on the Master table, the -ve PK is then replaced with a real PK from the database.


I then need to update the foreign key value in the detail table to match the real PK, but I can't work out how to get the real PK from the Master to the Detail table.


Tried creating a constraint in the dataset bewteen the tables with cascaded updates, this worked fine except that the constaint will not allow the foreign key in the detail table to be NULL. I need to allow NULLs in the detail table - so that won't work.


I can call Update() on the master and get it to generate the proper PKs, but can't find a way of tying up a master PK with which detail row it corresponds to.

Any suggestions welcome.

0
Comment
Question by:EricTViking
  • 4
  • 2
6 Comments
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33739155
are you using a dataset?
If socheck the tables in your dataset (dataset Tab)
expand The parent table and you will se also the child...after the last field of your parent table ..this table is that youhave to use....
0
 
LVL 11

Author Comment

by:EricTViking
ID: 33739178
Thanks for the reply but it doesn't make much sense. I mentioned I was using a dataset in the second sentence.
0
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33739213
When you add data in the child can you see the FK number?
0
[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33739342
maybe i have to improove my english!!!! ok ...Check this video is exactly what you want
http://msdn.microsoft.com/en-us/vbasic/cc138241.aspx
0
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33739460
And also this Provides a function for updatets deletes from parents child relaiton ....
http://msdn.microsoft.com/en-us/vbasic/bb725826.aspx
Hope it helps
0
 
LVL 11

Accepted Solution

by:
EricTViking earned 0 total points
ID: 33740035
Like I said I can't enable the constraint because I allow NULLs in the child FK.

In the end I coded a manual solution that solved the problem.

Pseudo code would be:
Add row to master table



Add row to detail table



Insert temporary PK of new master row as FK in details table



Create list of indices in dataset of added rows in master along with temporary PKs



Update master



Iterate added rows list and replace details FK with real PK

Open in new window

0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.

863 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

25 Experts available now in Live!

Get 1:1 Help Now