Link to home
Start Free TrialLog in
Avatar of Wayne Burr
Wayne BurrFlag for United States of America

asked on

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!
Avatar of Carl Tawn
Carl Tawn
Flag of United Kingdom of Great Britain and Northern Ireland image

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.
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.
Avatar of Wayne Burr

ASKER

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!
Avatar of hatem72
hatem72

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.
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.
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?
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.
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.
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.
ASKER CERTIFIED SOLUTION
Avatar of hatem72
hatem72

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial