Why isn't a dataset updating

I'm at a loss and don't even know what code to share to have someone help me.

I have 3 tables: client, spouse, addresses.  I have datasets for each one.  The client record is the primary table and has its primary key in spouse and addresses.  

I can create a client and modify as I wish.

After creating the client, I can add a spouse and modify as needed.

After creating the client, I can add an address with no problem.  Once I try to modify I get the exception, Update requires a valid UpdateCommand when passed DataRow collection with modified rows.

I am at a loss of where to begin to solve this.  For spouse and addresses I have added identical sql queries, where spouse.clientid = client.id and addresses.clientid = client.id (otherwise I would be able to pull the correct data to match the client, which is happening just fine).

Can someone help?  I've spent hours on this and have given us.
vlvawterAsked:
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.

Fernando SotoRetiredCommented:
Hi vlvawter;

Have you added a CommandBuilder object to the DataAdapter? It sounds like you do not have the commands set up in the adapter.

If not add a line of code line this after you have created the DataAdapter.

If using SQL classes

    Dim cb As SqlCommandBuilder = New SqlCommandBuilder(da)

If using OleDB classes

    Dim cb As OleDbCommandBuilder = New OleDbCommandBuilder(da)

Where the da in the above commands are the respective DataAdapters, SqlDataAdapter or OleDbDataAdapter.

Hope that this is of some help.

Fernando
0
vlvawterAuthor Commented:
Fernando,

I have created the dataset with the built-in wizards, which is supposed to set up all the lower level commands for me.  I have created all three tables from the same database.  Two of them work every time (without my having to create a CommandBuilder) and one works intermittantly.  

I can go the route you're talking about, but I'd rather know first of all if there is a quirk in the auto-dataset routine that I don't know about.  I'm going to wait and see if anyone knows the answer to this.  If not, I'll have to go your route and code on a lower level.  I was wanting to avoid that.  If I have to go that route, I'll award you the points.
0
Bob LearnedCommented:
When you dragged the data adapter to your form, and went through the wizard, did it say that it successfully created all 4 command types (Select, Delete, Insert, and Update)?  In order to use the Select SQL statement to build the other 3 commands, you need to include primary key information.

Bob
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
vlvawterAuthor Commented:
LearnedOne,

Your comments put me onto the path that finally solved my problem.  I wanted more than a solution; I wanted the reason why I was having problem.  Finally I realized that if I were updating a record, unless I created a sql query that found my record, then I had to have a primary key.  The adapter would not know which record to update otherwise.  Once I added a primary key, deleted and recreated the datasets, everything worked fine.

Thanks.
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.