Question Create new record in main table and simultaneously create new record a child tables?

jazzglenn

Member
Joined
Jan 17, 2012
Messages
5
Programming Experience
Beginner
Little something about me: First of all I'm really really bad at VB.Net and I hope you'll forgive me on my "newbiness" about this topic, I'm more of PHP and MySQL that got stuck up with VB.net on a very very important project. Also I've been using tutorials at this youtube page since the day i got the project, mind this: I have no background about VB.NET, and I Hope you guys could help me.


payrol.png
This is what my database look like, and yes I am recklessly showing my ERD to show how desperate I am on getting this done.


Anyways, the scope of the problem is:
1. the table "tbl_emp_sal" creates a new record for a payroll.
2. table "tbl_emp_sal" is clumped up bunch of FK's or Foreign Keys from PK's on other Different tables.
3. BUT, creating a new record for table "tbl_emp_sal" requires the other tables like "tbl_mba", "tbl_com", "tbl_dempcc", "tbl_tax" to create a record as well
4. I only need "tbl_mba", "tbl_com", "tbl_dempcc", "tbl_tax" to create a Primary Key that will directly connect to the main table "tbl_emp_sal" through Foreign Key.


in short: When the main table creates the new record the child tables create a blank record with its PK's sent to the main table.


I hope this is not much to ask for a solution. I really hope thread will get answers soon.


P.S. I've been using a lot of tablebindingsource's and Tableadapters soo much that sometimes i just get lost in what i'm doing. Yes this requires a face plam moment, but do please help :(
 
I'm not quite sure what the problem is. If you want to create records in the other tables then just go ahead and create them. Once they are created, get their IDs and then create the record for the other table. What part of that are you not able to do?
 
I'm not quite sure what the problem is. If you want to create records in the other tables then just go ahead and create them. Once they are created, get their IDs and then create the record for the other table. What part of that are you not able to do?

First of all, thanks for the response. Every single minute passby was like hell. phew... i feel a bit comfy now

OnT: Mr. Mod. Is there a way to like: when you click to add a new record on the main table, the other tables automatically generate a fk to support the main table when you click the save button on the main one? I mean the other tables automatically makes a new blank record on their own when you create a new record on the main one, and the PK on the other tables lunges its PK to support the main tables FK automatically when the main table record is save?

Or there is no way to do so and I have to manually put the other table's PK on the main table's FK, just to make a relationship with both?
 
First of all:
when you click to add a new record on the main table
Click what? We cannot read your mind. We have absolutely no idea how your application is designed. If I had to guess I would say that you have a WinForms app with a Button and, in the Click event handler of that Button, you have code that creates a new DataRow. In that case you already know how to create a DataRow so you simply need to do it for the other DataTables as well. It will appear to the user that it is automatic but it will be your code that does it.
 
First of all:Click what? We cannot read your mind. We have absolutely no idea how your application is designed. If I had to guess I would say that you have a WinForms app with a Button and, in the Click event handler of that Button, you have code that creates a new DataRow. In that case you already know how to create a DataRow so you simply need to do it for the other DataTables as well. It will appear to the user that it is automatic but it will be your code that does it.

help.png
This toolbar... thing... (omg... i dont even know its name... I feel soo dumb right now... )
I mean its like when you create a new record(the red circle one) the other tables create a record of their own when you save the main record(the blue circle) automatically.

It will appear to the user that it is automatic but it will be your code that does it.
THIS! THIS! THIS! This is what i'm looking for! Do you have any links to where i can learn more about things, or even something related to this? I'm really using the lazy drag and drop ones with ADO.NET and tableadapters and stuff that makes it really confusing, I thought it was gona be easy since its ado and stuff, but its not! Its even harder...
 
That tool bar is a BindingNavigator, which you could have determined by selecting it and then looking in the Properties window. The solution to your issue is quite simple. Using the Data Source window and dragging and dropping is easy but, as you would expect, it can't handle every scenario. Sometimes you have to make some customisations of your own. This is one of those times.

That BindingNavigator is associated with a BindingSource and that BindingSource is bound to your DataTable. The control(s) on your form are also bound the the BindingSource. Any changes you make in the UI, either via the BindingNavigator or the control(s) are reflected in the BindingSource and any changes made in the BindingSource are reflected in the DataTable. Likewise, changes made in the DataTable are reflected in the BindingSource and the UI.

When you click the Add New button on the BindingNavigator, it calls the AddNew method of the BindingSource. That in turn creates a new DataRow. Once you finish editing the new row, the EndEdit method of the BindingSource is called and the DataRow is added to the DataTable. You need to prevent the AddNew method being called implicitly, because you need to do some work yourself before it is. To do that, set the AddNewItem property of the BindingNavigator to Nothing. You can now click that button and nothing will happen. This allows you to handle the Click event of that button yourself. In the Click event handler, you can create the other rows as required, add them to their respective DataTables and get their IDs. You can then call AddNew on the BindingSource yourself and assign those IDs to the appropriate fields. Everything else will remain the same.
 
Wow, it seems you took you time finding a solution just to help me... you're the best.

This allows you to handle the Click event of that button yourself. In the Click event handler, you can create the other rows as required, add them to their respective DataTables and get their IDs. You can then call AddNew on the BindingSource yourself and assign those IDs to the appropriate fields. Everything else will remain the same.

This one right, the one that i placed with a red arrow with?
this here right.png
SQL Codes, right?
 
Important point of note for newbies and people experienced with databases but maybe not .NET... DataTables are in memory data containers that look and behave a bit like a table in a database, but it should be pointed out that they are most definitely not a apparition of the database table. DataTables can have more or fewer columns than the database table they might or might not represent. Data is downloade dinto a datatable and worked with locally, like cache, then maybe sent back or not

If anyone here versed in helping people says "datatable" they are not referring to the table full of data in the database..
 
I would personally like to thank jmcilhinney for helping through the entire time! Thank you for sharing you knowledge for people like me who barely knows what their doing.

OnT: It seems the problem is simpleNO STUPIDLY SIMPLE!

Lemme give you an idea... TAX has Salary?... No. Does Salary have Tax? Yes!

I blame one of the panelist for our project for this. We went to her 2 times in a row just to get that ERD right, and to think of it! Its STILL WRONG!

Anyways, I did manage to pass this challenge and when I got the flows right, Everything went as smooth as flowing water!

@cJard: May you please, clarify it a bit... I'm quite confused :|
 
When youre asking about .net and database things, when someone says these words:

DataTable DataColumn DataRow DataSet

They are talking about something in the .net program, not the database. A DataRow is like an array of values. A DataTable is like an array of DataRows

Many people get confused and think that changing something in a datatable will change data in the database (SQL Server, MySQL,Oracle, Access) table.. It wont. DataTables are often used as temporary data storage inside a .net app - they might not have anything to do with a database at all
 
Back
Top