Adding Child and Parent Records on One Form at One Time

Question

4.00/5 (1 vote)

See more:

I'm new to Visual Basic and am designing a database with a vb.net front end and sql server back end. I'm hoping for some insight...

The database consists of a dataset with a parent table and multiple child tables, all related to the parent table by an autoincrement "SpecID" key.

I have one form that includes bound fields from the parent table and multiple child tables. I'd like to allow the user to enter info in all fields, click the "Add" button and save all the tables to the dataset in one fell swoop. Obviously, the parent must be added before any child records can be added. What is the "safest" way to accomplish this?

I've thought about:

ParentBindingSource.AddNew()
ParentBindingSource.EndEdit()
ChildBindingSource.AddNew()
ChildBindingSource.EndEdit()

etc.
then entering all the information as if it's an update to the row.

I've also considered setting EnforceConstraints to false while the data is being entered, then changing it back to true at some point.
Is there a typical way to get this done?

Posted 23-Feb-11 13:42pm

WooHoo 9

Updated 23-Feb-11 16:11pm

Yusuf

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Albin Abel · Answer 1 · 2011-02-25T19:46:00

Hi

Defining a foreign key constraint will ensure that no child rows can add without a parent key. Can use the cascade type for update and delete rule and enforce constraints to true for safe delete and update.

For example..

C#

private void Form1_Load(object sender, EventArgs e)
 {
     DataSet dataSet2 = new DataSet("TestDataset");
     DataTable t1 = new DataTable("Parent");
     DataColumn t1col1 = new DataColumn("ParentId", typeof(int));
     t1col1.AllowDBNull = false;
     t1col1.AutoIncrement = true;
     t1col1.AutoIncrementSeed = 1;
     t1col1.AutoIncrementStep = 1;
     t1col1.Unique = true;
     DataColumn t1col2 = new DataColumn("Name", typeof(string));
     t1.Columns.Add(t1col1);
     t1.Columns.Add(t1col2);

     DataTable t2 = new DataTable("Child");
     DataColumn t2col1 = new DataColumn("ChildId", typeof(int));
     t2col1.AllowDBNull = false;
     t2col1.AutoIncrement = true;
     t2col1.AutoIncrementSeed = 1;
     t2col1.AutoIncrementStep = 1;
     t2col1.Unique = true;
     DataColumn t2col2 = new DataColumn("Name", typeof(string));
     DataColumn t2col3 = new DataColumn("ParentId", typeof(int));
     t2col3.AllowDBNull = false;
     t2.Columns.Add(t2col1);
     t2.Columns.Add(t2col2);
     t2.Columns.Add(t2col3);

     dataSet2.Tables.Add(t1);
     dataSet2.Tables.Add(t2);

     DataRelation rel = new DataRelation("Rel1", t1col1, t2col1);

     dataSet2.Relations.Add(rel);

     Constraint parent_child_link = new ForeignKeyConstraint("ParentChildRel", t1col1,t2col3);
     t2.Constraints.Add(parent_child_link);

     DataRow pRow = dataSet2.Tables["Parent"].NewRow();
     pRow["Name"] = "Parent1";
     dataSet2.Tables["Parent"].Rows.Add(pRow);

     DataRow cRow = dataSet2.Tables["Child"].NewRow();
     cRow["Name"] = "Child1";
     cRow["ParentId"] = 1;
     dataSet2.Tables["Child"].Rows.Add(cRow);


 }

This works fine, but if you remove this code block....

C#

DataRow pRow = dataSet2.Tables["Parent"].NewRow();
pRow["Name"] = "Parent1";
dataSet2.Tables["Parent"].Rows.Add(pRow);

It won't work as there is a foreign key violation..So you are safe you won't violate the constraint and handle this error as you wish , to notify error or handle it in a intelligent way