Help... Need help updating database from datagrid or dataset

Please help!  I'm cannot figure out this out. How do I update information stored on multiple tables?  My database is filled with users.  Those users have several related tables.  For example each user might have a medical record, driving record, or even movies they rented records...  I have each of those tables in fields or datagrids in a tab control.  1 control for each related table.  What is the best way to save that information when it is edited?  

It seems like a waste to save every table if only 1 entry was made.  I'm pulling my hair and I need to get this fixed quickly.

I can also use JOINS to make it one big datatable, but saving it to the database then becomes a chore.  Any ideas would be appreciated.
jlrrayAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

kaylanreilorCommented:
Could you provide more details about the way that you bind data from the source to your controls ? Which type of databinding are you using ?
I'm not sure to understand the architecture of your database.
0
jlrrayAuthor Commented:
I've done it any number of ways.  I've binded the controls themselves directly to the attached Dataset using the designer.  I've also pulled the database into a dataset in seperate named tables and attached the controls to the datatables.  IN this case I've used the sqlDataAdapter.  I've also used access for the database. Imagine I have a people table, cars table, houses table.  THe people can have any number of houses or cars.  In my form, the designer initially created a nav bar control with the main table being the people table.  In 2 tabs on the form, I have one for cars and one for houses.  Inside the cars tab, is the fields from the cars datatable.. say ID (pk) | PeopleID (FK) | CarType | PurchasedDate.  Say Car Type refers to a seperate cars type table.  

How would I update the cars datatable and post that to the database.  or for that matter, how does one figure out how to change anything that has changed, been deleted or updated.. cars / houses or whatever?

I just used a simple example so I can understand it and hopefully apply this to my real projects.
0
kaylanreilorCommented:
For each different table I certainly have ONE binding only. Then if the data binding is correctly performed, each time something happen to the data in the DB that must be reflected on your display.
Whatever what happen on the GUI I suppose that you handle all the events, for example if the user presses the "del" key on the keyboard you have a function behind that is doing something.
So there is no magic way. Each time something happen you have process the event idividually and calculate which SQL query must be done on which data source.
You should have one dataset with several tables inside, for example, and update it by taking into account the links that exist between different tables. Each controls binded to the tables should be automatically updated.
Your question relates on your DB architecture perhaps.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

jlrrayAuthor Commented:
I'm not quite sure I understand what you're saying.  Are you saying that I should have a save button for each table and save them individually?
0
kaylanreilorCommented:
I'm not saying that you should have a save button, I just supposed that you provide something to the users to change the data. I just don't know how is made your GUI since you're telling so few about your app so I'm oblidge to make some suppositions... and since you're economizinf the info so everything is confusing to me.

But forget these buttons stories.
"How do I update information stored on multiple tables" => each time you want to change data in a table you have to calculate the impact on the others.
0
jlrrayAuthor Commented:
Do you have an example of what you mean?  Lets assume that I have the following table:

Employees
ID (PK) | Name | EmployerID (FK) | Phone | Address


EmployerID
ID (PK) |  CompanyName

Certification
ID (PK) |  Certification

EmployeeCertification
ID (PK) | Certification.ID | EmployeeID

So If I have one form.  The main section of that form is the Employees Information (name, address, phone)..  I was thinking of a tab object to hold the Certification Data.  

So the user has to enter all this information about the employee into the database.  If it is only name,address, phone -- its saved and updated no problem.  But because its relational, what is the best way to save all the related tables?    I've heard some people using transactions, some doing everything manual, etc.. I want the easiest way to handle saving related tables in one screen.

Joe

0
jlrrayAuthor Commented:
I figured it out after watching Beth Massi's series of videos.  Quite enlightening for me.

http://msdn2.microsoft.com/en-us/vbasic/bb466226.aspx

Joe
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.