?
Solved

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

Posted on 2006-03-23
16
Medium Priority
?
228 Views
Last Modified: 2010-04-24
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!
0
Comment
Question by:wayneburr
  • 6
  • 4
  • 2
  • +1
13 Comments
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 16276279
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
 
LVL 16

Expert Comment

by:RobertRFreeman
ID: 16276282
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
 

Author Comment

by:wayneburr
ID: 16276371
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 16

Expert Comment

by:RobertRFreeman
ID: 16277400
See my articles:

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

2003 (goes into configuring adapters):
http://vsnetdatabinding.blogspot.com/
0
 
LVL 6

Expert Comment

by:hatem72
ID: 16289603
0
 

Author Comment

by:wayneburr
ID: 16487624
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
 
LVL 16

Expert Comment

by:RobertRFreeman
ID: 16489562
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
 

Author Comment

by:wayneburr
ID: 16489853
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
 
LVL 16

Expert Comment

by:RobertRFreeman
ID: 16491950
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
 

Author Comment

by:wayneburr
ID: 16530052
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
 
LVL 16

Expert Comment

by:RobertRFreeman
ID: 16530453
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
 
LVL 6

Accepted Solution

by:
hatem72 earned 1000 total points
ID: 16530576

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
 
LVL 16

Assisted Solution

by:RobertRFreeman
RobertRFreeman earned 1000 total points
ID: 16531582
Alright!

Note:  You should not use AcceptChanges at all.
The adapter will automatically call acceptchanges as part of the update.
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question