Update a Database (code) when using an adapter on a form - vb2005

VS2005
VB2005
winforms

I am trying to update data to a database via code on a form that uses an adapter.

The dataset that I am using has joined tables.  I use the adapter to fill the data on the form (This code is built in by just draggind the dataset on the form).  Since it has joined tables, I found that in VB2005 you have to code your own insert/update/deletes.  Well I have done searches until I'm sick looking for this solution.  What I found was solutions for previous version and with 2005 I'm sure there is a better effecient solution.

I would appreciate some advice on how I can accomplish insert/update/delete in VB2005 with an adapter already defined.

Thanks!
wayneburrAsked:
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.

Carl TawnSystems and Integration DeveloperCommented:
If you are using multiple tables then you will have to create new Command objects for Update/Insert/Delete and assign them to the corresponding properties of the DataAdapter. This is because the code generator for the DataAdapter isn't very good at automatically creating those commands for linked tables.
0
RobertRFreemanCommented:
Nope, there isn't a better way in 2005.  The TableAdapters replace DataAdapters, but they are still limited to writing inserts and updates on single tables.

If you are updating mulitiple tables, either code the commands to do so, or split them into distinct tables in the dataset.
However, if the joined tables are read-only and you have only one updateable table, then create the TableAdapter using the single select statement, then afterwards, modify just the select command n the TableAdapter's Properties window to include the read-only joins.  This will leave the single table update commands and use a joined select command.
0
wayneburrAuthor Commented:
My biggest problem is the coding for adapters.  The code that I have found was used with 2003 and is not working for 2005.

Do you have some code snipit of how vb2005 would work?  Just an example of using the connection string and how to reference the fields on the form and the calling of the update knowing that your using an adapter?

Thanks!
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

RobertRFreemanCommented:
See my articles:

2005:
http://www.codeproject.com/useritems/ComboboxGridFilter.asp

2003 (goes into configuring adapters):
http://vsnetdatabinding.blogspot.com/
0
wayneburrAuthor Commented:
None of the suggestions really helped.  I can't belive that I could be the only one in the VS2005 world that is trying to use the power of VS2005 to update more then one column in different tables on the same form (winform).  I don't know of too many projects that have only one table per form and are anything other then grids.  (A view for instance on a form to display data from other tables and allow the user to update fields on that same form)
I am trying to stay away from codeing the adapters and that is what I felt that 2005 offers etc.
0
RobertRFreemanCommented:
The VS2005 standard is to have 1 DataTable and TableAdapter per table in your database.  This will allow you to bind to joined fields and update the tables.  You just need to call the update command on each tableadapter.

Are you binding a grid to a joined table, binding a grid to the child, or not using a grid.
0
wayneburrAuthor Commented:
Thanks for your response - I had just about decided to rewrite this in VB6 :)
I am in the process of creating the adapters as you mentioned.  On this perticular form I have only 2 tables (Shipto & Billto).  As I am doing this and you mentioning calling the update commands; would you code behind a button to call the update statements etc?  What would be the syntax of calling that update statement knowing that the adapter was not coded manually?
0
RobertRFreemanCommented:
The update command is always:
MyTableAdapter.Update(MyDataTable)

You would usually have a save command that would call the update command for each table adapter on the form.  There is a save button built into the binding navigator.
0
wayneburrAuthor Commented:
From what I am finding out is that when you add a field from one table, a bindingnavigator is added.  But when you add another field from another table, you don't get the bindingnavigator.  So, you don't get another binding navaigator and you can't have a navigator for one table and not that other without adding code without coding in buttons for the updates etc.
0
RobertRFreemanCommented:
Right.  There should be only one save button and one navigator.  For additional tables, you would just add the updates to the bindingnavigator's save button click handler.
0
hatem72Commented:

hi wayneburr

i did face this problem ... and thank god it solved today ... just check this link

http://www.experts-exchange.com/Programming/Programming_Languages/Dot_Net/VB_DOT_NET/Q_21823630.html

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
RobertRFreemanCommented:
Alright!

Note:  You should not use AcceptChanges at all.
The adapter will automatically call acceptchanges as part of the update.
0
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
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.